Search count of user logins using lookup table

[jwalzerpitt]

I have a .csv file that has a list of users I'd like to search against to see how many times they've logged in. The .csv file is named allusers.csv, and contains one column named user. I created a lookup table named allusers.csv.

I'm trying to run the search against an index in which the user field is named sremote_UserID. My search is as follows:

|inputlookup allusers.csv | table user| eval count=0 | join type=left user [search index="sremote" "Login succeeded for" | eval user=sremote_UserID | stats count(user) as count by user | rename count(user) as count]

When I run the search, all users return 0 for the login count (I want those users who haven't logged in to return a count of 0). What needs to be fixed in my search? I'm brain cramping on how to associate the user column in the .csv file to the user field named sremote_UserID.

Thx

[vganjare · Accepted solution]

Hi,

Can you try following query?

index="sremote" "Login succeeded for" [ |inputlookup allusers.csv | rename user as sremote_UserID | fields sremote_UserID ] | eval user=sremote_UserID | stats count as count by user

Please validate if following query is giving you the events which has users from allusers.csv.

index="sremote" "Login succeeded for" [ |inputlookup allusers.csv | rename user as sremote_UserID | fields sremote_UserID ]

If the above query is giving you valid events, then stats will also be proper in query 1.

Thanks!!

[jwalzerpitt]

Thx a million for the reply and fix. Both queries works perfectly.

Greatly appreciated.