topic Re: How to search events that contain a userID from a list? in Splunk Search

How to search events that contain a userID from a list?

Log_wrangler — Tue, 29 Sep 2020 20:07:57 GMT

I have a list of userIDs on a text file, called WatchList.txt

Splunk can natively parse out a field value pair (userID = John) from the logs I am searching.

My list is as follows:

userID
John
Mary
Bob
Paul

I want write a query like this:

index=app_logs sourcetype=user_logs | stats count by userID | WHERE (userID is on the list)

I am not sure how to write it, or how I can use a lookup as an input to the search.

index=app_logs sourcetype=user_logs [| inputlookup WatchList.txt] | stats count by userID

Is this correct ??? Or is there a better way???

Please provide an example.

Thank you

Re: How to search events that contain a userID from a list?

jkat54 — Fri, 22 Jun 2018 00:07:29 GMT

If you want to do the lookup approach then you’ll have to save the log/data file as csv and upload it. See the documentation on adding lookups.

Once you have it added as a lookup you can do this:

index=app_logs sourcetype=user_logs [|inputlookup lookupName.csv | fields userID | format]

Re: How to search events that contain a userID from a list?

jkat54 — Fri, 22 Jun 2018 00:10:03 GMT

That search would “unpack” to this:

index=app_logs sourcetype=user_logs ((userID=John) OR (userID=Mary) ...)

Re: How to search events that contain a userID from a list?

Log_wrangler — Fri, 22 Jun 2018 12:27:51 GMT

Thank you for the reply.
I created a watchlist.txt and I ran

index=app_logs sourcetype=user_logs [| inputlookup WatchList.txt] | stats count by userID

which gave me results, but are you saying it needs to be csv with a pipe to fields and pipe format, because docs indicate a .txt file is ok too?

also is there a better way to write this? in the subsearch I am not indicating an index or anything other than the list... is that correct?

Thank you

Re: How to search events that contain a userID from a list?

jkat54 — Fri, 22 Jun 2018 15:06:54 GMT

The fields command is to be sure that you only select the field we want. The format command is what makes each line in your lookup translate to ((field=value1fromlookup) OR (field=value2fromlookup))

Though fields and format commands won’t always be required, they suit the needs for what you’ve asked.

Re: How to search events that contain a userID from a list?

jkat54 — Tue, 29 Sep 2020 20:08:13 GMT

txt is ok if docs say so...

Is your search right? I guess so... that is if you want to look through an index named app_logs where sourcetype=user_logs AND UserID=John AND UserID=Mary AND UserID=Bob AND UserID=Paul then transform the results into statistical count by UserID.

But I though you wanted an OR condition between the UserID fields instead of the AND conditions. Hence the need for the format command. The fields command is just a best practice when helping others because if you have 3 columns in your lookup it would unpack to this instead:

(
headerfield1=row2col1 AND headerfield2=row2col2 AND headerfield3=row2col3
) OR/AND (
headerfield1=row3col1 AND headerfield2=row3col2 AND headerfield3=row3col3
)

OR/AND if you dont/do use the format command.

The fields reduces the count of the headerfield KvPs passed from the subsearch to the root search.

Re: How to search events that contain a userID from a list?

jkat54 — Fri, 22 Jun 2018 15:19:37 GMT

Actually I might be off on how it unpacks without the format command but you can try and see for yourself. Run each search and look at the job inspector. In there look for the normalized search. It should show you a better picture of what happens when you use format or fields, etc

Re: How to search events that contain a userID from a list?

Log_wrangler — Fri, 29 Jun 2018 13:52:14 GMT

I looked at a few of my old queries and read the docs again. Your solution is correct if I want to use multiple field headers in my csv.