hi
We have a centralised lookup file (which is CSV file), but not in our control to change it.
The lookup file (enrichment.csv
) has sample
host,user,department
host1,admin\user1,FINANCE
host2,test\user2,HR
host1,admin\user2,DBA
Sample events (sourcetype=mydata
)
2019-05-14T13:57:00 client=host1 client_user=user1
2019-05-14T13:57:00 client=host2 client_user=user2
2019-05-14T13:57:00 client=host3 client_user=user1
I want to do a "lookup" on the enrichment.csv to find out the "department". But the "user" field need a regex to match the user.
I can quite easily do, when I use "join" and "inputlookup" using pipe
sourcetype=mydata | rename client as host, client_user as user
| join user host [|inputlookup enrichment.csv | rex field=user "((?<domain>[^\\\]+)\\\)?(?<user>.+)"]
But how can we do this using "lookup"? I'm looking for something in lines of
sourcetype=mydata | lookup enrichment.csv client as host [client_user | <some_regex> to get user] OUTPUT department
Hi there @koshyk,
You can't apply the regex directly to the lookup
command but you can go about this in two different approaches :
1- I think this approach is suitable for the scenario you described here :
sourcetype=mydata
| append [|inputlookup enrichment.csv | rex field=user "((?<domain>[^\\\]+)\\\)?(?<user>.+)"]
| stats values(department) as department by host, user
2- Second solution would be to use outputlookup (possibly schedule it) and build the csv file with the right values you require. You can then use that new lookup file without having to worry about regex.
Cheers,
David
That feature is not currently available. Your best bet is to get the lookup table format corrected (may be split the user field in lookup into domain and user field, so you can match). A workaround could be to create a copy of that lookup (using a regular scheduled search) where you can modify user column to suit your need.