I need to write a query to to report on all last logon times for users that logged into any linux in our environment. What am I doing wrong?

[dylan_yoder]

The query I wrote doesn't seem to work as expected. The time stamp is missing under the logon_time column next to the corresponding listed users and the users in the user column show many duplicate occurrences (example: 20+ root logons) instead of a single entry of the last logon of that account on that specific host.

sourcetype=linux_secure source="/var/log/secure" (user=* OR ruser=*) ("Accepted Publickey" OR "session opened" OR "Accepted password")
| stats list(user) as User, list(ruser) as "Remote User", list(latest(eval(if(action==success,_time, null())))) as logon_time by host
| eval logon_time=if(isint(logon_time),strftime(logon_time, "%b %d, %I:%M %p"), logon_time)
| sort –count

[somesoni2]

Try like this

sourcetype=linux_secure source="/var/log/secure" (user=* OR ruser=*) ("Accepted Publickey" OR "session opened" OR "Accepted password") |  eval logon_time =if(action==success,_time, null()) | stats values(logon_time ) as logon_time by host user ruser | eval logon_time=mvindex(logon_time,-1)  | eval logon_time=if(isint(logon_time),strftime(logon_time, "%b %d, %I:%M %p"), logon_time) | stats list(user) as User, list(ruser) as "Remote User", list(logon_time) as logon_time by host

Update

This should give you latest logon for each (unique) user per host.

sourcetype=linux_secure source="/var/log/secure" (user=* OR ruser=*) ("Accepted Publickey" OR "session opened" OR "Accepted password") |  eval logon_time =if(action==success,_time, null()) | stats latest(logon_time) as logon_time by host user ruser | eval logon_time=if(isint(logon_time),strftime(logon_time, "%b %d, %I:%M %p"), logon_time)

If you want to group users and their corresponding logon time based on host, then you can add following to above search

| stats list(*) as * by host

[dylan_yoder]

I ended up using the following queries to get the desired results

sourcetype=linux_secure user=* ("Accepted Publickey" OR "session opened" OR "Accepted password")
| stats latest(eval(if(vendor_action="session opened",_time, null()))) as logon_time by host user
| eval logon_time=if(isint(logon_time),strftime(logon_time, "%b %d, %I:%M %p"), logon_time)
| sort –count
| stats list(user) as user, list(logon_time) as logon_time by host

OR

sourcetype=linux_secure source="/var/log/secure" (user=* OR ruser=*) ("Accepted Publickey" OR "session opened" OR "Accepted password") |  eval logon_time =if(action="success",_time, null()) | stats latest(logon_time) as logon_time by host user  | eval logon_time=if(isint(logon_time),strftime(logon_time, "%b %d, %I:%M %p"), logon_time) |  stats list(*) as * by host | sort -logon_time

Both seemed to work.

[dylan_yoder]

That didn't seem to work for me unfortunately. Nothing resolved at all. I need it to show the latest/most recent logon event for the individual/unique user listed by Linux host.

[dylan_yoder]

Thanks that helped!