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

[splunkrocks2014]

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

[ltrand]

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

[somesoni2 · Accepted solution]

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]

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?

[somesoni2]

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

[splunkrocks2014]

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.

[somesoni2]

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