topic Re: custom query in Splunk Search

custom query

Harish2 — Wed, 06 Mar 2024 21:02:52 GMT

|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???

Re: custom query

yuanliu — Thu, 07 Mar 2024 03:15:22 GMT

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.

Re: custom query

Harish2 — Wed, 06 Mar 2024 23:42:35 GMT

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

Re: custom query

yuanliu — Thu, 07 Mar 2024 04:55:30 GMT

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.

Re: custom query

gcusello — Thu, 07 Mar 2024 07:08:55 GMT

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

Re: custom query

Harish2 — Fri, 08 Mar 2024 14:47:28 GMT

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"]

Re: custom query

gcusello — Fri, 08 Mar 2024 14:57:55 GMT

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

Re: custom query

Harish2 — Fri, 08 Mar 2024 15:43:03 GMT

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

Re: custom query

gcusello — Fri, 08 Mar 2024 15:45:16 GMT

Hi @Harish2 ,

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

Ciao.

Giuseppe

Re: custom query

Harish2 — Fri, 08 Mar 2024 15:48:33 GMT

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

Re: custom query

gcusello — Fri, 08 Mar 2024 15:53:21 GMT

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

Re: custom query

Harish2 — Fri, 08 Mar 2024 16:19:53 GMT

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.

Re: custom query

gcusello — Fri, 08 Mar 2024 16:56:24 GMT

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

Re: custom query

Harish2 — Fri, 08 Mar 2024 17:50:06 GMT

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

Re: custom query

gcusello — Fri, 08 Mar 2024 21:14:21 GMT

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

Re: custom query

Harish2 — Mon, 11 Mar 2024 00:55:09 GMT

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

Re: custom query

gcusello — Mon, 11 Mar 2024 07:54:15 GMT

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

Re: custom query

Harish2 — Mon, 11 Mar 2024 11:28:42 GMT

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

can we do that ???

Re: custom query

gcusello — Mon, 11 Mar 2024 13:51:04 GMT

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