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
Try this:
index="YouShouldAlwaysSpecifyIndex" AND sourcetype="my_source_type"
| dedup message.userId
| rex field=message.time "(?<_time>\d+)"
| timechart span=1month count
Try this:
index="YouShouldAlwaysSpecifyIndex" AND sourcetype="my_source_type"
| dedup message.userId
| rex field=message.time "(?<_time>\d+)"
| timechart span=1month count
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...
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?
Try making this an explicetely extracted field - at the moment you're using KV_MODE to do this, right?
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?
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...
Hi, thanks for all the help.
With that query:
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.
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.
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
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.
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.