Run a monthly Performance report excluding maintenance hours on specific days

[nmohammed]

We have month performance reports on Calls and errors metrics, with a few calculations to do generate a application performance report. The report again has data broken into hourly buckets per day.
But since we have regular maintenance windows and code freeze end of month, we need to generate the reports accordingly.

I need help in excluding metrics for these maintenance hours.

Tuesday - starts 10:00 PM PT and ends at 02:00 AM PT Wednesday
Thursday - starts 10:00 PM PT and ends at 02:00 AM PT Friday

i have tried the following, but it doesn't work out ...

search | (wday=Tuesday OR wday=Thursday) AND ((date_hour>=22 AND date_minute>=00) AND (date_hour<=1 AND date_minute<=59)

also above mentioned maintenance hours are not in effect last 10 days of month, so it should be regular report.

[somesoni2 · Accepted solution]

Try something like this (assuming date_* fields are available in your logs)

your base search | search NOT (_time<relative_time(_time,"@mon+1mon-10d") AND (date_wday="Tuesday" OR date_wday="Thursday") AND date_hour>=22) OR (date_wday="Wednesday" OR date_wday="Friday") AND date_hour<2) 

**Updated (fixed syntax on filter)#2 **
replace | search with | where and change case of date_wday

 (sourcetype=error OR sourcetype=info client=*) | where NOT (_time<relative_time(_time,"@mon+1mon-10d") AND (((date_wday="tuesday" OR date_wday="thursday") AND date_hour>=22) OR ((date_wday="wednesday" OR date_wday="friday") AND date_hour<2))) |bucket _time span=1h | stats count as Total count(eval(sourcetype=="info")) as Calls count(eval(sourcetype=="error")) as Errors count(eval(exec_time_ms > 3000)) as Heavy by _time, client | eval QoS = round(((Total-Errors)/Total)*100, 3) | eval Perf = round((1-(Heavy/Calls))*100, 3)

[nmohammed]

SomeSoni2,

I tried to work this out with tstats and data models . But it again doesn't exclude filters. I am able to see all the hours.

|tstats avg(ABC.exec_time) as exec_time FROM datamodel=ABC where sourcetype=abc groupby ABC.transaction, ABC.client, _time span=1h | eval date_hour=tonumber(strftime(_time,"%H")) | eval date_wday = strftime(_time,"%A") | where NOT (_time=22) OR ((date_wday="wednesday" OR date_wday="friday") AND date_hour<2) OR ((date_wday="saturday") AND date_hour>=16) OR ((date_wday="sunday") AND date_hour<4))) | bucket _time span=1h |stats avg(exec_time) as "Response Time" by _time, ABC.transaction, ABC.client

[somesoni2]

If you're using the same set of filters, ensure that where clause is this

| where NOT (_time<relative_time(_time,"@mon+1mon-10d") AND (((date_wday="tuesday" OR date_wday="thursday") AND date_hour>=22) OR ((date_wday="wednesday" OR date_wday="friday") AND date_hour<2)))

[nmohammed]

Actually, I pasted it wrong in the reply.., I was using the filer correctly, but had to change eval date_wday = (strftime(_time,"%A")) to eval date_wday = lower(strftime(_time,"%A")) and it worked.

|tstats avg(ABC.exec_time) as exec_time FROM datamodel=ABC where sourcetype=abc groupby ABC.transaction, ABC.client, _time span=1h | eval date_hour=tonumber(strftime(_time,"%H")) | eval date_wday = lower(strftime(_time,"%A")) | where NOT NOT (_time=22) OR ((date_wday="wednesday" OR date_wday="friday") AND date_hour<2) OR ((date_wday="saturday") AND date_hour>=16) OR ((date_wday="sunday") AND date_hour<4))) | bucket _time span=1h |stats avg(exec_time) as "Response Time" by _time, ABC.transaction, ABC.client

[nmohammed]

Ran the report again with the new updated query (NOT) condition . It worked perfectly, validated the results.

Thank you so much Somesoni2.

[nmohammed]

Somesoni2,

could you help me address this ? I have tried it again.. basically when we filter it out using (_time

[somesoni2]

I guess the comparison based on date_wday wasn't working. Try the updated answer.

[nmohammed]

I tried the updated query, but it does not eliminate the maintenance hours :

(date_wday="Tuesday" OR date_wday="Thursday") AND date_hour>=22) OR ((date_wday="Wednesday" OR date_wday="Friday") AND date_hour<2)

• I verified this by running the report for Jan. Example, Jan 5th, 2016 is Tuesday, if above condition is met , then the hours from 22:00 and 23:00 should be excluded in the report, but I am still seeing numbers for those hours.

[nmohammed]

Thanks Somesoni2

I have tried the above expression you mentioned in the answer, but it gives an error for unbalanced parentheses.

Then I modified it to the following:

your base search | search NOT (_time=22) OR ((date_wday="Wednesday" OR date_wday="Friday") AND date_hour<=2))

But this one still includes results from those hours.

I also removed the relative time condition, to eliminate the freeze (last 10days) and just try with the following (this one worked):

your base search| search NOT ((date_wday="Tuesday" OR date_wday="Thursday") AND date_hour>=22) OR ((date_wday="Wednesday" OR date_wday="Friday") AND date_hour<=2)

Can you suggest where I am going wrong in order to achieve my overall goal to eliminate events from freeze with maintenance windows?

This is the exact search, I am using:

(sourcetype=error OR sourcetype=info client=*) | search NOT (_time=22) OR ((date_wday="Wednesday" OR date_wday="Friday") AND date_hour<=2)) |bucket _time span=1h | stats count as Total count(eval(sourcetype=="info")) as Calls count(eval(sourcetype=="error")) as Errors count(eval(exec_time_ms > 3000)) as Heavy by _time, client | eval QoS = round(((Total-Errors)/Total)*100, 3) | eval Perf = round((1-(Heavy/Calls))*100, 3)

Thanks again, appreciate your help.

[somesoni2]

I believe the updated query should take care of both freeze period and maintenance period.