Splunk Search

Average durations....

DTERM
Contributor

I’ve posted this query before but did not get a correct answer based on my requirements so I’m trying it again and providing more details this time.

I need a query that will provide the average duration for each ticketNumber for severity levels 0-6 for the past thirty days. The individual ticket duration is defined as:

  1. A single ticket duration = [last actionTime for a ticketNumber where (actionCode=U OR actionCode=I) AND deletedBy!=NULL] - [FirstOccurance where ActionCode = I], also serverName and serverSerial must be equal on all these instances of this calculation.

  2. This should process all tickets for the past thirty days.

  3. Finally, once we have a duration, I would need to calculate the average duration for severity levels 0-6.

  4. The ticket durations need to be renamed like 0 - Cleared, 1 - Intermediate, 3 - Warning, 4 Maintenance, 5 - Major, 6 - Critical

A sample log entry follows (I’ve sorted this log, the order of the entries are normally random, they are not sorted by ticketNumber as displayed below):

lastOccurrence=2012/07/25 10:00:00|firstOccurrence=2012/07/25     09:00:00|actionCode=I|actionTime=2012/07/25 18:00:38|serverName=PRIME_SN1|serverSerial=3129|severity=0|ticketNumber=111|deletedBy=John
lastOccurrence=2012/07/25 10:20:00|firstOccurrence=2012/07/25 09:20:00|actionCode=U|actionTime=2012/07/25 18:20:38|serverName=PRIME_SN1|serverSerial=3129|severity=0|ticketNumber=111|deletedBy=Mike
lastOccurrence=2012/07/25 10:40:00|firstOccurrence=2012/07/25 09:40:00|actionCode=U|actionTime=2012/07/25 18:30:38|serverName=PRIME_SN1|serverSerial=3129|severity=0|ticketNumber=111|deletedBy=Jake
lastOccurrence=2012/07/25 11:00:00|firstOccurrence=2012/07/25 10:00:00|actionCode=D|actionTime=2012/07/25 18:40:38|serverName=PRIME_SN1|serverSerial=3129|severity=0|ticketNumber=111|deletedBy=John

lastOccurrence=2012/07/25 10:00:00|firstOccurrence=2012/07/25 09:00:00|actionCode=I|actionTime=2012/07/25 18:23:38|serverName=PRIME_SN2|serverSerial=3130|severity=1|ticketNumber=222|deletedBy=John
lastOccurrence=2012/07/25 10:20:00|firstOccurrence=2012/07/25 09:20:00|actionCode=U|actionTime=2012/07/25 18:23:38|serverName=PRIME_SN2|serverSerial=3130|severity=1|ticketNumber=222|deletedBy=Alley
lastOccurrence=2012/07/25 10:40:00|firstOccurrence=2012/07/25 09:40:00|actionCode=U|actionTime=2012/07/25 18:23:38|serverName=PRIME_SN2|serverSerial=3130|severity=1|ticketNumber=222|deletedBy=Mike
lastOccurrence=2012/07/25 11:00:00|firstOccurrence=2012/07/25 10:00:00|actionCode=D|actionTime=2012/07/25 18:23:38|serverName=PRIME_SN2|serverSerial=3130|severity=1|ticketNumber=222|deletedBy=John

lastOccurrence=2012/07/25 10:00:00|firstOccurrence=2012/07/25 09:00:00|actionCode=I|actionTime=2012/07/25 18:23:38|serverName=PRIME_SN3|serverSerial=3131|severity=0|ticketNumber=333|deletedBy=Sam
lastOccurrence=2012/07/25 10:20:00|firstOccurrence=2012/07/25 09:20:00|actionCode=U|actionTime=2012/07/25 18:23:38|serverName=PRIME_SN3|serverSerial=3131|severity=0|ticketNumber=333|deletedBy=Mike
lastOccurrence=2012/07/25 10:40:00|firstOccurrence=2012/07/25 09:40:00|actionCode=U|actionTime=2012/07/25 18:23:38|serverName=PRIME_SN3|serverSerial=3131|severity=0|ticketNumber=333|deletedBy=Stan
lastOccurrence=2012/07/25 10:00:00|firstOccurrence=2012/07/25 10:00:00|actionCode=D|actionTime=2012/07/25 18:23:38|serverName=PRIME_SN3|serverSerial=3131|severity=0|ticketNumber=333|deletedBy=John

So the duration for ticketNumber 111 = 2012/07/25 18:30:38 − 2012/07/25 09:00:00. Then I would need to get the average of all Severities.

Based on this log, the sample output may look like the following table where 3:15:47:11 is 3 days, 16 hours, 47 minutes, etc..

Severity    Average Duration
1 Cleared   3+16:47:11
2 Intermediate  3+04:08:11
3 Warning   4+04:03:40
4 Maintenance   4+22:05:21
5 Major     3+13:24:25
6 Critical  3+00:32:48

Here is a sample answer that was provided in an earlier ticket. However this does not work. The logic seems accurate, but it does not work. Not sure why…

index=myapp  
| eval ticket_end_time = if(actionCode=="U",strptime(actionTime,"%Y/%m/%d %H:%M:%S"),NULL)   | tail 1
| eval ticket_start_time = if(actionCode=="I",strptime(firstOccurrence,"%Y/%m/%d %H:%M:%S"),NULL)   
| transaction serverName serverSerial ticketNumber   
| eval ticketDuration = ticket_end_time - ticket_start_time   
| eval pretty_ticketDuration = floor(ticketDuration/60/60)." Hours ".floor(floor(ticketDuration - (floor(ticketDuration/60/60)*60*60))/60)." Min ".floor(ticketDuration%60)." Sec."   
| stats avg(ticketDuration) AS Average_Ticket_Duration list(pretty_ticketDuration) AS pretty_ticketDuration by serverName serverSerial
| eval severity=case(severity==0,"Cleared",severity==1,"Intermediate",severity==2,"Warning",severity==3,"Maintenance",severity==4,"Major",severity==5,"Critical") |rename severity as Severity | rename avg_netcool_duration as "Average Duration"

Thanks in advance...

Tags (2)
0 Karma
1 Solution

yannK
Splunk Employee
Splunk Employee

one word : transaction

example :
mysearch | transaction ticketNumber startswith="keyword1" endswith="keyword2" | table duration ticketNumber severity | stats avg(duration) by severity

see http://docs.splunk.com/Documentation/Splunk/4.3.3/SearchReference/Transaction

View solution in original post

yannK
Splunk Employee
Splunk Employee

one word : transaction

example :
mysearch | transaction ticketNumber startswith="keyword1" endswith="keyword2" | table duration ticketNumber severity | stats avg(duration) by severity

see http://docs.splunk.com/Documentation/Splunk/4.3.3/SearchReference/Transaction

sdaniels
Splunk Employee
Splunk Employee

That is part of his search -> '| transaction serverName serverSerial ticketNumber'

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...