Reporting

Run a monthly Performance report excluding maintenance hours on specific days

nmohammed
Contributor

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.

0 Karma
1 Solution

somesoni2
Revered Legend

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)

View solution in original post

0 Karma

somesoni2
Revered Legend

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)
0 Karma

nmohammed
Contributor

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

0 Karma

somesoni2
Revered Legend

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)))
0 Karma

nmohammed
Contributor

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

0 Karma

nmohammed
Contributor

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

Thank you so much Somesoni2.

0 Karma

nmohammed
Contributor

Somesoni2,

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

0 Karma

somesoni2
Revered Legend

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

0 Karma

nmohammed
Contributor

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.
0 Karma

nmohammed
Contributor

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.

0 Karma

somesoni2
Revered Legend

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

0 Karma
Get Updates on the Splunk Community!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...