All Apps and Add-ons

sorting by date timestamp not working as expected

ebailey
Communicator

I am working with the lastlog script in the nix TA. The output is not in order like it would be if I just ran the last command from the CLI so I am trying to sort the output by date of last login to make it useful but I am not having much luck.

index=os sourcetype=lastlog host=test | multikv | dedup LATEST | table LATEST | sort LATEST

Aug 27 19:05:48 2014
Aug 29 23:12:12 2013
Aug 7 12:10:25 2014
Aug 9 07:04:39 2013
Dec 12 00:17:34 2014
Dec 17 07:39:17 2014
Dec 17 09:24:28 2014

Any ideas?

Thanks!

0 Karma
1 Solution

gfuente
Motivator

Hello

In your example, LATEST is a text, so when you sort it, it´s beign sort lexicographically. You should need to convert that timestamp to epoch, sort it, and then convert it back to human readable format. Something like:

index=os sourcetype=lastlog host=test | multikv | dedup LATEST | eval LATEST = strptime(LATEST,"%b %d %H:%M:%S %Y") | table LATEST | sort LATEST | eval LATEST = strftime(LATEST,"%b %d %H:%M:%S %Y")

Didn´t test it, so maybe there is something missing, but the general idea should do it

Regards

View solution in original post

gfuente
Motivator

Hello

In your example, LATEST is a text, so when you sort it, it´s beign sort lexicographically. You should need to convert that timestamp to epoch, sort it, and then convert it back to human readable format. Something like:

index=os sourcetype=lastlog host=test | multikv | dedup LATEST | eval LATEST = strptime(LATEST,"%b %d %H:%M:%S %Y") | table LATEST | sort LATEST | eval LATEST = strftime(LATEST,"%b %d %H:%M:%S %Y")

Didn´t test it, so maybe there is something missing, but the general idea should do it

Regards

ebaileytu
Communicator

That is it - just add a = to the last eval

index=os sourcetype=lastlog host=test | multikv | dedup LATEST | eval LATEST = strptime(LATEST,"%b %d %H:%M:%S %Y") | table LATEST | sort LATEST | eval LATEST = strftime(LATEST,"%b %d %H:%M:%S %Y")

Thanks!

Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...