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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...