topic Re: How to trigger an alert week day without data? in Splunk Search

How to trigger an alert week day without data?

Gzuluaga — Tue, 17 May 2022 20:07:54 GMT

Hi, I'm pretty new in splunk, I've been reading a lot of documentation and other questions here, but I don't find the help that I need.

I have this search, every day is a left join like this:

index=myIndex sourcetype=mySource | eval weekday=strftime(_time,"%A") | where weekday = "Monday" | where Systems= "SYSTEM 1" OR "SYSTEM 2" OR "SYSTEM 3" OR "SYSTEM 4" | eval ExpectedTime = case( System="SYSTEM 1", "6:30am", System="SYSTEM 2", "6:35am", System="SYSTEM 3", "6:45am", System="SYSTEM 4", "6:40am" ) | eval CurrentSLO= case( System="SYSTEM 1", "7:15am", System="SYSTEM 2", "7:20am", System="SYSTEM 3", "7:10am", System="SYSTEM 4", "7:10am" ) | eval EndHour=substr(time, 50, 1) | eval EndMin=substr(time, 52, 2) | eval time = EndHour.":".EndMin | eval Mon = " (" .EndHour. ":" .EndMin. "am)" | eval category="CATEGORY 1" | table category Systems ExpectedTime CurrentSLO Mon Tue Wed Thu Fri | rename ExpectedTime as "Expected Time" | rename CurrentSLO as "Current SLO" | rename category as "Category" | join type=left Systems [ search index=myIndex sourcetype=mySource | eval weekday=strftime(_time,"%A") | where weekday = "Tusday" | where Systems= "SYSTEM 1" OR "SYSTEM 2" OR "SYSTEM 3" OR "SYSTEM 4" | eval ExpectedTime = case( System="SYSTEM 1", "6:30am", System="SYSTEM 2", "6:35am", System="SYSTEM 3", "6:45am", System="SYSTEM 4", "6:40am" ) | eval CurrentSLO= case( System="SYSTEM 1", "7:15am", System="SYSTEM 2", "7:20am", System="SYSTEM 3", "7:10am", System="SYSTEM 4", "7:10am" ) | eval EndHour=substr(time, 50, 1) | eval EndMin=substr(time, 52, 2) | eval time = EndHour.":".EndMin | eval Tue = " (" .EndHour. ":" .EndMin. "am)" | eval category="CATEGORY 1" | table category Systems ExpectedTime CurrentSLO Mon Tue Wed Thu Fri | rename ExpectedTime as "Expected Time" | rename CurrentSLO as "Current SLO" | rename category as "Category" . . .

I need to trigger an alert when there is no information for a day of the week.

I've been trying whit search count=0, transaction and other failed solution attempts.

Re: Trigger an alert Week day without data

somesoni2 — Tue, 17 May 2022 19:53:09 GMT

I would first optimize your search like this

index=myIndex sourcetype=mySource Systems IN ("SYSTEM 1" , "SYSTEM 2" , "SYSTEM 3" , "SYSTEM 4") | eval weekday=strftime(_time,"%A") | eval EndHour=substr(time, 50, 1) | eval EndMin=substr(time, 52, 2) | eval time = EndHour.":".EndMin | eval Time = " (" .EndHour. ":" .EndMin. "am)" | eval category="CATEGORY 1" | chart values(Time) over Systems by weekday | eval ExpectedTime = case( System="SYSTEM 1", "6:30am", System="SYSTEM 2", "6:35am", System="SYSTEM 3", "6:45am", System="SYSTEM 4", "6:40am" ) | eval CurrentSLO= case( System="SYSTEM 1", "7:15am", System="SYSTEM 2", "7:20am", System="SYSTEM 3", "7:10am", System="SYSTEM 4", "7:10am" ) | eval category="CATEGORY 1" | table category Systems ExpectedTime CurrentSLO Mon Tue Wed Thu Fri | rename ExpectedTime as "Expected Time" | rename CurrentSLO as "Current SLO" | rename category as "Category"

To generate alert on missing column value, just add following to above search:

| eval hasMissingValues="false" | foreach Mon Tue Wed Thu Fri [ | eval hasMissingValues=if(isnull('<<FIELD>>'),"true",hasMissingValues)] | where hasMissingValues="true"

Re: Trigger an alert Week day without data

Gzuluaga — Thu, 19 May 2022 15:19:32 GMT

Thanks @somesoni2 for your help!

Optimize the search as you taught me and added the lines to generate a missing column value, but it is always true even when there is data.

it's supposed would be false, right?

Because I'm thinking to use the field hasMissingValues="true" to trigger my alert.

Re: Trigger an alert Week day without data

Gzuluaga — Thu, 19 May 2022 14:35:44 GMT

@somesoni2

I'm realize if I remove Fri in the foreach (today is thursday) it works!

Now I'm thinking in a way to do the same dynamically, for example if today is Tuesday the foreach must only eval Monday and Tuesday.

Re: Trigger an alert Week day without data

somesoni2 — Thu, 19 May 2022 17:57:42 GMT

I forgot about days which have not come yet. So try this version (no hard coded week days)

inindex=myIndex sourcetype=mySource Systems IN ("SYSTEM 1" , "SYSTEM 2" , "SYSTEM 3" , "SYSTEM 4") | eval weekday="Wday-".strftime(_time,"%A") | eval EndHour=substr(time, 50, 1) | eval EndMin=substr(time, 52, 2) | eval time = EndHour.":".EndMin | eval Time = " (" .EndHour. ":" .EndMin. "am)" | eval category="CATEGORY 1" | chart values(Time) over Systems by weekday | eval ExpectedTime = case( System="SYSTEM 1", "6:30am", System="SYSTEM 2", "6:35am", System="SYSTEM 3", "6:45am", System="SYSTEM 4", "6:40am" ) | eval CurrentSLO= case( System="SYSTEM 1", "7:15am", System="SYSTEM 2", "7:20am", System="SYSTEM 3", "7:10am", System="SYSTEM 4", "7:10am" ) | eval category="CATEGORY 1" | table category Systems ExpectedTime CurrentSLO Wday-* | rename ExpectedTime as "Expected Time" | rename CurrentSLO as "Current SLO" | rename category as "Category" | eval hasMissingValues="false" | foreach Wday-* [ | eval hasMissingValues=if(isnull('<<FIELD>>'),"true",hasMissingValues)] | where hasMissingValues="true" | rename Wday-* as *

Re: Trigger an alert Week day without data

Gzuluaga — Thu, 19 May 2022 19:30:18 GMT

Thanks @somesoni2 but I don't understand your search, I mean, there is a

| eval weekday="Wday-".strftime(_time,"%A")

But then you are using the string "Wday-" to filter, but Wday is not a field in the search.

In my main search have Mon Tue Wed Thu Fri, those are calculated fields and indicate if there is data or not.

| table category Systems ExpectedTime CurrentSLO Mon Tue Wed Thu Fri

I understand your idea to filter, I'm trying to do that with every day (calculated fields)

Re: How to trigger an alert week day without data?

somesoni2 — Thu, 19 May 2022 20:01:50 GMT

Read comments inline below for explanation of the search

inindex=myIndex sourcetype=mySource Systems IN ("SYSTEM 1" , "SYSTEM 2" , "SYSTEM 3" , "SYSTEM 4") | eval weekday="Wday-".strftime(_time,"%A") | rename COMMENT as "Above will set the value as Wday-Mon instead of Mon.. and so forth for other days" | eval EndHour=substr(time, 50, 1) | eval EndMin=substr(time, 52, 2) | eval time = EndHour.":".EndMin | eval Time = " (" .EndHour. ":" .EndMin. "am)" | eval category="CATEGORY 1" | chart values(Time) over Systems by weekday | rename COMMENT as "After the chart command, you will see a column for each value of weekday i.e. Wday-Mon, Wday-Tue....etc)" | eval ExpectedTime = case( System="SYSTEM 1", "6:30am", System="SYSTEM 2", "6:35am", System="SYSTEM 3", "6:45am", System="SYSTEM 4", "6:40am" ) | eval CurrentSLO= case( System="SYSTEM 1", "7:15am", System="SYSTEM 2", "7:20am", System="SYSTEM 3", "7:10am", System="SYSTEM 4", "7:10am" ) | eval category="CATEGORY 1" | table category Systems ExpectedTime CurrentSLO Wday-* | rename ExpectedTime as "Expected Time" | rename CurrentSLO as "Current SLO" | rename category as "Category" | eval hasMissingValues="false" | rename COMMENT as "Now the foreach command will be run against each of week day fields that i.e. Wday-Mon, Wday-Tue..etc. It will than find out if there are missing values" | foreach Wday-* [ | eval hasMissingValues=if(isnull('<<FIELD>>'),"true",hasMissingValues)] | where hasMissingValues="true" | rename COMMEND as "Finally, renaming the Wday-Mon to just Mon..and so forth" | rename Wday-* as *

Re: How to trigger an alert week day without data?

Gzuluaga — Fri, 20 May 2022 18:30:54 GMT

Thanks a lot! @somesoni2

It works!

It was very helpful and I learned a lot.