Splunk Search

How to find out the no of users who didn't logged in some X application. and its percentage.????

moiezuddin
Explorer

How to find out the no of users who didn't logged in some X application. and its percentage.????

Tags (1)
0 Karma
1 Solution

lguinn2
Legend

Questions that ask "who did NOT login" or "which forwarder is NOT sending data" are always harder in Splunk. You can search the data in Splunk, but generally the data is what DID happen. So if I get a list of users from Splunk who logged in (probably easy) - how does Splunk know what is MISSING?

Somehow, you need to give Splunk a list of things (users, hosts, return codes or whatever) that SHOULD be there. Sometimes, the easiest way to do this is with a lookup. Imagine that you have created a user lookup table. (Lookup tutorial is here.)

The csv file could look like this, or it could be more complicated:

user
lguinn
somesoni2
moiezudden
jdoe
etc...

If the lookup is named user_lookup and the field in your Splunk data is called user too, you can do this:

| inputlookup user_lookup
| join type=outer user [ search <yoursearchhereforlogins> 
       | stats count by user ]
| fillnull
| where count = 0

This should give you a list of folks who have not logged in during your search interval.

I am not sure how you want to count the percentage. If what you want to know is "what percent of users have not logged in?" you can do the following:

| inputlookup user_lookup
| join type=outer user [ search <yoursearchhereforlogins> 
      | stats count as LoginCount by user ]
| fillnull
| eventstats count as TotalUsers count(eval(LoginCount=0)) as NeverLoggedIn 
| eval PercentNotLoggedIn = round(NeverLoggedIn*100/TotalUsers,1)
| where LoginCount = 0
| stats first(PercentNotLoggedIn) as "Percent Never Logged In" 
        values(user) as "User Names"

View solution in original post

lguinn2
Legend

Questions that ask "who did NOT login" or "which forwarder is NOT sending data" are always harder in Splunk. You can search the data in Splunk, but generally the data is what DID happen. So if I get a list of users from Splunk who logged in (probably easy) - how does Splunk know what is MISSING?

Somehow, you need to give Splunk a list of things (users, hosts, return codes or whatever) that SHOULD be there. Sometimes, the easiest way to do this is with a lookup. Imagine that you have created a user lookup table. (Lookup tutorial is here.)

The csv file could look like this, or it could be more complicated:

user
lguinn
somesoni2
moiezudden
jdoe
etc...

If the lookup is named user_lookup and the field in your Splunk data is called user too, you can do this:

| inputlookup user_lookup
| join type=outer user [ search <yoursearchhereforlogins> 
       | stats count by user ]
| fillnull
| where count = 0

This should give you a list of folks who have not logged in during your search interval.

I am not sure how you want to count the percentage. If what you want to know is "what percent of users have not logged in?" you can do the following:

| inputlookup user_lookup
| join type=outer user [ search <yoursearchhereforlogins> 
      | stats count as LoginCount by user ]
| fillnull
| eventstats count as TotalUsers count(eval(LoginCount=0)) as NeverLoggedIn 
| eval PercentNotLoggedIn = round(NeverLoggedIn*100/TotalUsers,1)
| where LoginCount = 0
| stats first(PercentNotLoggedIn) as "Percent Never Logged In" 
        values(user) as "User Names"

moiezuddin
Explorer

Thanks for your response , Its worked. I appreciate.

0 Karma

somesoni2
Revered Legend

Which application, Splunk application or custom application? If later, does your splunk has logon data indexed from that application?

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>