topic Re: stats conditional count in Splunk Search

stats conditional count

landen99 — Tue, 15 Apr 2014 15:05:46 GMT

I want to count the number of times that the following event is true, bool = ((field1 <> field2) AND (field3 < 8)), for each event by field4. The two methods in consideration are: 1) eval if and stats sum, and 2) stats if count.

How can I make these methods work, if possible? I want to understand the functions in this context. Also, is there a better way?

Here is my eval approach, so far:

| eval bool = ((field1 <> field2) AND (field3 < 8)) | eval field_bool = if (bool, 1, 0) | stats sum(field_bool) by field4

Here is my stats approach, so far:

| eval bool = ((field1 <> field2) AND (field3 < 8)) | stats if(bool, count) by field4

Re: stats conditional count

martin_mueller — Tue, 15 Apr 2014 15:38:28 GMT

You can do one of two things:

base search | eval bool = if((field1 != field2) AND (field3 < 8), 1, 0) | stats sum(bool) as count

base search | stats count(eval((field1 != field2) AND (field3 < 8))) as count

Re: stats conditional count

landen99 — Tue, 15 Apr 2014 15:46:54 GMT

.. adding by field4, of course. These have the exact same effect? Is either method better or faster? Is there a better way than those two?

Re: stats conditional count

martin_mueller — Tue, 15 Apr 2014 15:49:48 GMT

Speed should be very similar. I prefer the first because it separates computing the condition from building the report. If you have multiple such conditions the stats in way 2 would become insanely long and impossible to maintain.

I don't see a better way, because this is as short as it gets. Compute condition, sum up cases where it matched. No step to leave out in there to still achieve the goal.

Re: stats conditional count

landen99 — Tue, 15 Apr 2014 16:39:43 GMT

Would it work just as well or better to remove the "if" function for the boolean evaluation for the first method like this?:

base search | eval bool = (field1 != field2) AND (field3 < 8) | stats sum(bool) as count

Added: It is giving me the error: "Error in 'eval' command: Fields cannot be assigned a boolean result. Instead, try if([bool expr], [expr], [expr])." So, no, the boolean expression is not treated as 1 for true and 0 for false.

Re: stats conditional count

martin_mueller — Tue, 15 Apr 2014 17:08:29 GMT

Yeah, you cannot have a field that contains a boolean value.

Re: stats conditional count

moisesroth — Tue, 22 Nov 2016 15:09:19 GMT

The following search filter all http status 2xx, 4xx and 5xx and create a field to with the percentage of http status 200 comparing with errors 400 and 500. If status 200 is lower than 94%, an "Warning" is applied.

base search | rename message.status as msg_status, message.fwdHost as hhost | search msg_status=2* OR msg_status=4* OR msg_status=5* | rangemap field=msg_status "200 Sucesso"=200-299 default="400-599 Erros" | eval ok=if((range = "200 Sucesso"), 1, 0) | eval nok=if((range = "400-599 Erros"), 1, 0) | stats sum(ok) as ok sum(nok) as nok by hhost | addtotals | eval p_ok=ok/Total*100 | rangemap field=p_ok "Normal"=94-100 default="Warning"

The result was like this:
hhost;ok;nok;p_ok;range;Total
cgws.domain.com;2055;102;95.271210;Normal;2157
dn.domain.com;6;1;85.714286;Warning;7
ecommerce.domain.com;106115;646;99.394910;Normal;106761