Search code examples
splunksplunk-query

Splunk - Lookup values + static search string = output with count

I want to perform a search where I need to use a static search string + input from a csv file with usernames:

  1. Search query- index=someindex host=host*p* "STATIC_SEARCH_STRING"

  2. Value from users.csv where the list is like this- Please note that User/UserList is NOT a field in my Splunk: **UserList** User1 User2 User3 . . UserN

I have tried using multiple one of them being- | inputlookup users.csv | join [search index=someindex host=host*p* "STATIC_SEARCH_STRING"] | lookup users.csv UserList OUTPUT UserList as User| stats count by User

The above one just outputs the list of users with count as '1' - which I assume it is getting from the table itself.

When I try searching events for a single user like- index=someindex host=host*p* "User1" "STATIC_SEARCH_STRING". I get 100's of events for that user.

Can someone please help me with this? Sorry if this is a noob question, I have been trying to learn splunk in order to reduce my workload and am stuck here.

Thanks in advance!

Solution 
  • index=someindex host=host*p* "STATIC_SEARCH_STRING" [ | inputlookup users.csv | fields UserList | rename UserList as query]

    What is happening here is that there is a sub-search, which does an inputlookup on the users.csv file. We then use fields to ensure there is only a single field (UserList) in the data. We then rename that field to query. This is a special field in sub-searches; when the sub-search returns the field query, it is expanded out into the expression (field_value_1) OR (field_value_2) OR ....

    This expression is then appended to the original search string, so the final search that Splunk executes is index=someindex host=host*p* "STATIC_SEARCH_STRING" ("alice") OR ("bob") OR ("charlie")

    This approach is outlined at https://docs.splunk.com/Documentation/Splunk/8.0.3/Search/Changetheformatofsubsearchresults

    You can also look at the Splunk format command, https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Format if you need to alter the sub-search's expression format, for example, adding * around each returned expression.