How can I get _time in stats command displays in Epoch when count is greater than 1?

[neerajs_81]

Hello,  The below search displays  _time in human readable format when count  of the results =1 but in EPOCH format when count > 1.   How can i get it to display _time value in  human readable format when count > 1 as well ?  Notice Rows number 2 ,4 and 5 in my results...

index=aws
stats values(user_type), values(_time), values(eventName) count by user_name
|rename values(*) as * 

[yuanliu]

Use strftime() before using values(_time); for example,

index=aws
| eval _time = strftime(_time, "%F %H:%M:%S")
|stats values(user_type), values(_time), values(eventName) count by user_name
|rename values(*) as * 

[isoutamo]

As @gcusello said the issue is how splunk manages _time on GUI. In GUI it will automatic convert it to your local TZ (actually what you have defined on client settings) based human readable values. But when you are using stats values(_time) you haven't anymore field _time instead your field name is "values(_time)" and for that splunk don't do any automatic conversation. When you rename those fields "rename values(*) as *" you get back field _time and splunk shows it again correctly.

@yuanliu 's answer is easy way to fix it . Another options is use 

stats values(user_type) as user_type, values(_time) as _time, values(eventName) as eventName count by user_name

(needs that eval _time to drop ms away) or you could try convert like

...
| convert timeformat="%F %T" ctime(values(_time))

which works also with many _time values (with or without ms part).

There are also some other ways to fix it. But the real issue is how splunk GUI handle _time field vs. other fields which has epoch times.

r. Ismo

[neerajs_81]

Thank you for the detailed information.

[yuanliu]

As @isoutamo said, it's a mere quick fix.  You may want to consider @PickleRick's points:

• Use a different field name for string and leave _time in its native format.  This may not matter in one or the other use case, but I often get confused when a builtin variable suddenly doesn't behave as I expected, then realize that I did something to change it.
• Are multiple timestamps good for the column, or should you consider min(_time), max(_time), avg(_time),  max(_time):min(_time), etc. as a single value? (Personally I find multiple timestamps in one row distracting.)

[PickleRick · Accepted solution]

Close, but simply doing values(_time) as _time won't do.

The reason is that, as we all know, _time as a field within the event is a numeric field. Also the webui will try to render a field, it it's called _time, as a unix timestamp field into a string value.

Problem with multivalued field is that it's not a single number. I'm not sure how it's treated internally, but "on the outside", unless interpreted as multivalued field, it's treated more or less like a string.

That's why the webui won't be able to render the timestamp to a date string because it has no single numeric timestamp to render in the first place.

Contrary to other proposed solutions I would not advise using eval, but fieldformat, because it leaves the underlying timestamp untouched. And you can perfectly well apply it after doing your stats magic.

So you can try.

index=aws
stats values(user_type), values(_time), values(eventName) count by user_name
|rename values(*) as *
| fieldformat _time=strftime(_time,"%c")

I'd however suggest you chose another field name for that timestamp field because webui not only tries to render the timestamp to a date string but also flattens the multivalued field to a single string.

That's simply one of the quirks of the WebUI (which usually works for the user's good but sometimes can be confusing).

[neerajs_81]

Thank you @PickleRick for the detailed information. Marking your response as solution.

[gcusello]

Hi @neerajs_81,

this is normal because _time is naturally in epochtime.

There's an automatic tranlaction if you have one value.

You have two choices:

• use eval before stats to have all the values in human Readable format,
• use earliest or latest instead values in the stats command, so you'll have only one value.

Ciao.

Giuseppe

[neerajs_81]

Thank you Legend 🙂