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
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)
index=xyz sourcetype=xyz:abc | rex field=user "(?:.\|)(?[\w])" | lookup user AS user OUTPUT title | search title=MD
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)
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?
What you want to do if the title is empty for a user?
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.
Try the updated answer (you probably don't need both the condition I wrote in where clause, just use whichever is applicable)