Splunk Search

_time being treated as a string when run with table command

termcap
Path Finder

Hi,

 

I have the following CSV data that I've uploaded into Splunk

 

iso_code,continent,location,date,total_cases
USA,North America,United States,2020-01-22,1.0
USA,North America,United States,2020-01-23,1.0
USA,North America,United States,2020-01-24,2.0
USA,North America,United States,2020-01-25,2.0

 

 

My props.conf is as below

 

[csv-c1]
BREAK_ONLY_BEFORE_DATE =
DATETIME_CONFIG =
INDEXED_EXTRACTIONS = csv
KV_MODE = none
LINE_BREAKER = ([\r\n]+)
NO_BINARY_CHECK = true
SHOULD_LINEMERGE = false
TIMESTAMP_FIELDS = date
TIME_FORMAT = %Y-%m-%d
category = Structured
description = Comma-separated value format. Set header and other settings in "Delimited Settings"
disabled = false
pulldown_type = true

 

 

Now, when I perform the search on this data the events are listed with the expected _time values

_time_field_full.PNG

But when I try to create a table with _time field, it appears as if Splunk treats the _time field as a string and not as a date object. Notice that the chronological order of the dates is lost as well as the HH:MM::SS part.

time_as_String.PNG

Can someone please point out why Splunk is behaving this way ? Surprisingly if I index the same data with the current timestamp, everything works fine and _time does not lose its chronological order or its HH:MM:SS part its displayed using the table command

 

Labels (3)
0 Karma
1 Solution

ITWhisperer
Ultra Champion

The dates are in chronological order, just reversed. The time is shown as midnight when displayed in the event list, but all the times are just dates so splunk optimises the display as only dates are present.

View solution in original post

0 Karma

termcap
Path Finder

Hi @gcusello kindly have a look at this behavior, your comment would be highly appreciated.

0 Karma

ITWhisperer
Ultra Champion

The dates are in chronological order, just reversed. The time is shown as midnight when displayed in the event list, but all the times are just dates so splunk optimises the display as only dates are present.

View solution in original post

0 Karma

termcap
Path Finder

@ITWhisperer 

 

The latest date I have is for yesterday: 08/04/2021 and the oldest date I have is 01/01/2020. In the below screenshot you will see that dates are mixed up with 2021 dates appearing between 2020 dates.

random_mixed.PNG

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @termcap,

as @ITWhisperer said, the dates are in chronological order, but probably in the second search they are displayed in the order of your ingestion csv file.

You can check this analyzing your csv file, probably the order that you see in you second search is the same of the csv file.

If you want to order them, you can sort them.

If instead you use the current timestamp, obviously they will be in index order, but if you index using current time as timestamp and you display _time and timestamp, probably you'll have the _time column ordered by time and the timestamp column in the sare order of now.

Ciao.

Giuseppe

termcap
Path Finder

Thank you for your comments @gcusello @ITWhisperer Is there a way to force Splunk to display the HH:MM:SS part when using the table command ?

0 Karma

ITWhisperer
Ultra Champion

You could try adding:

| fieldformat _time=strftime(_time,"%Y-%m-%d %H:%M:%S")
0 Karma