Splunk Search

_time not displaying correctly in the output

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

Motivator

Add this to the end of your search

|convert ctime(_time)
0 Karma

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

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

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

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!