Splunk Search

Better search string than this to count top logins by Company by date

bowesmana
SplunkTrust
SplunkTrust

My data consists of login events to a system. Each user belongs to a Company, of which there are 12 companies represented. The event date is the login time.

Fields are

Company - Company code
Co_Name - Company name
Name - User's name

I want a report to show :

For each company, the users with the most logged in days, i.e. only one login counts per day, during a given period.

I can achieve the top user per company like this

sourcetype=logins* | stats dc(Date) as Count by Name, Co_Name | sort Co_Name, -Count | dedup Co_Name

but if there is a company with more than one user with the same number of logged in days, I will only get one, whereas I want all the users for that company with that max login count.

I tried this one

sourcetype=logins* | top showperc=false 1 Name, Company by Co_Name

but that does not take account of more than one login per day, which should only count 1.

I feel there would be a more correct way to achieve this other than my search string above...

Tags (1)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

How about this, starting with your initial search:

sourcetype=logins* | stats dc(Date) as Count by Name, Co_Name | eventstats max(Count) as maxCount by Co_Name | where Count=maxCount

That should keep multiple equal "leaders" per company.

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

How about this, starting with your initial search:

sourcetype=logins* | stats dc(Date) as Count by Name, Co_Name | eventstats max(Count) as maxCount by Co_Name | where Count=maxCount

That should keep multiple equal "leaders" per company.

bowesmana
SplunkTrust
SplunkTrust

Perfect - thanks a lot martin!

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 ...