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
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>