query using file with list of names

[hawkeyesc72]

I've got a list of over 100 account names and I'd like to search Splunk to find out the most recent activity (if any) the account performed.

Is it possible to create a search query that points Splunk to the file, it grabs the account names from the file, then performs some configured search and returns the results I need in a table?

I'd prefer not having to go through each account name one by one.

Thank you in advance for any help you can provide.

[PickleRick]

There are two techniques for solving such problem and picking the right one might depend on your use case.

One has been already shown by @gcusello and @ITWhisperer and involves a subsearch. This way you get a set of conditions for your main search by evaluating the subsearch which lists the entries from your lookup. 

Another possible way of going about it is to run a general search and then use the lookup to filter the results

Like

index=<whatever>
| stats max(_time) as latest_activity_time by user
| lookup <your_lookup.csv> lookupuser AS user OUTPUT user AS matcheduser
| search matcheduser=*

[hawkeyesc72]

Took a little tweaking but I finally got it to run. This will save us a ton of time in the long run. Thank you both for the suggestions!

[hawkeyesc72]

Thank you! I will definitely start checking these out! I appreciate the assist!

[gcusello]

Hi @hawkeyesc72 ,

yes, it's possible:

you have to put this list in a lookup (e.g. my_lookup.csv) and use it in a subsearch, putting attention that the column name in the lookup is the same of the field in your search.

So if the field is Account_name, bith in lookup and in the main search, you could run something like this:

index=your_index [ | inputlookup my_lookup.csv | fields Account_name ]
| ...

if the column name in lookup is diferent than the field in the main search, you have to rename it in the subsearch.

if you want to search the names in the lookup as free text search, you could run

index=your_index [ | inputlookup my_lookup.csv | rename Account_name AS query | fields query ]
| ...

Ciao.

Giuseppe

[ITWhisperer · Accepted solution]

You can use a subsearch to filter your main search, so, assuming you have loaded your list of names into a lookup, and that your data keeps the name in the same field in all your events, you could try something like this

index=<your index> [| inputlookup <your lookup file>.csv | table name | format]
| stats latest(_time) as latest_activity by name