Splunk Search

Calculate the difference between two time fields within a single event

migquinn
Engager

I have two time fields in a single event that I need to calculate the difference between and then display said difference in a table.

The two fields and time formats are below:

Time Created - Wed, 18 Dec 2019 19:23:56 -0500
Time Assigned - Wed, 18 Dec 2019 19:36:00 -0500

I would also like to then display the average of the difference for the other events, for example, if I have 10 events, I'd like the average time of the 10 differences.

Thanks in advance.

Tags (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @migquinn,
for first question you have to convert dates in epochtime using eval command and strptime funcion, but first rename fileds without spaces:

your_search
| rename "Time Created" AS Time_Created "Time Assigned" AS Time_Assigned
| eval diff=strptime(Time_Created,"%a, %d %b %Y %H:%M:%S %z")  - strptime(Time_Assigned,"%a, %d %b %Y %H:%M:%S %z") 
| table Time_Created Time_Assigned diff

About the average:

your_search
| rename "Time Created" AS Time_Created "Time Assigned" AS Time_Assigned
| eval diff=strptime(Time_Created,"%a, %d %b %Y %H:%M:%S %z")  - strptime(Time_Assigned,"%a, %d %b %Y %H:%M:%S %z") 
| stats avg(diff) AS Average

Ciao.
Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @migquinn,
for first question you have to convert dates in epochtime using eval command and strptime funcion, but first rename fileds without spaces:

your_search
| rename "Time Created" AS Time_Created "Time Assigned" AS Time_Assigned
| eval diff=strptime(Time_Created,"%a, %d %b %Y %H:%M:%S %z")  - strptime(Time_Assigned,"%a, %d %b %Y %H:%M:%S %z") 
| table Time_Created Time_Assigned diff

About the average:

your_search
| rename "Time Created" AS Time_Created "Time Assigned" AS Time_Assigned
| eval diff=strptime(Time_Created,"%a, %d %b %Y %H:%M:%S %z")  - strptime(Time_Assigned,"%a, %d %b %Y %H:%M:%S %z") 
| stats avg(diff) AS Average

Ciao.
Giuseppe

migquinn
Engager

Hi Giuseppe,

Thank you very much, this worked a charm!

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...