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.

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!

Learn Splunk Insider Insights, Do More With Gen AI, & Find 20+ New Use Cases You Can ...

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

Buttercup Games: Further Dashboarding Techniques (Part 7)

This series of blogs assumes you have already completed the Splunk Enterprise Search Tutorial as it uses the ...

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

What are Community Office Hours? Community Office Hours is an interactive 60-minute Zoom series where ...