0

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!

messi.torres28
  • 39
  • 1
  • 7
  • 1
    What problem are you trying to solve with this query? Perhaps there is another way. – RichG Apr 17 '20 at 17:46
  • @RichG So I want to make sure that none of the users have any issues accessing the application. So my query would search for the Static search string + username. [there are around 10k Users] Once I have this, I can then setup an alert for it if any user has less than say 10 events generated in a particular given time. – messi.torres28 Apr 17 '20 at 18:24
  • 1
    If you don't have a 'user' field in your index, what connects indexed events to lookup file entries? – RichG Apr 17 '20 at 19:27

2 Answers2

0

I think you're doing the search inside out

What I think you may want is the following:

index=ndx sourcetype=srctp host=host*p* User=*
| search 
    [| inputlookup users.csv ]
| stats count by User

If I understand your question correctly, you want to use the values in your lookup as a filter on the data (ie, only where User is in that list)

If that is the case, the above will do just that

If you need to make the fieldnames match because the lookup table has a different name, change the subsearch to the following:

    [| inputlookup users.csv
     | rename lookup_field_name as User ]
warren
  • 32,620
  • 21
  • 85
  • 124
  • Thank you. The above query posted by you gives 0 results. I again cross-checked to confirm if that was the case but as I said, each of the users has 100's of events generated for them. – messi.torres28 Apr 17 '20 at 18:35
  • @messi.torres28 - really need some sample event data to be able to help you any better than mere guessing :) – warren Apr 17 '20 at 20:26
0
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.

Simon Duff
  • 2,631
  • 2
  • 7
  • 15
  • This worked perfectly. Thanks a lot! Also, thank you to everyone else for taking the time to help me out here with multiple of my problems! – messi.torres28 Apr 18 '20 at 09:42