_time not displaying correctly in the output

[Shashank_87]

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?

[arjunpkishore5]

Add this to the end of your search

|convert ctime(_time)

[woodcock]

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

[renjith_nair]

@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")

[Shashank_87]

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

[renjith_nair]

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