Is it possible to write the report to pull the users where the last login was equal 90 or more than 90 days?

[lksridhar]

Hi Folks,

I am write a search query to pull the users where the last login was equal 90 or more than 90 days? in splunk but i could not able to pull the details. could you please any one provide the search query winch we can use to pull the details.

[lksridhar]

Thanks woodcock and Daljeanis for your answer.

[woodcock · Accepted solution]

Like this (assuming failed/success is the same thing):

index=_audit sourcetype=audittrail user=* action=log* 
| rename info as status 
| replace succeeded with success in status 
| replace failed with failure in status 
| replace "login attempt" with login in action 
| stats count max(_time) AS _time BY user 
| eval secondsSinceLastSeen=now()-_time 
| eval timeSinceLastSeen=tostring(secondsSinceLastSeen, "duration")
| where secondsSinceLastSeen >= (90*24*60*60)

[DalJeanis]

Here's how to do this kind of query:

First, you search with index=* and your own user ID to find the records where you logged on this week. Depending on whether your systems are Unix or windows, these may be PAM records or windows events (usually 4624 these days). If your system uses multiple kinds of systems, then you will need to find a person using each one in order to determine what they look like and what index they are stored in.

Next, now that you have the format of each record, you build a query that will put together the data something like this....

   (search for index=index1 source=source1 usernamefield1=* any other search terms)
OR (search for index=index2 source=source2 usernamefield2=* any other search terms)
OR (search for index=index3 source=source3 usernamefield3=* any other search terms)
| fields index usernamefield1 usernamefield2 usernamefield3 any other fields you need to tell which kind of record it is 
| eval USER = case(test for first type of record, usernamefield1,
                   test for second type of record, usernamefield2,
                   test for third type of record, usernamefield3,
                   ...
                   true(), "((UNKNOWN))")
| stats max(_time) as lastused by USER
| where lastused<=now()-(89*24*3600)