Splunk Search

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

neerajs_81
Builder

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 * 

 

 


neerajs_81_0-1645010820666.png

 

Labels (1)
Tags (1)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

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

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

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 * 
Tags (1)

isoutamo
SplunkTrust
SplunkTrust

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
Builder

Thank you for the detailed information.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
Builder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Builder

Thank you Legend 🙂

Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...