Splunk Search
Highlighted

What is the best way to format _time when values become unreadable after transpose?

Contributor

So I have to queries...

First one gives me a normal time/date format which is human-readable i.e. (2017-10-05 15:20:27 )

index=fireeye sourcetype=nx_json  | stats  list(appliance) as Appliance list(alert.src.host) as Source_Host list(alert.src.ip) as Source_IP list(alert.dst.ip) as Dest_IP list(alert.explanation.malware-detected.malware.name) as Explanation by _time 

Second one gives me a machine format for time/date i.e. (1507234827)

index=fireeye sourcetype=nx_json  | stats  list(appliance) as Appliance list(alert.src.host) as Source_Host list(alert.src.ip) as Source_IP list(alert.dst.ip) as Dest_IP list(alert.explanation.malware-detected.malware.name) as Explanation by _time  | transpose | rename column as Details, row* as occurrence*

The difference is that I transpose the output...

Does anyone know why this happens?... and what is the best way to correct this?

Thank you

Tags (3)
0 Karma
Highlighted

Re: What is the best way to format _time when values become unreadable after transpose?

SplunkTrust
SplunkTrust

The variable _time is special. It is actually stored in epoch time, but it is displayed in human-readable format.

Do this before the transpose:

| eval _time = strftime(_time,"%Y-%m-%d %H:%M:%S.%3N"
| rename _time as Time

You can see the time format variables here, if you want to make it some other format.

https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Commontimeformatvariables

View solution in original post