Splunk Search

Calculate time difference, then calculate average duration per severity level

DTERM
Contributor

This is a sample snippet from a very large log file:

lastOccurrence=2012/07/05 13:56:14|firstOccurrence=2012/06/18 13:46:12|severity=1|ticketNumber=111
lastOccurrence=2012/07/05 14:56:09|firstOccurrence=2012/06/18 14:45:59|severity=1|ticketNumber=111
lastOccurrence=2012/07/05 15:56:09|firstOccurrence=2012/06/18 15:45:59|severity=3|ticketNumber=222
lastOccurrence=2012/07/05 16:56:09|firstOccurrence=2012/06/18 16:45:59|severity=3|ticketNumber=222

Each ticket (number) can have many (sometimes in the 100's) updates for each ticket in the logs.

My question is how can I find the average ticket duration for each severity level (levels 1-5).

So how to calculate the time difference between the last and first occurrence of each ticket? Then how to get the average of all severity level times? Can Splunk do that?

Thanks.

Tags (2)
0 Karma
1 Solution

ayme
Splunk Employee
Splunk Employee

Something like this will do the trick where you use strptime() to convert to epoch:

sourcetype=mysourcetype | eval firstOccurrence=strptime(firstOccurrence,"%Y/%m/%d %H:%M:%S") | eval lastOccurrence=strptime(lastOccurrence,"%Y/%m/%d %H:%M:%S") | stats min(firstOccurrence) as firstOccurrence, max(lastOccurrence) as lastOccurrence by ticketNumber   | eval diff=lastOccurrence-firstOccurrence | stats avg(diff) by severity

View solution in original post

ayme
Splunk Employee
Splunk Employee

Something like this will do the trick where you use strptime() to convert to epoch:

sourcetype=mysourcetype | eval firstOccurrence=strptime(firstOccurrence,"%Y/%m/%d %H:%M:%S") | eval lastOccurrence=strptime(lastOccurrence,"%Y/%m/%d %H:%M:%S") | stats min(firstOccurrence) as firstOccurrence, max(lastOccurrence) as lastOccurrence by ticketNumber   | eval diff=lastOccurrence-firstOccurrence | stats avg(diff) by severity
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 ...