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")
... View more