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!!
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
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
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
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
@prettysunshinez, did this solve your problem?
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
You mean Error1 is a medium as long as there are not more than 250 within a 30 minute time period?
And if that's the case, what is the output of your first and drill down queries supposed to look like?
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
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?
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
You don't have a Comments column in your last lookup result. Was that just an omission?
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.
Ah! Now it makes sense. Amending my answer.