topic Evaluate errors based on thresholds in Alerting

Evaluate errors based on thresholds

prettysunshinez — Tue, 09 Jun 2020 22:01:54 GMT

I have a requirement where my query has to capture the error from the logs,look for its respective Description and Comments from the lookup file and then after perform statistics based on the Comments

Now what I want is,on comparing it with the lookup file threshold column,error ghf count is 250 and it exceeds the threshold,so its Comments should no longer be Medium,instead it has to be changed as High(Upgraded)..

Kindly let me know how to proceed with it.

Thanks in advance!!

Re: Evaluate errors based on thresholds

rmmiller — Mon, 30 Mar 2020 14:11:10 GMT

You don't have a Comments column in your last lookup result. Was that just an omission?

Re: Evaluate errors based on thresholds

rmmiller — Mon, 30 Mar 2020 15:01:19 GMT

Another clarification: For your example drill down when Comments="Medium" -- do you want that drill down to include Medium(Upgraded)" also? Or only the "native" Medium, regardless of Threshold violation?

Re: Evaluate errors based on thresholds

prettysunshinez — Mon, 30 Mar 2020 15:04:46 GMT

So for the requirement was just to have the count Comments wise and on clicking the Comments,to display the error wise count.

But now the requirement is to have like this at the first point itself.(For eg)

Comments count
High 60
Medium(Upgraded) 560

Where in clicking the Medium(Upgraded) should show me the list of errors which were Low and has exceeded the threshold.

Re: Evaluate errors based on thresholds

prettysunshinez — Mon, 30 Mar 2020 15:07:20 GMT

Medium should show me the errors that are categorised as medium and that have not exceeded thier assigned threshold.

Medium(Upgraded) should show me the errors that are categorised as low and that have exceeded thier assigned threshold

Re: Evaluate errors based on thresholds

rmmiller — Mon, 30 Mar 2020 16:52:37 GMT

Your base search:

index = <details of your search>
| regular expression to catch error max_match=0
| mvexpand error
| lookup lookupfile_name error outputs Description Comments Threshold
| stats count BY Description,Threshold,Comments
| eval Comments = case(count > Threshold AND lower(Comments)=="high","Critical(Upgraded)",count > Threshold AND lower(Comments)=="medium","High(Upgraded)",count > Threshold AND lower(Comments)=="Low","Medium(Upgraded)",1==1,Comments)
| table Comments,count

You need to include the threshold value as output from your lookup command then compare the counts against the threshold. Unfortunately, you have to build your own translation from one value of Comments to the next (upgraded), but we can do that with the eval / case command.

Note: I took the liberty of adding some insurance against your Comments fields maybe not always having the correct Capitalization.

Step 1: Change your lookup command to include the threshold value:

| lookup lookupfile_name error outputs Description Comments Threshold

Note I've included "Threshold" as an output field.

Step 2: Use stats to compute the count per Name, Comments and Threshold

| stats count BY Description,Comments,Threshold

This will tally all the errors up, split by Name, Comments, and Threshold.

Step 3: Compare each count against the Threshold value:

| eval Comments = case(count > Threshold AND lower(Comments)=="high","Critical(Upgraded)",count > Threshold AND lower(Comments)=="medium","High(Upgraded)",count > Threshold AND lower(Comments)=="low","Medium(Upgraded)",1==1,Comments)

This command upgrades the Comments value if the count is greater than the Threshold value. If count is equal to or less than Threshold, there is no change in the value of Comments.

Step 4: Display the interested fields in a table showing the Comments and count:

| table Comments,count

This just displays the Comments and count fields in a table, from which you can click to drill down to your next search. Doing another stats count here will yield incorrect results. You have already run stats, so just show the interested fields.

Your drill down search:

You are pretty close with your attempt; you just need to add in the logic that addresses the threshold violations.

index = <details of your search>
| regular expression to catch error max_match=0
| mvexpand error
| lookup lookupfile_name error outputs Description Comments Threshold
| stats count BY Description,Threshold,Comments
| eval Comments = case(count > Threshold AND lower(Comments)=="high","Critical(Upgraded)",count > Threshold AND lower(Comments)=="medium","High(Upgraded)",count > Threshold AND lower(Comments)=="Low","Medium(Upgraded)",1==1,Comments)
| search Comments=$selectedcomment|s$
| table Description,count

This assumes your drill down token is named selectedcomment. Note there's a pipe and an extra s here -- it tells Splunk to put double quotes around the value of this variable when running the search as a drill down.

Mocked-up Data

Some run anywhere SPL to help make sense of this using randomly-generated data is below. You can ignore this unless you want to learn more about how this works. Sometimes you might get no results, but it's only because of the randomness. Remove the search clause to validate the logic, if interested.

| makeresults count=50
| eval raw=split("SplunkForwarder,SMTP_Server,WWW_Publishing,Server,Workstation",",")
| eval comm=split("Low,Medium,High,Low",",")
| eval thold=split("15,10,5,10",",")
| eval Random = random()%4
| eval Description=mvindex(raw,Random)
| eval Comments=mvindex(comm,Random)
| eval Threshold=mvindex(thold,Random)
| fields - raw,comm,Random,thold
| stats count BY Description,Threshold,Comments
| eval Comments = case(count > Threshold AND lower(Comments)=="high","Critical(Upgraded)",count > Threshold AND lower(Comments)=="medium","High(Upgraded)",count > Threshold AND lower(Comments)=="low","Medium(Upgraded)",1==1,Comments)
| search Comments="High(Upgraded)"
| table Description,count

Hope that helps!

rmmiller

Re: Evaluate errors based on thresholds

manjunathmeti — Mon, 30 Mar 2020 17:17:50 GMT

Change your first query to:

Index =  | regular expression to catch error max_match=0 | mvexpand error | lookup lookupfile_name error outputs Description, Comments, Threshold  | stats count by Description, Comments, Threshold | eval Comments=case(Comments == "High" AND count > Threshold,  "Critical(Upgraded)", Comments == "Medium" AND count > Threshold, "High(Upgraded)", Comments == "Low" AND count > Threshold, "Medium(Upgraded)", 1==1, component) | stats count by Comments

Change your last query to:

Index =  | regular expression to catch error max_match=0 | mvexpand error | lookup lookupfile_name error outputs Description, Comments, Threshold  | stats count by Description, Comments, Threshold | eval Comments=case(Comments == "High" AND count > Threshold,  "Critical(Upgraded)", Comments == "Medium" AND count > Threshold, "High(Upgraded)", Comments == "Low" AND count > Threshold, "Medium(Upgraded)", 1==1, component) | search Comments="$Comments$" | stats count by Description

Re: Evaluate errors based on thresholds

rmmiller — Mon, 30 Mar 2020 20:16:14 GMT

Ah! Now it makes sense. Amending my answer.

Re: Evaluate errors based on thresholds

rmmiller — Wed, 01 Apr 2020 21:13:36 GMT

@prettysunshinez, did this solve your problem?

Re: Evaluate errors based on thresholds

prettysunshinez — Thu, 02 Apr 2020 10:07:34 GMT

I have another major requirement to be added on to this.
In the lookup file,the fifth column is a time duration,and so my query has to check if the count exceeds the threshold or not fr tht time duration.

Error1 abc medium 250 30(in mins)

Something like the above

Re: Evaluate errors based on thresholds

rmmiller — Thu, 02 Apr 2020 14:35:55 GMT

You mean Error1 is a medium as long as there are not more than 250 within a 30 minute time period?

Re: Evaluate errors based on thresholds

rmmiller — Thu, 02 Apr 2020 14:39:39 GMT

And if that's the case, what is the output of your first and drill down queries supposed to look like?

Re: Evaluate errors based on thresholds

prettysunshinez — Thu, 02 Apr 2020 15:46:08 GMT

1.Yes..

2.if they have not exceeded the threshold they will be in medium and if exceeded high(upgraded)

And rest are all same