Timechart using an epoch timestamp in the row instead of _time

[andrewbeak]

Hi,

I'm using JSON extract on my rows. I want to use the value that is contained in "message.time" instead of _time to timechart against.

This field is a UNIX epoch timestamp, for example 1525847317

I am trying this:

sourcetype="my_source_type" 
| eval _time=message.time
| dedup message.userId
| timechart span=1month count

Somebody else has said elsewhere on the forums that internally _time is an epoch value and it's just converted in the display to a string like 2018-05-09T13:33:57.000+03:00

I have also tried | eval _time=strftime(message.time, '%Ez') and also the strptime function, just in case.

Whatever I try the _time variable is cut out of the event (when I expand the event row) and my statistics table never contains any data.

How can I use the epoch timestamp value to timechart against?

EDIT:

This query shows three rows, _time formatted like 2018-05-09 14:06:56 and timetest/message_time both formatted as Unix epoch timestamps.

sourcetype="my_source_type"  
| eval timetest=_time
| dedup message.userId
| table _time, timetest, message.time

This query has blank (empty) columns for _time and timetest:

sourcetype="my_source_type"  
| eval _time=message.time
| eval timetest=_time
| dedup message.userId
| table _time, timetest, message.time

This query has a blank column for timetest, but _time is populated.... maybe there is a problem with field extraction? The documentation http://docs.splunk.com/Documentation/SplunkCloud/7.0.0/Knowledge/Addanevalexpressionattribute says that you can use an auto-extracted field to eval with.

sourcetype="my_source_type"  
| eval timetest=message.time
| dedup message.userId
| table _time, timetest, message.time

[woodcock · Accepted solution]

Try this:

index="YouShouldAlwaysSpecifyIndex" AND sourcetype="my_source_type"
| dedup message.userId
| rex field=message.time "(?<_time>\d+)"
| timechart span=1month count

[xpac]

Hey,

You should be able to use the timestamp as it is, because it has to be an epoch timestamp. Did you check if there is maybe any excess data in that field, like spaces, " or ' or something like this?
Don't be confused about being unable to see _time - fields starting with _ are invisible by default, you could do a second eval like | eval timetest=_time to see if it actually worked, just in case.
However, that should work fine, because timechart operates on _time by default...

[andrewbeak]

Thanks for the reply, I thought from reading other answers that it should work like that, but it definitely doesn't. I've updated the question with more details. Could it be something to do with field extraction maybe?

[xpac]

Try making this an explicetely extracted field - at the moment you're using KV_MODE to do this, right?

[andrewbeak]

Yes, I'm using KV_MODE. Piping the search through extract just duplicates the message.time field (two values appear for every row when I output the table as above) and the timetest is still empty. The documentation claims that auto-extracted fields should work. Should I raise this as a bug to Splunk?

[xpac]

Yeah, it should work, this document says the same.
Just thought to make it explicit (as in, create as an extracted field with regex, just to make sure).

Also, you said you tried this:

 sourcetype="my_source_type"  
 | eval _time=message.time
 | eval timetest=_time
 | dedup message.userId
 | table _time, timetest, message.time

Try this:

 sourcetype="my_source_type"  
 | eval oldtime=_time
 | eval _time=message.time
 | eval timetest=_time
 | dedup message.userId
 | table oldtime, _time, timetest, message.time

Is _time showing the same timestamp as oldtime? If yes, it somehow didn't get overwritten by message.time...

[andrewbeak]

Hi, thanks for all the help.

With that query:

1. oldtime is populated with a timestamp,
2. _time is blank,
3. timetest is blank, and
4. message.time is the timestamp

Setting _time to a literal (1525869721, for example) works.... it gets overwritten.

This looks like a bug with Splunk cloud to me, because the results I'm seeing definitely don't match what I expect from the documentation.

Thanks again, I'm going to raise a bug against this behaviour.

[xpac]

The only thing that comes to my mind, that would be easy to test, to try to copy the _indextime field to the _time field. _indextime always exists, is extracted at index time, and I've done this often, if that also doesn't work it would be a good indicator.

So, give it a try, replace message.time with _indextime.

[andrewbeak]

If I just replace _time with _indextime, like this: eval _time=_indextime then I have values in _time.

I cannot seem to overwrite message.time because _time is blank in this query:

sourcetype="amino:analyticsevent" index="dna_prod_analytic_event"
| eval message.time=_indextime
| eval _time=message.time
| table _time, message.time

Making an entirely new variable (not trying to overwrite the extracted variable) results in _time having values:

sourcetype="amino:analyticsevent" index="dna_prod_analytic_event"
| eval foo=_indextime
| eval _time=foo
| table _time, message.time

[xpac]

Ah, doom. Some commands fail when you have fields with dots, or other stuff inside, so you might want to try to put that message.time in " or ' - maybe that helps.

[andrewbeak]

Nope, that just treats it like a string literal and my column values are now all the string "message.time" 🙂 I've raised the issue with Splunk.