Any query help Highly appreciated ? Thanks in advance !
lists accounts in Splunk that have not been used (logon) for 90 days or more .
Is splunk automatically delete user after 90 days ?
Hi splunker969,
try something like this
index=_audit earliest=-90d latest=now
| stats count BY user
| append [ | rest splunk_server=local /services/authentication/users | rename title AS user | eval count=0 | fields user count ]
| stats sum(count) AS Total BY user
| where Total=0
| table user
Anyway Splunk doesn't automatically delete users.
Bye.
Giuseppe
Hi splunker969,
try something like this
index=_audit earliest=-90d latest=now
| stats count BY user
| append [ | rest splunk_server=local /services/authentication/users | rename title AS user | eval count=0 | fields user count ]
| stats sum(count) AS Total BY user
| where Total=0
| table user
Anyway Splunk doesn't automatically delete users.
Bye.
Giuseppe
I have used this and it works but how can I add the timestamp of their last login?
Hi @jcourses,
If you don't have a login in the monitored period you cannot have the last login, if instead you want the last login of the connected users, you could try something like this:
index=_audit earliest=-90d latest=now
| stats latest(_time) AS latest count BY user
| append [ | rest splunk_server=local /services/authentication/users | rename title AS user | eval count=0 | fields user count ]
| stats sum(count) latest(_time) AS latest AS Total BY user
| eval latest=if(isnull(latest),"Missing",strftime(latest,"%d-%m-%d %H:%M:%S"))
| table user latest
If you want the last login outside the monitoring period (90 days), you have to build a different monitoring approach: you should schedule a search like the following that every night writes in a lookup (calle e.g. splunk_connections.csv) the list of connected users in the previous day:
index=_audit earliest=-d@d latest=@d
| stats latest(_time) AS latest count BY user
| table user latest
| outputlookup splunk_connections.csv append=true
and then use this lookup to add the last login information to the main search:
index=_audit earliest=-90d latest=now
| stats latest(_time) AS latest count BY user
| append [ | rest splunk_server=local /services/authentication/users | rename title AS user | lookup splunk_connections.csv user OUTPUT latest AS old_latest | eval count=0 | fields user old_latest count ]
| stats sum(count) latest(_time) AS latest values(old_latest) AS old_latest AS Total BY user
| eval latest=if(isnull(latest),if(isnull(old_latest),"Missing",strftime(old_latest,"%d-%m-%d %H:%M:%S"),strftime(latest,"%d-%m-%d %H:%M:%S"))
| where total=0
| table user latest
Ciao.
Giuseppe
HI @gcusello
I am getting results from:
index=_audit earliest=-90d latest=now
| stats count BY user
| append [ | rest splunk_server=local /services/authentication/users | rename title AS user | eval count=0 | fields user count ]
| stats sum(count) AS Total BY user
| where Total=0
| table user
But I want to include the timestamp from when they last logged in within that period and then potentially include it on the dashboard we have setup.
Hi @gcusello
I'm getting a "Error in 'stats' command: The argument 'AS' is invalid." for both searches.
Any ideas?
Hi @jcourses,
sorry: paste error!
This is the correct first:
index=_audit earliest=-90d latest=now
| stats latest(_time) AS latest count BY user
| append [ | rest splunk_server=local /services/authentication/users | rename title AS user | eval count=0 | fields user count ]
| stats sum(count) AS Total latest(_time) AS latest BY user
| eval latest=if(isnull(latest),"Missing",strftime(latest,"%d-%m-%d %H:%M:%S"))
| table user latest
this is the correct second:
index=_audit earliest=-90d latest=now
| stats latest(_time) AS latest count BY user
| append [ | rest splunk_server=local /services/authentication/users | rename title AS user | lookup splunk_connections.csv user OUTPUT latest AS old_latest | eval count=0 | fields user old_latest count ]
| stats sum(count) AS Total latest(_time) AS latest values(old_latest) AS old_latest BY user
| eval latest=if(isnull(latest),if(isnull(old_latest),"Missing",strftime(old_latest,"%d-%m-%d %H:%M:%S"),strftime(latest,"%d-%m-%d %H:%M:%S"))
| where total=0
| table user latest
Ciao.
Giuseppe
Thanks @cusello
What is the | eval count=0| portion for in the rest subsearch?
From what I can tell it just adds a value to the blank counts so they can be calculated in the stats call?
@gcusello
Hi splunker969,
if you don't use count=0, you cannot aggregate and sum results from the main search and the list of all users, so the search doesn't run.
The approach is that you need to have always a value, also when there isn't any result in the main search (that's the problem solved in this way.)
Ciao and next time!
Giuseppe