Splunk Search

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

Super Champion

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


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 :

| 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.


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

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...