Calculate time difference, then calculate average duration per severity level

[DTERM]

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.

[ayme · Accepted solution]

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