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:
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.
This should process all tickets for the past thirty days.
Finally, once we have a duration, I would need to calculate the average duration for severity levels 0-6.
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...
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
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
That is part of his search -> '| transaction serverName serverSerial ticketNumber'