topic Re: How do you use a calculated "_time" field for a timechart query in Splunk Search

How do you use a calculated "_time" field for a timechart query

jbrenner — Fri, 21 Jan 2022 21:29:48 GMT

I have a Splunk query that does a lot of computation and eventually returns only two calculated fields: _time and STORE_ID via the table command.

The _time field is formatted exactly like the the built-in _time field (e.g., "2022-01-17 23:50:25,897").

I want to do a timechart showing the count of how many times each unique STORE_ID appears in a given time bucket, using my calculated _time variable to fill the buckets. What do I put in the timechart clause to accomplish this?

Thanks! Jonathan

Re: How do you use a calculated "_time" field for a timechart query

richgalloway — Sat, 22 Jan 2022 00:59:16 GMT

The timechart command needs only the _time and one other field (STORE_ID in this case). The catch, however, is _time must be in epoch (integer) format. It won't work if you format it like it appears in the UI. FTR, the built-in _time field always is in epoch form, but the UI automatically converts it to text when displayed.

Re: How do you use a calculated "_time" field for a timechart query

diogofgm — Sat, 22 Jan 2022 01:25:40 GMT

Hi Jonathan,

As richgalloway stated _time is and epoch that splunk automagicly convert to the format you see in the GUI. Also the _time you see in the GUI is based on your regional settings (e.g your events could be GMT+0 and _time shows the GMT+5 conversion). You can check there "real" _time value when you do something like "| eval my_time = _time", the my_time will show the epoch time (which is based on UTC).

As for your case, if your time field is a calculated one that is not in epoch format and want to use that in a time chart you can use this:

| eval my_time = strptime(_time,"%Y-%m-%d %H:%M:%S,%3N") | eval _time = my_time | timechart span=1h count by STORE_ID

You can change the span to size the time bucket you want to use.

Re: How do you use a calculated "_time" field for a timechart query

PickleRick — Sat, 22 Jan 2022 21:14:19 GMT

One more thing - it's a UI feature - if a nummerical field is called _time, even if it's not a field from your event but a calculated one, it'll be displayed as a date string.

See for yourself

| makeresults

This will show you _time as a current timestamp (formatted as datetime string)

| makeresults
| eval time2=_time-86400

This will show you current timestamp in _time (again - formatted) as well as time2 which will hold a nummerical timestamp of exactly one day ago.

If you then forget the old _time and rename your new field back to _time

| makeresults
| eval time2=_time-86400
| fields - _time
| rename time2 as _time

You'll see _time again formatted as a datetime string but with the value of "a day ago".

It's still a nummerical value internally and you can do normal arithmetics on it (contrary to string dates which you have to parse first with strptime if you want to modify it in any way) but if the field is called _time, splunk's webui automatically displays it as if you called fieldformat on it with strftime().

So the question is whether your calculated _time is indeed a string date with the format it shows or is it just a nummerical field which is conveniently displayed by webui.

Try renaming the field and see if the shown value changes to a number.