Splunk Search

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

lindsley
Engager

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?

Tags (2)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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]

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
Engager

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]

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...