Splunk Search

How to use if condition along with count in a where condition?

New Member

Hi All, I need to build a search that to show result as below. I have grouped the events based on the id which is unique in my logs. I need to show the result as for particular event need to show count of occurrence of a satus. Something like as below.
| where myresult= if(count(eventstatus=="good")>0) then show only count of good. If(count(eventstatus=="bad")>0) then show only count of bad. something like this. I have tried different possible solutions but nothing worked for me. suppose for a event1 both good and bad status exists then have to show only good.
Events FinalStatus
Event1 10 >>>>>>>>>>>>>> Good
Events 2 >>>>>>>>>>>>>> Bad

0 Karma

SplunkTrust
SplunkTrust

This can be written much more elegantly, but it does the job and you can understand it.

(your base search that gives the eventID and status for each event...)
| table eventID status
| eval goodcount=if(status="good",1,0)
| eval badcount=if(status="bad",1,0)
| stats sum(goodcount) as goodcount, sum(badcount) as badcount by eventID
| eval status = if(badcount>0,"bad","good")
| eval count=if(badcount>0,badcount,goodcount)

Each event gets assigned a 1 either in the goodcount or badcount fields, and a 0 in the other. Then the stats command sums them up. Finally, the status is set and the count is displayed based on whether there are any bad events for that eventID.

The first two evals and the stats command can be compressed into a single stats command with inline count(eval()) expressions, but I figure you probably need this easier-to-explain version to get you started.


Footnote - Count

Count as a function only applies to transforming/aggregate commands (stats, eventstats, chart and so on). If you use it anywhere else, it must be applying to a field by that name that came OUT of one of those commands. That's why you would get nowhere trying to use it in a where clause.

I make a habit of renaming the count field that comes out of those transforming commands, so I never make that mental mistake of confusing the two. For instance, sometimes you want to do something with the count of a prior command, inside a command that itself is creating a count. That gets confusing results when Splunk doesn't do what you intended.

Footnote - Where

Where is a command used to filter the results of a search. It gets rid of all the records that don't fit the where criteria. As such, if you plan on processing the record one of two ways, the where command isn't relevant to what you are trying to do. Look to if or case or occasionally rex or coalesce, depending on what you are trying to get done.

0 Karma

New Member

Hi, Thank you very much for your immediate response to my question. The base criteria was I will set of events from log file. I will group them based on myeventId and calculate the response time of each events, if the response time is not with in the predefined threshold value then i will show them in a graph along with the which operation has been falls under the category. Now my requirement was i have three status "GREEN","AMBER","RED". If any of the operation has at least one "RED" then I need to show only those events count.(no need to show "AMBER" and "RED"). If there are no events with "RED" and have atleast one "AMBER", then have to show only "AMBER) . If there are no "RED"&"AMBER" then only have to show "GREEN". I got stuck to filter these events. I am able to show all three events for each operation with my below query. Not able to figure it out with second requirement.
index= myindex source = "myapplog" application= "myapp" |
transaction myeventId startswith="start process" |
eval endTransTime=(strptime(max(transtime), "%H:%M:%S)) |
eval startTransTime= (strptime(min(transtime), "%H:%M:%S"))|
eval response = (endTransTime-startTransTime)|
eval resStatus =if(response <= 0.01, "GREEN",if(ResponseTime<=0.02 ,"ABMBER","RED")) |
eval busEvents=case(match(path,"get\:\/products\/\success.html"),"Products-Success",
match(path,"get\:\/products\/\remove.html"),"Products-Remove",
match(path, "post\:\/products\/\purchase.html"), "Products-Purchase",1=1,"Others")| chart count over busEvents by resStatus

Please help me on this.
From the above the graph is as figure1 alt text. My requirement is to show as figure2 alt text.

0 Karma

Revered Legend

Can you share your current full search?

0 Karma

New Member

Hi, Thank you very much for your immediate response to my question. The base criteria was I will set of events from log file. I will group them based on myeventId and calculate the response time of each events, if the response time is not with in the predefined threshold value then i will show them in a graph along with the which operation has been falls under the category. Now my requirement was i have three status "GREEN","AMBER","RED". If any of the operation has at least one "RED" then I need to show only those events count.(no need to show "AMBER" and "RED"). If there are no events with "RED" and have atleast one "AMBER", then have to show only "AMBER) . If there are no "RED"&"AMBER" then only have to show "GREEN". I got stuck to filter these events. I am able to show all three events for each operation with my below query. Not able to figure it out with second requirement.
index= myindex source = "myapplog" application= "myapp" |
transaction myeventId startswith="start process" |
eval endTransTime=(strptime(max(transtime), "%H:%M:%S)) |
eval startTransTime= (strptime(min(transtime), "%H:%M:%S"))|
eval response = (endTransTime-startTransTime)|
eval resStatus =if(response <= 0.01, "GREEN",if(ResponseTime<=0.02 ,"ABMBER","RED")) |
eval busEvents=case(match(path,"get\:\/products\/\success.html"),"Products-Success",
match(path,"get\:\/products\/\remove.html"),"Products-Remove",
match(path, "post\:\/products\/\purchase.html"), "Products-Purchase",1=1,"Others")| chart count over busEvents by resStatus

Please help me on this.

0 Karma

SplunkTrust
SplunkTrust

Can you paste in a few example events, and especially the search you currently have?

This is unclear as it is - where does "eventstatus" come from, and if you are creating it how is it made? Why is Event1 "Good" but Events is "Bad?"

And what do you mean by "supposed for an event1 both good and bad status exists then only have to show good"? If you have already grouped them, it depends a lot on how you did that for how to make only the right ones show up (and most likely this will be answered by filtering or doing some evals ahead of time before the grouping).

Also, please remember to use the "code" button "101010" on the editing toolbar when you paste code-like things.

0 Karma

New Member

Hi, Thank you very much for your immediate response to my question. The base criteria was I will set of events from log file. I will group them based on myeventId and calculate the response time of each events, if the response time is not with in the predefined threshold value then i will show them in a graph along with the which operation has been falls under the category. Now my requirement was i have three status "GREEN","AMBER","RED". If any of the operation has at least one "RED" then I need to show only those events count.(no need to show "AMBER" and "RED"). If there are no events with "RED" and have atleast one "AMBER", then have to show only "AMBER) . If there are no "RED"&"AMBER" then only have to show "GREEN". I got stuck to filter these events. I am able to show all three events for each operation with my below query. Not able to figure it out with second requirement.
index= myindex source = "myapplog" application= "myapp" |
transaction myeventId startswith="start process" |
eval endTransTime=(strptime(max(transtime), "%H:%M:%S)) |
eval startTransTime= (strptime(min(transtime), "%H:%M:%S"))|
eval response = (endTransTime-startTransTime)|
eval resStatus =if(response <= 0.01, "GREEN",if(ResponseTime<=0.02 ,"ABMBER","RED")) |
eval busEvents=case(match(path,"get\:\/products\/\success.html"),"Products-Success",
match(path,"get\:\/products\/\remove.html"),"Products-Remove",
match(path, "post\:\/products\/\purchase.html"), "Products-Purchase",1=1,"Others")| chart count over busEvents by resStatus

Please help me on this.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!