Splunk Search

How to monitor transaction duration, but only account from 8AM to 6PM?

moisesroth
Path Finder

I have the fallowing search:

index="my_app" p_id=635392908992408562
| transaction p_id
| eval starttime=strftime(_time,"%Y-%m-%d %H:%M:%S")  
| eval endtime=if(duration!=0, strftime(_time+duration,"%Y-%m-%d %H:%M:%S"), "") 
| eval duration=if(duration!=0, tostring(duration,"duration"), "")
| table p_id starttime endtime duration

The result was:
alt text

Desired result:
In this case the duration should be 02:41:28, not 13:18:07, because the SLA should be account only between 8AM to 6PM.

Someone can help me to solve this issue?

Tags (2)
0 Karma
1 Solution

cmerriman
Super Champion

would something like this help as a starting point:

index="my_app" p_id=635392908992408562
 | transaction p_id
|eval starttime1=_time
|eval enddtime1=if(duration!=0,_time+duration, "")
|eval hours1=mvrange(starttime1,endtime1,3600)
|convert ctime(hours1) timeformat="%m/%d/%Y %H:%M:%S" 
|eval interestedHours=mvfilter(match(hours1,"( 08| 09| 10| 11| 12| 13| 14| 15| 16| 17| 18).*"))

that will get you a list of timestamps in hour intervals for every 8 am - 6 pm in your timeframe. you might want to do a eventstats maybe afterwards by p_id and date, perhaps, so that it resets every day, and then you can sum up the time frames each day. The caveat is that it wouldn't be exact H:M:S times, since it just adds a new timestamp every 3600 seconds.

View solution in original post

cmerriman
Super Champion

would something like this help as a starting point:

index="my_app" p_id=635392908992408562
 | transaction p_id
|eval starttime1=_time
|eval enddtime1=if(duration!=0,_time+duration, "")
|eval hours1=mvrange(starttime1,endtime1,3600)
|convert ctime(hours1) timeformat="%m/%d/%Y %H:%M:%S" 
|eval interestedHours=mvfilter(match(hours1,"( 08| 09| 10| 11| 12| 13| 14| 15| 16| 17| 18).*"))

that will get you a list of timestamps in hour intervals for every 8 am - 6 pm in your timeframe. you might want to do a eventstats maybe afterwards by p_id and date, perhaps, so that it resets every day, and then you can sum up the time frames each day. The caveat is that it wouldn't be exact H:M:S times, since it just adds a new timestamp every 3600 seconds.

View solution in original post

moisesroth
Path Finder

Thanks! It works after some adaptations.
PS: There is an error at variable enddtime1, the correct should be endtime1

The final code for my environment was something like this:
One observation: Because I'm using minutes to count the total SLA, the regular expression used to validate from 08 to 18 should stop as 17, because 17 means 17:00, 17:01 ....17:59.

index="my_app" p_id=635392908992408562
| transaction p_id

| eval starttime=_time
| eval endtime=_time+duration
| eval my_minutes=mvrange(starttime,endtime,60)
| convert ctime(my_minutes) timeformat="%Y-%m-%d %H:%M:%S"
| eval interestedMinutes=mvfilter(match(my_minutes,"( 08| 09| 10| 11| 12| 13| 14| 15| 16| 17).*"))
| eval SLA_Minutes=mvcount(interestedMinutes)
| eval SLA_Time=tostring(SLA_Minutes*60,"duration") 

| eval h_starttime=strftime(starttime,"%Y-%m-%d %H:%M:%S") 
| eval h_endtime=if(duration!=0, strftime(starttime+duration,"%Y-%m-%d %H:%M:%S"), "") 
| eval h_duration=if(duration!=0, tostring(duration,"duration"), "") 

| table p_id h_starttime h_endtime SLA_Time
0 Karma

cmerriman
Super Champion

awesome, i'm glad it helped. i use a similar code for calculating weekdays v weekends, so i thought it could be similar for hours.

0 Karma

somesoni2
Revered Legend

What should be the duration if the starttime was 2017-05-15 14:00:00 with same endtime?

0 Karma

moisesroth
Path Finder
starttime 2017-05-15 14:00:00 and endtime 2017-05-16 10:41:28

The result should be 06:41:28.

0 Karma

somesoni2
Revered Legend

Getting complex 🙂 . What should be the duration if the starttime was 2017-05-14 14:00:00 with same endtime (stretched to more than a day)? Also, will there be cases where endtime be outside your SLA windows (falls between 6PM and 8 AM next day)?

0 Karma

moisesroth
Path Finder

1)
starttime 2017-05-14 14:00:00 and endtime 2017-05-16 10:41:28
The result should be 18:41:28.

On detail the calculation will be like below:
D14 - 14:00:00 to 18:00:00 = 04:00:00
D15 - 06:00:00 to 18:00:00 = 12:00:00
D16 - 06:00:00 to 10:41:28 = 02:41:28
SLA = 04h + 12h + 02:41:28 = 18:41:28

2)
Every starttime or endtime will be consider to the next valid time.
e.g:
19:00 will be consider next day at 08:00
06:00 will be consider current day at 08:00

0 Karma