Splunk Search

How do i calculate the average response time to exclude weekends and holidays

Explorer

i was searching in Splunk how to represent the days but no luck, i am going wrong somewhere

this is my input

Submit Date / Creation Date Time Stamp    Incident Response Date Time
09/14/2016 01:14 AM                       09/14/2016 01:19 AM

and my search that calculates the average esponse time and display on a graph is as follows:

| search query
| eval created=strptime(Created_Date, "%d-%m-%Y %H:%M") 
| eval resolution=strptime(Resolution_Date, "%d-%m-%Y %H:%M")
| eval difference=abs (resolution - created)
| eval diff_in_minutes=(difference/60)
| eval diff_in_minutes=tostring(round(diff_in_minutes))
| eval month = strftime(created, "%b-%Y")
| chart avg(diff_in_minutes) over month by Severity

there are possibilities where the time difference should exclude the weekends and also consider the holidays if any like: 25th Dec, 1st Jan etc.

Is it possible to amend it in such a way?

0 Karma

Communicator

index=index_name date_wday="monday" OR date_wday="tuesday" OR date_wday="wednesday" OR date_wday="thursday" OR date_wday="friday" l stats...............

I had experienced equal will work faster than not equal

So use this query for faster results

0 Karma

Legend

Hi rijinc,
If you need to exclude only weekend you have to exclude saturday and sunday using

NOT (date_wday=saturday OR date_wday=sunday) in you search.

If instead you want to manage all the holidays and maybe also the middle time days, you have to create a lookup with all the year days indicating working day (0), middle working day (1) and holiday (2).
In addition you could also exclude (or include) extra time (e.g. from the 19 PM to the 7,45 AM).
Adding to your searches a condition like this (I inserted it in a macro):

 | eval TimeStamp_day=strftime(_time,"%Y-%m-%d") 
 | lookup CAL.csv day AS TimeStamp_day OUTPUT Type
 | eval hours=strftime(_time,"%H")
 | eval minutes=strftime(_time,"%M")
 | search Type=2 OR (Type=1 (hours>14 OR (hours<7 AND minutes<45))) OR (Type=0 (hours>20 OR (hours<8 AND minutes<45)))

Bye.
Giuseppe

0 Karma

Champion

Assuming english names of weekdays, you could add the following to your initial search query to only allow weekdays.

search query [| gentimes start=-1 | eval date_wday="monday tuesday wednesday thursday friday" | makemv date_wday | mvexpand date_wday | table date_wday | format] | ...

As far as holidays go, that gets a bit harder accounting for leap years. You might be better off leaving them in or removing statistical outliers.

0 Karma

Legend

For holidays, you will need to create a lookup table (holidaylist.csv) with two fields date & holiday. Date field should have date in %d-%m-%Y format. Once you create this lookup, you can reference that to exclude holiday from your data. The following should get you started...

| search query
| eval created=strptime(Created_Date, "%d-%m-%Y %H:%M") 
| eval resolution=strptime(Resolution_Date, "%d-%m-%Y %H:%M")
| eval wip=mvrange(created, resolution, "1d")
| mvexpand wip
| eval wip=strftime(wip, "%d-%m-%Y")
| lookup holidaylist.csv date AS wip OUTPUT holiday AS holiday
| eval holiday=strftime(wip, "%a")
| eval difference=abs (resolution - created)
| stats values(eval(if(isnull(holiday), difference, null()))) as diff count(evalI(isnotnull(holiday))) as holidays by tktid
| eval difference=diff-(holidays*86400)
| eval diff_in_minutes=(difference/60)
| eval diff_in_minutes=tostring(round(diff_in_minutes))
| eval month = strftime(created, "%b-%Y")
| chart avg(diff_in_minutes) over month by Severity