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
Revered Legend

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
Revered Legend

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!

Splunk Decoded: Service Maps vs Service Analyzer Tree View vs Flow Maps

It’s Monday morning, and your phone is buzzing with alert escalations – your customer-facing portal is running ...

What’s New in Splunk Observability – September 2025

What's NewWe are excited to announce the latest enhancements to Splunk Observability, designed to help ITOps ...

Fun with Regular Expression - multiples of nine

Fun with Regular Expression - multiples of nineThis challenge was first posted on Slack #regex channel ...