Getting Data In

How to extract two values from two same fields in two events and then find the difference in timestamp

vanakkam777
New Member

Log data example:

{'job_no':'1','begin_build_time':'2019-08-15 11:00:00','event_type':'staging'}
{'job_no':'1','end_build_time':'2019-08-15 11:10:00','event_type':'staging'}
{'job_no':'1','begin_execution_time':'2019-08-15 11:10:01','event_type':'transaction'}
{'job_no':'1','end_execution_time':'2019-08-15 11:20:00','event_type':'transaction'}
{'job_no':'1','begin_artifact_time':'2019-08-15 11:20:01','event_type':'upload'}
{'job_no':'1','end_artifact_time':'2019-08-15 11:30:00','event_type':'upload'}

I need data table output like:
Job# 1 staging 10 mins transaction 10 mins upload 10 mins total 30 mins

Each line is coming as a event in Splunk.
I know we have to use coalesce and streamstats, but I am new to splunk not able to figure out how to get to this level.

Thanks in advance.

0 Karma

mayurr98
Super Champion

Try this run anywhere search

| makeresults 
| eval data="{'job_no':'1','begin_build_time':'2019-08-15 11:00:00','event_type':'staging'} | {'job_no':'1','end_build_time':'2019-08-15 11:10:00','event_type':'staging'}" 
| makemv data delim="|" 
| mvexpand data 
| table data 
| rex field="data" "'job_no':'(?<job_no>[^\']+)','(begin_build_time':'(?<begin_build_time>[^\']+)|end_build_time':'(?<end_build_time>[^\']+))','event_type':'(?<type>[^\']+)'"| eval begin_build_time=strptime(begin_build_time,"%Y-%m-%d %H:%M:%S"),end_build_time=strptime(end_build_time,"%Y-%m-%d %H:%M:%S") 
| stats values(begin_build_time) as begin_build_time values(end_build_time) as end_build_time by type job_no
| eval time_diff_in_min=round((end_build_time-begin_build_time)/60)

In your env, you should try

index=<your_index>
| rex field=_raw "'job_no':'(?<job_no>[^\']+)','(begin_build_time':'(?<begin_build_time>[^\']+)|end_build_time':'(?<end_build_time>[^\']+))','event_type':'(?<type>[^\']+)'"| eval begin_build_time=strptime(begin_build_time,"%Y-%m-%d %H:%M:%S"),end_build_time=strptime(end_build_time,"%Y-%m-%d %H:%M:%S") 
| stats values(begin_build_time) as begin_build_time values(end_build_time) as end_build_time by type job_no
| eval time_diff_in_min=round((end_build_time-begin_build_time)/60)
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...