Splunk Enterprise

Summary Index - How to count using eval on a specific field equals to true

beriwalnishant
Path Finder

Hi Team,

I am stuck with a query that is not working.

I have set up a summary index that collects data every 1 hour and every 15min.

I have a field 'isCostChanged' which I want to count basis 'Yes' and 'No' in Summary Index.

Using this query :

index=summary-my-sumdata splunk_server_group=default reporttype=costchangecount reporttime=fifteenmin isCostChanged=*

| stats sum(count) as Total, sum(eval(isCostChanged="true")) as CostChanged, sum(eval(isCostChanged="false")) as NoCostChanged by CountryCode
| eval CostChangeRatio=round((CostChanged/Total)*100,2)
| eval NoCostChangeRatio=round((NoCostChanged/Total)*100,2)
| fields CountryCode, NoCostChanged, CostChanged, CostChangeRatio

 

What its doing - Total count is correct but the count for isCostChanged=true and =false is not correct, the count is less

if I do this below to verify the data, the count is correct

| stats sum(count) as Total by isCostChanged

Can you help how to achieve this 

Thanks in advance 

Nishant

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Summing count as total is not the same as counting change and no change events

index=summary-my-sumdata splunk_server_group=default reporttype=costchangecount reporttime=fifteenmin isCostChanged=*

| eval changeCost=if(isCostChanged="true",count,0)
| eval noChangeCost=if(isCostChanged="false",count,0)
| stats sum(count) as Total, sum(changeCost) as CostChanged, sum(noChangeCost) as NoCostChanged by CountryCode
| eval CostChangeRatio=round((CostChanged/Total)*100,2)
| eval NoCostChangeRatio=round((NoCostChanged/Total)*100,2)
| fields CountryCode, NoCostChanged, CostChanged, CostChangeRatio

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Summing count as total is not the same as counting change and no change events

index=summary-my-sumdata splunk_server_group=default reporttype=costchangecount reporttime=fifteenmin isCostChanged=*

| eval changeCost=if(isCostChanged="true",count,0)
| eval noChangeCost=if(isCostChanged="false",count,0)
| stats sum(count) as Total, sum(changeCost) as CostChanged, sum(noChangeCost) as NoCostChanged by CountryCode
| eval CostChangeRatio=round((CostChanged/Total)*100,2)
| eval NoCostChangeRatio=round((NoCostChanged/Total)*100,2)
| fields CountryCode, NoCostChanged, CostChanged, CostChangeRatio

beriwalnishant
Path Finder

@ITWhisperer 
Though I dont doubt this query and sure is the most simple one but i just want to see the different ways people have before I click on 'Accept as solution'

Which I will do just want to see what others come up with - good to have more than one solution


0 Karma

beriwalnishant
Path Finder

Incredibly brilliant sir @ITWhisperer 

That was awesome, it's so sad that on SI the simple sum(eval) doesn't work but no issues with your work around rocks.

I checked and **bleep**, the count is bang on.

The solution will make my queries lengthy but no worries as its a simple workaround 

 

Thanks a lot again

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...