topic Re: How can I exclude a specific recurring time range from my search? in Getting Data In

How can I exclude a specific recurring time range from my search?

qbolbk59 — Fri, 06 Oct 2017 14:11:16 GMT

Hi all,

I need to create a dashboard which can provide me the total review time taken by the analyst. I have created the following query:

| datamodel Incident_Management Notable_Events search | stats earliest(_time) as _time by rule_id  | `drop_dm_object_name("Notable_Events")`| `get_correlations` | join rule_id [| datamodel Incident_Management Incident_Review search  | stats earliest(_time) as reviewtime by Incident_Review.rule_id,Incident_Review.reviewer_realname| `drop_dm_object_name("Incident_Review")`] | eval tot=reviewtime-_time | stats count,avg(tot) as avg_tot,max(tot) as max_tot ,min(tot) as min_tot by reviewer_realname | sort - avg_tot | `uptime2string(avg_tot, avg_tot)` | `uptime2string(max_tot, max_tot)` | `uptime2string(min_tot, min_tot)` |rename *_tot* as *_time_to_review* | fields - *_dec

This is working fine and giving me results close to my expectations. However i don't need to include the off-business hours in the review time. For e.g., if i acknowledged and alert today and i closed it tomorrow, the total review time should not have the Off-business hour time (possibly 8-10 hours) and it should get subtracted.

Can anybody help me here on this issue ?

Re: How can I exclude a specific recurring time range from my search?

Sukisen1981 — Sun, 08 Oct 2017 09:06:53 GMT

this is an interesting problem...just to be clear-
eval tot=reviewtime-_time
review time is like a start time and the _time is like an end time for each ticket?
basically, you already have for each event the reviewtime like say
event reviewtime(start time) is 10:00 am on a monday
and the
_time (end time) is like say 10:00 AM on Wednesday?
so assuming an 8 hr work day 8:00 - 16:00
your eval tot should be 6 hrs (mon) + 8 hrs(tue) + 2 hrs(wed)= 16?

Re: How can I exclude a specific recurring time range from my search?

DalJeanis — Tue, 10 Oct 2017 15:39:39 GMT

If you absolutely never have a situation where an analyst works on a ticket for more than one work day... i.e., never count more than the work hours during the first and second day :

 | eval daydiff= floor(reviewtime/86400) - floor(_time/86400)
 | eval tot = case(daydiff<=0, reviewtime - _time,
                   daydiff=1, reviewtime - _time - 16*3600,
                   daydiff>1, reviewtime - _time + 8* 3600 - daydiff*86400)

On the other hand, if you want to count all 9-5 hours regardless of weekday or weekend, use this:

 | eval daydiff= floor(reviewtime/86400) - floor(_time/86400)
 | eval tot = case(daydiff<=0, reviewtime - _time,
                   daydiff>=1, reviewtime - _time - daydiff*16*3600)

And, if you want to count off for weekends, but not for weekdays, then there will be some complicated logic that I can't rattle off without checking the time functions.

Re: How can I exclude a specific recurring time range from my search?

qbolbk59 — Sun, 15 Oct 2017 15:03:06 GMT

Hi @DalJeanis

Thanks for helping. The normal working hours is 12 hours a day and 7 days a week. so if an alert was triggered today and it got closed tomorrow, i need to remove the 12 off business hours from the total time taken. similarly if it got closed after two days, i need to remove 24 hours (2*12) and so on. I used the logic provided by you and modified it a bit and now have used the below query and it seems that now it's rendering me exact results.

| datamodel Incident_Management Notable_Events search | stats earliest(_time) as _time by rule_id | `get_correlations` | join rule_id [| datamodel Incident_Management Incident_Review search  | stats earliest(_time) as reviewtime by Incident_Review.rule_id,reviewer_realname | `drop_dm_object_name("Incident_Review")`] | eval tot=reviewtime-_time |eval tot1=round(tot/86400) | eval tot2=(tot-(tot1*43200)) |stats count,avg(tot2) as avg_tot,max(tot2) as max_tot ,min(tot2) as min_tot by reviewer_realname | sort - avg_tot | `uptime2string(avg_tot, avg_tot)` | `uptime2string(max_tot, max_tot)` | `uptime2string(min_tot, min_tot)` |rename *_tot* as *_time_to_review* | fields - *_dec

Re: How can I exclude a specific recurring time range from my search?

qbolbk59 — Sun, 15 Oct 2017 15:13:13 GMT

It's not exact result, but close to the expectations. Needs more tweaking.