Splunk Search

Issue using sort command with inputlookup command

sdkp03
Communicator

I have a lookup file with 3 fields - source, status, timestamp.  Timestamp is saved as per below:

eval timestamp=strftime(_time,"%d%m%y %H:%M:%S")

Sample data:

ABC, 1, 20/03/21 04:45:46

ABC, 0, 27/03/21 11:17:31

ABC, 1, 29/03/21 14:33:06

ABC, 0, 01/04/21 12:56:41

Search query I am using is - | inputlookup test.csv | sort -TIMESTAMP

result as below:

ABC, 1, 29/03/21 14:33:06
ABC, 0, 27/03/21 11:17:31
ABC, 1, 20/03/21 04:45:46
ABC, 0, 01/04/21 12:56:41

and when I use query - |inputlookup test.csv | sort TIMESTAMP

ABC, 0, 01/04/21 12:56:41
ABC, 1, 20/03/21 04:45:46
ABC, 0, 27/03/21 11:17:31
ABC, 1, 29/03/21 14:33:06

 

This is weird because sort is happening just based on date! I am not even able to use eval on TIMESTAMP field(result is always empty). Have tried addinfo, where timestamp>now-xxx with no luck.

Labels (1)
Tags (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

Not sure I can see the image perfectly but are you doing

strptime(TIMESTAMP, "%d%m%y...")

If so, you are missing the / character in your dates between day month year

Also, reiterating the others, whenever dealing with dates, use epoch for calculations or if using strings, always use %F %T (shorthand for %Y-%m-%d %H:%M:%S) as that will give you consistency if having to treat them as strings

 

View solution in original post

scelikok
SplunkTrust
SplunkTrust

Hi @sdkp03,

Since TIMESTAMP values are string , in order to be sort as time you need to change to epoc or %Y%m%d%H%M%S. Epoc is easier for your case, please try below;

| inputlookup test.csv 
| eval timestamp=strptime(TIMESTAMP,"%d%m%y %H:%M:%S")
| sort - timestamp
| fields - timestamp
If this reply helps you an upvote and "Accept as Solution" is appreciated.

sdkp03
Communicator

As posted in the description eval on TIMESTAMP isn't working. Please see below attached snapshot, eval timestamp is blank.

downloads.jpg

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Not sure I can see the image perfectly but are you doing

strptime(TIMESTAMP, "%d%m%y...")

If so, you are missing the / character in your dates between day month year

Also, reiterating the others, whenever dealing with dates, use epoch for calculations or if using strings, always use %F %T (shorthand for %Y-%m-%d %H:%M:%S) as that will give you consistency if having to treat them as strings

 

sdkp03
Communicator

Missing / was the issue. Thanks for pointing that out. Its working as expected now 🙂

0 Karma

Funderburg78
Path Finder

What he said above.... However, if you want to display the timestamp in Human readable then I would do a 

| sort -timestamp

| table TIMESTAMP

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...