Splunk Search

Issue using sort command with inputlookup command

sdkp03
Path Finder

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 is appreciated.

sdkp03
Path Finder

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
Path Finder

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!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...