search of user logon count using lookup table to return all users

[lindsley]

Hi,

I have a search like this to return the number of times users have logged in over a week.

source="mysource" "login succeeded" | eval luser=lower(user) | stats count(luser) by luser

I also have a lookup table with all users who have access to the system

Is there a way I could make my stats to put out a 0 count for users who are in the lookup table but haven't logged in?

[somesoni2 · Accepted solution]

Assuming name of the lookup file is allusers.csv and field name in the lookup is luser, try following

|inputlookup allusers.csv | table luser| eval count=0 | join type=left luser  [search source="mysource" "login succeeded" | eval luser=lower(user) | stats count(luser) as count by luser]

[lindsley]

Thanks a bunch. The only change I had to make to your command was to add a "| rename count(luser) as count"

full command

|inputlookup allusers.csv | table luser| eval count=0 | join type=left luser [search source="mysource" "login succeeded" | eval luser=lower(user) | stats count(luser) as count by luser | rename count(luser) as count]