topic Summary Index - How to count using eval on a specific field equals to true in Splunk Enterprise

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

beriwalnishant — Thu, 06 Jan 2022 11:27:21 GMT

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

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

ITWhisperer — Thu, 06 Jan 2022 12:33:33 GMT

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

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

beriwalnishant — Thu, 06 Jan 2022 12:52:01 GMT

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

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

beriwalnishant — Thu, 06 Jan 2022 12:53:56 GMT

@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