topic Re: How to edit my search to filter results using extracted fields from a lookup and a where clause? in Splunk Search

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

splunkrocks2014 — Wed, 22 Jun 2016 18:03:36 GMT

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

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

somesoni2 — Wed, 22 Jun 2016 18:13:50 GMT

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)

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

ltrand — Wed, 22 Jun 2016 18:25:08 GMT

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

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

splunkrocks2014 — Wed, 22 Jun 2016 19:04:18 GMT

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?

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

somesoni2 — Wed, 22 Jun 2016 19:25:54 GMT

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

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

splunkrocks2014 — Wed, 22 Jun 2016 20:39:00 GMT

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.

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

somesoni2 — Wed, 22 Jun 2016 20:57:33 GMT

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