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

[bhavani_p]

Hi All,
I need help with Splunk to find the count of the events.
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 search. 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

[woodcock · Accepted solution]

First, ditch transaction; try this:

index= myindex source = "myapplog" application= "myapp"
| stats values(*) AS * range(transtime) AS response BY myeventId
| 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",
                      true(),                                          "Others")
| eventstats count(eval(resStatus="GREEN")) AS GREEN count(eval(resStatus="AMBER")) AS AMBER count(eval(resStatus="RED")) AS RED
| eval KEEPME=case(((RED>0)   AND     (resStatus="RED")),   "YES",
                   ((RED>0)   AND NOT (resStatus="RED")),    "NO",
                   ((AMBER>0) AND     (resStatus="AMBER")), "YES",
                   ((AMBER>0) AND NOT (resStatus="AMBER")),  "NO",
                   true(),                                  "YES")
| search KEEPME="YES"
| chart count over busEvents by resStatus

[bhavani_p]

Hi Woodcock. Its working as expected :). Thanks much for your help.