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

[moiezuddin]

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

[lguinn2 · Accepted solution]

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]

Thanks for your response , Its worked. I appreciate.

[somesoni2]

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