custom query

[Harish2]

|tstats count where index=app-idx host="*abfd*" sourcetype=app-source-logs by host

This is my alert query, i want to modify the query so that i wont receive alert at certain times.
For example: Every month like on 10 , 18, 25 and during 8am to 11am i don't want to get the alerts.
Rest all for other days its should work as normal.

how can i do it???

[gcusello]

Hi @Harish2 ,

you have to exclude from results the days and the hours, something like this:

| tstats 
   count 
   latest(_time) as _time
   WHERE index=app-idx host="*abfd*" sourcetype=app-source-logs 
   BY host
| eval 
   day=strftime(_time, "%d"), 
   hour=strftime(_time, "%H")
| where NOT (day IN (10, 18, 25) OR hour<8 OR hour>11)
| fields - _time day hour

You can also check weekends and holydays, using using a lookup containing the holydays, e.g.:

date type
2024-03-29 ferial
2024-03-30 weekend
2024-04-31 weekend
2024-04-01 holyday
2024-04-02 ferial
2024-04-03 ferial
2024-04-04 ferial
2024-04-05 weekend
2024-04-06 weekend
2024-04-07 ferial

and running something like this:

| tstats 
   count 
   latest(_time) as _time
   WHERE index=app-idx host="*abfd*" sourcetype=app-source-logs 
   BY host
| eval 
   day=strftime(_time, "%d"), 
   hour=strftime(_time, "%H"),
   date=strftime(_time, "%Y-%m-%d
|lookup calendsr.csv date OUTPUT type
| where NOT (day IN (10, 18, 25) OR hour<8 OR hour>11 OR type IN ("weekend", "holyday"))
| fields - _time day hour

Ciao.

Giuseppe

[Harish2]

Hi @gcusello , Thank you so much, you gave me exactly what i want. but i tried but i don't want to add any date or hours in the query i added  them in the csv file and run the below query still i am receiving the alerts.
can you please let me know what i am missing.

And i want to add time also in the csv file, and link to the query so that during mentioned time and date my alert should not trigger.
please help me on that

| tstats 
   count 
   latest(_time) as _time
   WHERE index=app-idx host="*abfd*" sourcetype=app-source-logs 
   BY host
|appendcols
[|makeresults
   |eval Today=date=strftime(_time, "%m/%d/%Y")
|lookup calendsr.csv date OUTPUT type
|eval type=if(isnotnull)(type),type,"NotHoliday"]

[gcusello]

Hi @Harish2 ,

your search isn't correct, there are some syntax errors.

| tstats 
   count 
   latest(_time) as _time
   WHERE index=app-idx host="*abfd*" sourcetype=app-source-logs 
   BY host
| appendcols [ | makeresults
   | eval date=strftime(_time, "%m/%d/%Y")
   | lookup calendsr.csv date OUTPUT type
   | eval type=if(isnotnull(type),type,"NotHoliday"]

Anyway, not using the hours , you check only a part of the requirementas you described.

Ciao.

Giuseppe

[Harish2]

i tried the query u provided i am receiving the alerts.
not sure what i am missing

[gcusello]

Hi @Harish2 ,

check the hours of these events, if they match the condition of your search.

Ciao.

Giuseppe

[Harish2]

Hi @gcusello , my goal is to i don't want to receive the alerts  during certain days. For example in csv file i gave todays date.  My alert condition is count >0, corn job is 15mins  for last 15 minutes. time range.

Used below query still i am receiving alerts.

| tstats 
   count 
   latest(_time) as _time
   WHERE index=app-idx host="*abfd*" sourcetype=app-source-logs 
   BY host
|where count >0
| appendcols [ | makeresults
   | eval date=strftime(_time, "%m/%d/%Y")
   | lookup calendsr.csv date OUTPUT type
   | eval type=if(isnotnull(type),type,"NotHoliday"]

[gcusello]

Hi @Harish2 ,

instead of appendcols thet run as it prefer, please try this:

| tstats 
   count 
   latest(_time) as _time
   WHERE index=app-idx host="*abfd*" sourcetype=app-source-logs NOT [ | inputlookup calendsr.csv WHERE type="holyday" | fields date ]
   BY host

Ciao.

Giuseppe

[Harish2]

Hi @gcusello , i tried the query, still  i am getting alerts

[gcusello]

Hi @Harish2 ,

sorry there was an error:

| tstats 
   count 
   latest(_time) as _time
   WHERE index=app-idx host="*abfd*" sourcetype=app-source-logs 
   BY host
| eval date=strftime(_time,"%Y-%m-%d")
| search NOT [ | inputlookup calendsr.csv WHERE type="holyday" | fields date ]

in in the lookup calendar.csv you have

date   type
2024-03-08 normal
2024-03-09 holyday
2024-03-10 holyday

the alert will not trigger in the 2024-03-09.

Ciao.

Giuseppe

[Harish2]

Hi @gcusello , Thank you so much the query you provided worked.
But when  i am trying to add time its not working, please find the below query:
Can you please help on this???

| tstats 
   count 
   latest(_time) as _time
   WHERE index=app-idx host="*abfd*" sourcetype=app-source-logs 
   BY host
| eval date=strftime(_time,"%Y-%m-%d %H:%M")
| search NOT [ | inputlookup calendsr.csv WHERE type="holyday" | fields date ]

csv file as below

date   type
2024-03-08 12:00 normal
2024-03-09 10:00 holyday
2024-03-09 12:00 holyday
2024-03-09 18:00 holyday
2024-03-09 23:00 holyday
2024-03-10 14:00 holyday
2024-03-10 18:00 holyday
2024-03-10 22:00 holyday

[gcusello]

Hi @Harish2 ,

it's clear, in your event's you haven't the starting hours (12:00).

As I described in my first answer, you have to manage hours in a different way (outside the lookup):

| tstats 
   count 
   latest(_time) as _time
   WHERE index=app-idx host="*abfd*" sourcetype=app-source-logs 
   BY host
| eval 
   date=strftime(_time,"%Y-%m-%d"),
   day=strftime(_time, "%d"), 
   hour=strftime(_time, "%H")
| search NOT (hour<8 OR hour>11 OR [ | inputlookup calendsr.csv WHERE type="holyday" | fields date ] )
| fields - _time day hour

obviously, using the date in the lookup without hours and minutes.

Ciao.

Giuseppe

[Harish2]

Hi @gcusello , is there is a way where I can update the hours in csv file not in the query.

can we do that ???

[gcusello]

Hi @Harish2 ,

no it's the easiest and flexible way, but why you don't want to use the hours and minutes in the search?

you can also create a macro to call instead adding all the conditions to your searches.

Ciao.

Giuseppe

[Harish2]

yes events are present, my alert condition is results greater than zeros for last 15 minutes. but as per my requirement i mentioned todays date in the csv file,  so alert should not trigger right

[gcusello]

Hi @Harish2 ,

the condition you required is that hour must be NOT hour<8 OR hour>11.

Are the hours of the events in the results compliant with this condition?

maybe you should change the hour condition.

Ciao.

Giuseppe

[yuanliu]

Something like

|tstats count max(_time) as _time ``` you will need latest _time ```
  where index=app-idx host="*abfd*" sourcetype=app-source-logs by host
| eval dom = strftime(_time, "%m"), hod = strftime(_time, "%H")
| where NOT dom IN (10, 18, 25) AND (8 > hod OR hod > 11) ``` whether AND or OR depends on exact semantic ```
| fields - _time dom hod

Several points of discussion.

• Semantics of "on 10 , 18, 25 and during 8am to 11am" is very loose in English.  Two opposite interpretations can be conveyed by this same phrase.  So you need to tune the logic according to your intention.
• _time is taken as the latest in dataset by host.  Depending on your data density, you may want to take some other approach, such as info_endtime.
• It is probably better to code your exclusions in a CSV than hard code in search.  But that's out of the scope of this question.

[Harish2]

Hi @yuanliu , thank you for the inputs.

As we have more number of alerts to be done. We want to go with CSV option.

I will create CSV file and will add time,  date and month, but I am not sure how to link with that in query 

Can you please help me on that 

[yuanliu]

Assuming you make a lookup called silence.csv with the following:

exclude_days	silence_start	silence_end
10,18,25	8	11

you can do something liie

| tstats count max(_time) as _time ``` you will need latest _time ```
  where index=app-idx host="*abfd*" sourcetype=app-source-logs by host
| eval dom = strftime(_time, "%m"), hod = strftime(_time, "%H")
| append
    [inputlookup exclusions.csv
    | eval exclude_days = split(exclude_days, ",")]
| eventstats values(exclude_days) as exclude_days values(silence_*) as silent_*
| where NOT dom IN exclude_days AND (silence_start > hod OR hod > silence_end) ``` whether AND or OR depends on exact semantic ```
| fields - _time dom hod

But note if you have lots of hosts with alert, this can get slow.  This is a method that is flexible to implement either of possible intentions in a similar fashion.  If performance becomes a problem, you will need optimize for the exact intent.