Splunk Search

How to edit my search to display _time format as the exact time of the event?

snehasal
Explorer

Hi,

I have written a query to find average of the runtime for each job on daily basis. My query works fine and I get results as expected. However, the _time in the search events doesn't show the exact time of event.
e.g _time is step_dtm_pst = 2017-06-05 23:49:24
However, after I run the query _times shows: 05/06/2017 00:00:00.000

The reason for this is using bin _time span=1d in query.

source="Temp10.csv" sourcetype="csv"  
| where step_info ="WORKFLOW START" OR step_info = "WORKFLOW END" 
| eval WfStart=If(step_info="WORKFLOW START",_time,null()) 
| eval WfEnd=If(step_info="WORKFLOW END",_time,null()) 
| sort 0 workflow_run_id 
| streamstats latest(WfStart) as WfStart, earliest(WfEnd) as WfEnd by workflow_run_id 
| eval WfDuration=round(((WfEnd-WfStart)/60),2) 
| bin _time span=1d
| timechart eval(round(avg(WfDuration),2)) as avgWFDuration by workflow_name limit=0 useother=false

I am not sure how to resolve this. Please help.

Thanks,
Sneha

Tags (3)
0 Karma
1 Solution

DalJeanis
Legend

try this

 source="Temp10.csv" sourcetype="csv"  
 | where step_info ="WORKFLOW START" OR step_info = "WORKFLOW END" 
 | eval WfStart=If(step_info="WORKFLOW START",_time,null()) 
 | eval WfEnd=If(step_info="WORKFLOW END",_time,null()) 
 | sort 0 workflow_run_id 
 | streamstats latest(WfStart) as WfStart, earliest(WfEnd) as WfEnd by workflow_run_id 
 | eval WfDuration=round(((WfEnd-WfStart)/60),2) 
 | timechart span=1d eval(round(avg(WfDuration),2)) as avgWFDuration by workflow_name limit=0 useother=false

View solution in original post

DalJeanis
Legend

try this

 source="Temp10.csv" sourcetype="csv"  
 | where step_info ="WORKFLOW START" OR step_info = "WORKFLOW END" 
 | eval WfStart=If(step_info="WORKFLOW START",_time,null()) 
 | eval WfEnd=If(step_info="WORKFLOW END",_time,null()) 
 | sort 0 workflow_run_id 
 | streamstats latest(WfStart) as WfStart, earliest(WfEnd) as WfEnd by workflow_run_id 
 | eval WfDuration=round(((WfEnd-WfStart)/60),2) 
 | timechart span=1d eval(round(avg(WfDuration),2)) as avgWFDuration by workflow_name limit=0 useother=false

snehasal
Explorer

This works.
Thank you:)

Get Updates on the Splunk Community!

Observe and Secure All Apps with Splunk

  Join Us for Our Next Tech Talk: Observe and Secure All Apps with SplunkAs organizations continue to innovate ...

Splunk Decoded: Business Transactions vs Business IQ

It’s the morning of Black Friday, and your e-commerce site is handling 10x normal traffic. Orders are flowing, ...

Fastest way to demo Observability

I’ve been having a lot of fun learning about Kubernetes and Observability. I set myself an interesting ...