Splunk Search

Complex query

DTERM
Contributor

I need a query that will provide the average duration of tickets for severity levels 0-4. The individual ticket duration is defined as actionTime (time) - FirstOccurance (time). The actionCode field must = I on the firstOccurance. The actionCode field must = D on the last occurrance. The serverName and serverSeria fields must match on both tickets.

A sample log entry follows:

lastOccurrence=2012/07/25 10:20:18|firstOccurrence=2012/07/25 09:36:17|writeTime=2012/07/25 18:23:58|actionCode=U|actionTime=2012/07/25 18:23:38|serverName=PRIME_SN1|serverSerial=3129|identifier=DC1mttrapd61 DC1 Host State - UP/DOWN|severity=0|alertText=PING OK - Packet loss = 0%, RTA = 0.02 ms host.domain.com+|businessUnit=CORPORATE|cmName=host.domain.com|deletedBy=Auto_Delete|dmName=None|hostGroup=|msgSrc=Nagios|nagios_duration=0d 0h 0m 0s|netcool_duration=|productName=POSS-VIT|serviceName=Host State - UP/DOWN|supportGroup=SYSADMIN|tally=78|tgtHostname=host1|ticketNumber=**

Tags (2)
0 Karma
1 Solution

Gilberto_Castil
Splunk Employee
Splunk Employee

It is difficult to address this item as there are some parts which are not clear. Nonetheless, allow me to make some educated assumptions about your use case. First, let's assume that a singular ticket may contain more than one log entry. Second, the first log entry for a ticket determines the value for the first occurrance. Third, the last log entry determines the last time the ticked was attended to.

With those assumptions, let's pretend that you have three log entries for your ticket. These are summarized in order to make this note more readable. The first entry in the logs is marked with "actionCode=I" and the last one is "actionCode=D".

  1. lastOccurrence=2012/07/25 10:20:18 | firstOccurrence=2012/07/25 09:36:17 | writeTime=2012/07/25 19:45:13 | actionCode=D | actionTime=2012/07/25 19:45:13 | serverName=PRIME_SN1 | serverSerial=3129

  2. lastOccurrence=2012/07/25 10:20:18 | firstOccurrence=2012/07/25 09:36:17 | writeTime=2012/07/25 18:52:22 | actionCode=U | actionTime=2012/07/25 18:52:22 | serverName=PRIME_SN1 | serverSerial=3129

  3. lastOccurrence=2012/07/25 10:20:18|firstOccurrence=2012/07/25 09:36:17 | writeTime=2012/07/25 18:23:38 | actionCode=I | actionTime=2012/07/25 18:23:38 | serverName=PRIME_SN1 | serverSerial=3129

Based on those wild assumptions, a search might look something like this:

sourcetype="complex_query"   
| eval ticket_end_time = if(actionCode=="D",strptime(actionTime,"%Y/%m/%d %H:%M:%S"),NULL)   
| 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  



As we dissect this search, we follow the logic you presented.

  1. get your data

    sourcetype="complex_query"

  2. The last occurrence is marked with actionCode = D AND the time is derived from the actionTime field. This is expressed in seconds.

    | eval ticket_end_time = if(actionCode=="D",strptime(actionTime,"%Y/%m/%d %H:%M:%S"),NULL)

  3. The first occurrence is marked with actionCode = I and the time is derived from the firstOccurrence field. This is expressed in seconds.

    | eval ticket_start_time = if(actionCode=="I",strptime(firstOccurrence,"%Y/%m/%d %H:%M:%S"),NULL)

  4. Create a relationship for the data records based on the serverName, serverSerial and possibly the ticketNumber.

    | transaction serverName serverSerial ticketNumber

  5. Evaluate the duration for the ticket. This is expressed in seconds.

    | eval ticketDuration = ticket_end_time - ticket_start_time

  6. Prettify the results into a human-readable format. In this case the delta is 36536 seconds and with this additional calculation this would read ''10 Hours 8 Min 56 Sec.''

    | eval pretty_ticketDuration = floor(ticketDuration/60/60)." Hours ".floor(floor(ticketDuration - (floor(ticketDuration/60/60)*60*60))/60)." Min ".floor(ticketDuration%60)." Sec."

  7. Get the average of the ticketDuration

    | stats avg(ticketDuration) AS Average_Ticket_Duration list(pretty_ticketDuration) AS pretty_ticketDuration by serverName serverSerial

There are multiple ways to accomplish what you are looking to do here. We may need more specifics and also a bit more data in order to fully satisfy your use case. But even if the search above, is way off, you should consider the srtptime and strftime functions as key contributors for your calculation. Also, the transaction command plays a pivotal role in creating a relationship between log entries in order to capture full context of your operational process.

Out of the search above you would see results like this:

ServerName serverSerial Average_Ticket_Duration pretty_ticketDuration
PRIME_SN1 3129 36536 10 Hours 8 Min 56 Sec.


I hope this helps.

View solution in original post

lguinn2
Legend

I like Gilberto's basic idea, but I think there is a simpler way to do it overall:

sourcetype="complex_query" (actionCode="U" OR action="D")
| eval lastTime=if(action=="U",strptime(actionTime,"%Y/%m/%d %H:%M:%S"),NULL)
| stats earliest(firstOccurrence) as startTime latest(lastTime) as endTime count(action="D") as ticketCompleted by ticketNumber
| where ticketCompleted > 0
| eval ticketDuration = lastTime - strptime(startTime,"%Y/%m/%d %H:%M:%S") 
| stats avg(ticketDuration) AS Average_Ticket_Duration by serverName serverSerial
| fieldformat Average_Ticket_Duration=tostring(Average_Ticket_Duration,"duration")

As I look at your log entry examples, I see that the events have both the firstOccurrence and the actionTime. So we don't need the I events in order to compute.

But perhaps we don't have a clear picture of the logs...

I updated this answer based on the comments - I did not have a clear picture of the logs! Hope this is better.

Also updated to correct a typo or two, and to eliminate tickets that did not have an event with action=D.

DTERM
Contributor

The actionCodes represents I - for the first instance of a ticket in the application, U- represents Updates to the ticket, and D - represent the the last ticket. There are many updates to the ticket during its span. I need to grab the last U for the "ticket_end_time". Almost like a "grep actionCode=U | tail -1".

0 Karma

lguinn2
Legend

So I changed my answer to use the action code "U" as the last ticket in the series.

But the search calculates the average duration of all tickets for each serverName and serverSerial. So I don't understand what you mean by "the serverName and serverSerial should be the same as other tickets in the series"

0 Karma

DTERM
Contributor

I have one modification to make to this request. The "ticket_end_time" needs to be changed to the last ticket where "actionCode=U" and the serverName and serverSerial should be the same as other tickets in the series. Thanks in advance.

0 Karma

Gilberto_Castil
Splunk Employee
Splunk Employee

It is difficult to address this item as there are some parts which are not clear. Nonetheless, allow me to make some educated assumptions about your use case. First, let's assume that a singular ticket may contain more than one log entry. Second, the first log entry for a ticket determines the value for the first occurrance. Third, the last log entry determines the last time the ticked was attended to.

With those assumptions, let's pretend that you have three log entries for your ticket. These are summarized in order to make this note more readable. The first entry in the logs is marked with "actionCode=I" and the last one is "actionCode=D".

  1. lastOccurrence=2012/07/25 10:20:18 | firstOccurrence=2012/07/25 09:36:17 | writeTime=2012/07/25 19:45:13 | actionCode=D | actionTime=2012/07/25 19:45:13 | serverName=PRIME_SN1 | serverSerial=3129

  2. lastOccurrence=2012/07/25 10:20:18 | firstOccurrence=2012/07/25 09:36:17 | writeTime=2012/07/25 18:52:22 | actionCode=U | actionTime=2012/07/25 18:52:22 | serverName=PRIME_SN1 | serverSerial=3129

  3. lastOccurrence=2012/07/25 10:20:18|firstOccurrence=2012/07/25 09:36:17 | writeTime=2012/07/25 18:23:38 | actionCode=I | actionTime=2012/07/25 18:23:38 | serverName=PRIME_SN1 | serverSerial=3129

Based on those wild assumptions, a search might look something like this:

sourcetype="complex_query"   
| eval ticket_end_time = if(actionCode=="D",strptime(actionTime,"%Y/%m/%d %H:%M:%S"),NULL)   
| 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  



As we dissect this search, we follow the logic you presented.

  1. get your data

    sourcetype="complex_query"

  2. The last occurrence is marked with actionCode = D AND the time is derived from the actionTime field. This is expressed in seconds.

    | eval ticket_end_time = if(actionCode=="D",strptime(actionTime,"%Y/%m/%d %H:%M:%S"),NULL)

  3. The first occurrence is marked with actionCode = I and the time is derived from the firstOccurrence field. This is expressed in seconds.

    | eval ticket_start_time = if(actionCode=="I",strptime(firstOccurrence,"%Y/%m/%d %H:%M:%S"),NULL)

  4. Create a relationship for the data records based on the serverName, serverSerial and possibly the ticketNumber.

    | transaction serverName serverSerial ticketNumber

  5. Evaluate the duration for the ticket. This is expressed in seconds.

    | eval ticketDuration = ticket_end_time - ticket_start_time

  6. Prettify the results into a human-readable format. In this case the delta is 36536 seconds and with this additional calculation this would read ''10 Hours 8 Min 56 Sec.''

    | eval pretty_ticketDuration = floor(ticketDuration/60/60)." Hours ".floor(floor(ticketDuration - (floor(ticketDuration/60/60)*60*60))/60)." Min ".floor(ticketDuration%60)." Sec."

  7. Get the average of the ticketDuration

    | stats avg(ticketDuration) AS Average_Ticket_Duration list(pretty_ticketDuration) AS pretty_ticketDuration by serverName serverSerial

There are multiple ways to accomplish what you are looking to do here. We may need more specifics and also a bit more data in order to fully satisfy your use case. But even if the search above, is way off, you should consider the srtptime and strftime functions as key contributors for your calculation. Also, the transaction command plays a pivotal role in creating a relationship between log entries in order to capture full context of your operational process.

Out of the search above you would see results like this:

ServerName serverSerial Average_Ticket_Duration pretty_ticketDuration
PRIME_SN1 3129 36536 10 Hours 8 Min 56 Sec.


I hope this helps.

DTERM
Contributor

It took me a long time to implement this but I got it. Thanks.

0 Karma

Gilberto_Castil
Splunk Employee
Splunk Employee

Very cool. You rock Lisa!

0 Karma

lguinn2
Legend

| 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

can be simplified to

| stats avg(ticketDuration) AS Average_Ticket_Duration by serverName serverSerial
| fieldformat Average_Ticket_Duration=tostring(Average_Ticket_Duration,"duration")

This makes steps 6 & 7 both shorter and more efficient

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