Splunk Search

_time not displaying correctly in the output

Shashank_87
Explorer

Hi, I have generated a report which contains _time column in a tabular format but it is displaying differently with different actions.
For example,
1. if i schedule that report as an email, I get _time displayed like this in the csv report - Wed Nov 6 23:59:57 2019
_time,siteReference,addressIdentifier,UPRN,serviceabilityOutcome
Sun Sep 15 23:59:58 2019,,,100050529544,UnServiceable

  1. If i schedule that report to configure as SFTP and send it on one of the ETL server it is displayed like this - I don't know how double quotes came into picture. I want this to be same as above format. "_time",siteReference,addressIdentifier,UPRN,serviceabilityOutcome "1573127879.336",20,6985807,,UnServiceable

Can someone help me with changing the proper format of _time field for the 2nd scenario?

0 Karma

arjunpkishore5
Motivator

Add this to the end of your search

|convert ctime(_time)
0 Karma

woodcock
Esteemed Legend

The _time field is very special in that it has an automatic fieldformat attached to it (see docs). When presented through the Splunk GUI, it will be pretty/human formatted but underneath, in reality, it is the integer that you see when dumping it to a file. You can see this if you rename or copy _time like this:

| eval Time=_time | rename _time as time | table time Time
0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@Shashank_87,

Splunk understands _time and it formats the value to a readable string but your ETL server doesn't. If you are not doing any further time calculation using the result, suggest to format it as string and use the value in the result

eg.

"your current search" |eval Time=strftime(_time,"%a %b %d %H:%M:%S %Y")
0 Karma

Shashank_87
Explorer

Hi @renjith.nair , yes that's perfect. That's what i used and now displaying it fine but I don't know why the double quotes are coming when i am checking the file on the server. And those double quotes are coming only on the _time column -

test@server1$ head -5 Daily_Report-_2019-11-07.csv
"_time",siteReference,address,number,status
"Thu Nov 07 14:10:56 2019",20,6922311,,working

This is the query i used -
| eval _time=strftime(_time, "%a %b %d %H:%M:%S %Y")
| table _time siteReference address number status

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@Shashank_87, most probably it's due to the presence of special characters in the result, in your case time has ":" in it. You may test it with other characters as well (space,. , etc)

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!