Splunk Search

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

koshyk
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

DavidHourani
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

somesoni2
SplunkTrust
SplunkTrust

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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...