Splunk Search

How to edit my search to filter results using extracted fields from a lookup and a where clause?

splunkrocks2014
Communicator

Hi.

How do I filter my results from an extracted field and where-clause?

I have a user lookup table which contains different formats such as /, , etc. I am able to extract a new field, but how can I apply it from the Splunk search?

My sample lookup csv file (users.csv):

user                       title
-------                    -------------
xyz.com/U1234              MD
X12345                     AVP
P12345                     ED

My lookup object configuration (transforms.conf):

[userid_lookup]
filename = users.csv
case_sensitive_match = false

And my Splunk search looks like the following. What is the correct syntax from my lookup?

index=xyz sourcetype=xyz:abc fields userid
| lookup userid_lookup | rex field=user "(?:.*\\\|)(?<userid>[\w]*)" OUTPUT title | where title="MD"

Thanks

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

Updated

index=xyz sourcetype=xyz:abc | join type=left userId [| inputlookup  userid_lookup  | rex field=user "(?:.*\\\|)(?<userid>[\w]*)" | table userId title ]
 | where title="MD" OR isnull(title)

View solution in original post

ltrand
Contributor

index=xyz sourcetype=xyz:abc | rex field=user "(?:.\|)(?[\w])" | lookup user AS user OUTPUT title | search title=MD

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

Updated

index=xyz sourcetype=xyz:abc | join type=left userId [| inputlookup  userid_lookup  | rex field=user "(?:.*\\\|)(?<userid>[\w]*)" | table userId title ]
 | where title="MD" OR isnull(title)

splunkrocks2014
Communicator

It works with title=something, but it doesn't work if searched user with empty title. For instance,

 index=xyz sourcetype=xyz:abc | join userid [| inputlookup  userid_lookup  | rex field=user "(?:.*\\\|)(?<userid>[\w]*)" | table userid title ]
  | where isnull(title)

Any clues?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

What you want to do if the title is empty for a user?

0 Karma

splunkrocks2014
Communicator

Basically, the title is never a null value. I am looking for if there are any users from the events are not matched to the lookup table. I can do following:

index=xyz sourcetype=xyz:abc | search NOT [| inputlookup  userid_lookup  | rex field=user "(?:.*\\\|)(?<userid>[\w]*)" | fields userid ]

but it doesn't work with the where-clause. I don't know why.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try the updated answer (you probably don't need both the condition I wrote in where clause, just use whichever is applicable)

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