Splunk Search

How to do use "lookup" when the table needs transformation using regex

Super Champion

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
0 Karma

Super Champion

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

0 Karma

Revered Legend

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.

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!