topic Re: _time being treated as a string when run with table command in Splunk Search

_time being treated as a string when run with table command

termcap — Thu, 08 Apr 2021 18:05:29 GMT

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

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.

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

Re: _time being treated as a string when run with table command

ITWhisperer — Thu, 08 Apr 2021 18:28:02 GMT

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.

Re: _time being treated as a string when run with table command

termcap — Thu, 08 Apr 2021 19:35:50 GMT

@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.

Re: _time being treated as a string when run with table command

termcap — Fri, 09 Apr 2021 10:33:04 GMT

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

Re: _time being treated as a string when run with table command

gcusello — Fri, 09 Apr 2021 11:19:45 GMT

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

Re: _time being treated as a string when run with table command

termcap — Fri, 09 Apr 2021 16:08:21 GMT

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 ?

Re: _time being treated as a string when run with table command

ITWhisperer — Fri, 09 Apr 2021 16:12:19 GMT

You could try adding:

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