How apply a stats sum with a where condition?

[intelsubham]

Need to sum a field value with a condition. For example, every log contains a field value pair "failedcount" with integer values, I want to sum up the failedcount only when other field "servertype" is equal to "bot" or "web". So I want two columns with botfailedcount( sum of failedcount where servertype=bot) and webfailedcount ( sum of failedcount where servertype=web)

[sideview · Accepted solution]

1) Since you want to split the servertype as your two columns, you need the chart command and it's "split by" argument. By a silly quirk, the chart command demands to have some field as the "group by" field so here we just make one and then throw it away after.

( servertype=bot OR servertype=web) | eval foo=1 | chart sum(failedcount) over foo by servertype | fields - foo

2) The other way is to use stats and then use xyseries to turn the "stats style" result set into a "chart style" result set, however we still have to do the same silly trick.

( servertype=bot OR servertype=web) | stats sum(failedcount) as count by servertype | eval foo="1" | xyseries foo servertype count | fields - foo

3) OR, you could make two separate count fields with the eval command, and then just use stats.

( servertype=bot OR servertype=web) | eval webFails=if(servertype="web",failedcount,null()) | eval botFails=if(servertype="bot",failedcount,null()) | stats sum(webFails) as webFailures sum(botFails) as botFailures

[intelsubham]

Thanks Sideview, I was looking for the third one.

[sundareshr]

Try this

... | stats sum(failedcount) as botfailedcount by servertype