Transaction : How to exclude Night hour and Week-end period time of duration calculation

[romux]

Hi,

For calculate Application unavailable Time on Workhours, I try to find a solution to exclude period time :

• 7PM to 7am no workhours time
• Week-end period time

But duration keep elapsetime of Saturday and Sunday when my transaction during some days.

Do you have an tricks&tips to exclude that because my SPL is very very long and I can't take again all case?

index=xxx_appli_alerts (scenario=APPLI1) NOT scenario=PERF_* NOT criticity=HP
| transaction scenario startswith=eval(status!="RECOVERY") endswith=eval(status="RECOVERY")

| eval starttime=strftime(_time, "%Y-%m-%d %H:%M:%S") 
| eval endtime=strftime(_time+duration, "%Y-%m-%d %H:%M:%S")
| eval number_of_day=strftime(duration, "%d")
| eval hour_of_the_starttime=strftime(_time, "%H") 
| eval hour_of_the_endtime=strftime(_time+duration, "%H") 
| eval anomaly_hno=if((strftime(_time,"%d") != strftime(_time+duration,"%d")),"YES","NO") 
| eval anomaly_weekend=if(((strftime(_time,"%w") == "5" AND hour_of_the_starttime >= "19") OR strftime(_time,"%w") == "6" OR strftime(_time,"%w") == "0") AND (strftime(_time,"%d") != strftime(_time+duration,"%d")),"YES","NO")
| eval hno_period=(19-7)*60*60*(number_of_day-1)

| eval work_start=if(hour_of_the_starttime >=19 OR hour_of_the_starttime < 7,"HNO", "HO")
| eval work_end=if(hour_of_the_endtime >=19 OR hour_of_the_endtime < 7,"HNO", "HO")

| eval day_of_week=strftime(_time,"%w")

| eval starttime=case(
anomaly_weekend=="YES", strftime(relative_time(_time,"+w@w1+7h"),"%Y-%m-%d %H:%M:%S"),
(work_start=="HNO" AND work_end=="HO") AND hour_of_the_starttime >=19 ,strftime(relative_time(_time,"@d+1d+7h"),"%Y-%m-%d %H:%M:%S"),
(work_start=="HNO" AND work_end=="HO") AND hour_of_the_starttime < 7, strftime(_time, "%Y-%m-%d 07:00:00"),
(work_start=="HNO" AND work_end=="HNO") OR (work_start=="HO" AND work_end=="HO") OR (work_end=="HNO" AND work_start=="HO"),starttime
)

| eval endtime=case(
(work_end=="HNO" AND work_start=="HO") AND hour_of_the_endtime >=19 ,strftime(relative_time(_time+duration,"@d+19h"),"%Y-%m-%d %H:%M:%S"),
(work_end=="HNO" AND work_start=="HO") AND hour_of_the_endtime < 7, strftime(_time+duration, "%Y-%m-%d 19:00:00"),
(work_start=="HNO" AND work_end=="HNO") OR (work_start=="HO" AND work_end=="HO") OR (work_start=="HNO" AND work_end=="HO"),endtime)

| eval calcul_hno=(strptime(strftime(_time+duration,"%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")-strptime(strftime(relative_time(_time+duration,"@d+7h"),"%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S"))+(strptime(strftime(relative_time(_time,"@d+19h"),"%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")-strptime(strftime(_time,"%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S"))

| eval duration=case(
anomaly_hno=="YES" AND anomaly_weekend=="YES", strptime(endtime,"%Y-%m-%d %H:%M:%S")-strptime(starttime,"%Y-%m-%d %H:%M:%S"),
anomaly_hno=="NO" AND anomaly_weekend=="YES", strptime(endtime,"%Y-%m-%d %H:%M:%S")-strptime(starttime,"%Y-%m-%d %H:%M:%S"),
anomaly_hno=="NO" AND anomaly_weekend=="NO", strptime(endtime,"%Y-%m-%d %H:%M:%S")-strptime(starttime,"%Y-%m-%d %H:%M:%S"),
anomaly_hno=="YES" AND anomaly_weekend=="NO" AND day_of_week!=5, strptime(endtime,"%Y-%m-%d %H:%M:%S")-strptime(starttime,"%Y-%m-%d %H:%M:%S")-hno_period,
anomaly_hno=="YES" AND anomaly_weekend=="NO" AND day_of_week=5, calcul_hno
)

| dedup _raw | search NOT (work_start="HNO" AND work_end="HNO")
| lookup plage_unavailable.csv application AS scenario OUTPUT datedeb datefin | where NOT (strptime(starttime,"%Y-%m-%d %H:%M:%S") > strptime(datedeb,"%Y-%m-%d %H:%M:%S") AND strptime(starttime,"%Y-%m-%d %H:%M:%S") < strptime(datefin,"%Y-%m-%d %H:%M:%S")) | stats  values(endtime) as endtime first(step) as step values(criticity) as bundle sum(duration) as duration values(work_start) values(work_end) values(anomaly_hno) values(anomaly_weekend) values(day_of_week) values(calcul_hno) values(number_of_day) by starttime, scenario 
| eval duration=round(duration,0)
| replace C1 WITH GOLD IN bundle 
| replace C2 WITH SILVER in bundle 
| addcoltotals label=duration | eval duration=tostring(duration,"duration")

[nickhills]

I use an external lookup to calculate business hours/weekends!

This is a very simplistic version, but hopefully enough to get you going if you want to go this route.

#!/usr/bin/python
import sys, csv
import datetime as dt

startHour = 8
endHour = 17
startWeek = 1 #Monday
endWeek = 5 #Friday

_time = sys.argv[1]
businesshours = sys.argv[2]

infile = sys.stdin
outfile = sys.stdout

r = csv.DictReader(infile)
header = r.fieldnames

w = csv.DictWriter(outfile, fieldnames=r.fieldnames)
w.writeheader()

for result in r:
        if result[_time]:
                timestamp = dt.datetime.utcfromtimestamp(float(result[_time]))
                weekday = dt.datetime.isoweekday(timestamp)

                if endWeek >= weekday:

                        if endHour > timestamp.hour > startHour:
                                result[businesshours] = "businesshours"
                                w.writerow(result)
                        else:
                                result[businesshours] = "notbusinesshours"
                                w.writerow(result)
                else:
                        result[businesshours] = "weekend"
                        w.writerow(result)

[nickhills]

Did this help you? If you found it useful, please be sure to accept/upvote any posts which helped, as it provides useful feedback for future viewers of your question. Good luck!