Alerting

Evaluate errors based on thresholds

prettysunshinez
Explorer

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

Labels (1)
0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

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

rmmiller
Contributor

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

0 Karma

rmmiller
Contributor

@prettysunshinez, did this solve your problem?

0 Karma

prettysunshinez
Explorer

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

0 Karma

rmmiller
Contributor

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

0 Karma

rmmiller
Contributor

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

0 Karma

prettysunshinez
Explorer

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

0 Karma

rmmiller
Contributor

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?

0 Karma

prettysunshinez
Explorer

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

0 Karma

rmmiller
Contributor

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

0 Karma

prettysunshinez
Explorer

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.

0 Karma

rmmiller
Contributor

Ah! Now it makes sense. Amending my answer.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...