Average durations....

[DTERM]

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...

[yannK · Accepted solution]

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]

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