Splunk Search

How apply a stats sum with a where condition?

intelsubham
Explorer

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)

0 Karma
1 Solution

sideview
SplunkTrust
SplunkTrust

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

View solution in original post

sideview
SplunkTrust
SplunkTrust

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
Explorer

Thanks Sideview, I was looking for the third one.

0 Karma

sundareshr
Legend

Try this

... | stats sum(failedcount) as botfailedcount by servertype
0 Karma
Get Updates on the Splunk Community!

New Cloud Intrusion Detection System Add-on for Splunk

In July 2022 Splunk released the Cloud IDS add-on which expanded Splunk capabilities in security and data ...

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Check out This Month’s Brand new Splunk Lantern Articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...