Splunk Search

Cant generate proper table with percentage and BY clause together

martinmasif
Explorer

Hi! First question and relative newbie, so bear with me! 🙂
I created below query to show the number of missing server ID's per rack. But I can't get the BY clause and percentage calculation to work in the same query.

index=servers
| eval serverId_present=if(isnotnull(serverId), "OK", "Missing server ID")
| stats count as totalServers, count(eval(serverId_present="Missing server ID")) as missingServers
| eval missingPercentage=round(100*missingServers/totalServers, 2) 
| chart sum(totalServers), sum(missingServers), sum(missingPercentage) by rack

No results found when using the BY clause at the end of the chart pipe. But when I remove the BY clause, it generates a table with only one row where the missingPercentage is correct.

Resuslts without BY clause:

sum(totalServers)   sum(missingServers) sum(missingPercentage)
854043  16326   1.91

So I created a second query to see if that worked instead, but no:

index=servers
| eval serverId_present=if(isnotnull(serverId), "OK", "Missing server ID")
| chart count AS totalServers count(eval(serverId_present="Missing server ID")) AS missingServers count(eval(round(100*missingServers/totalServers,2))) AS missingPercentage by rack

This query generates a table with rows for each rack where all the missingPercentage is just "0". Still a "0" if I remove the BY clause. The BY clause is working though and showing rows for each Instance.

Resuslts with BY clause:

Instance    totalOrders totalError  missingRate
Rack1 575555    2502    0
Rack2 278488    13824   0

Tried stats command instead of chart but no difference.

0 Karma
1 Solution

jpalacian
Path Finder
 index=servers
 | eval serverId_NOT_present=if(isnotnull(serverId), 0, 1)
 | stats count as totalServers, sum(serverId_NOT_present) as missingServers by rack
 | eval missingPercentage=round(100*missingServers/totalServers, 2) 
 | stats sum(totalServers), sum(missingServers), sum(missingPercentage) by rack

View solution in original post

jpalacian
Path Finder
 index=servers
 | eval serverId_NOT_present=if(isnotnull(serverId), 0, 1)
 | stats count as totalServers, sum(serverId_NOT_present) as missingServers by rack
 | eval missingPercentage=round(100*missingServers/totalServers, 2) 
 | stats sum(totalServers), sum(missingServers), sum(missingPercentage) by rack

to4kawa
Ultra Champion
index=servers
| eval serverId_present=if(isnotnull(serverId), "OK", "Missing server ID")
| stats count as totalServers, count(eval(serverId_present="Missing server ID")) as missingServers by rack
| eval missingPercentage=round(100*missingServers/totalServers, 2) 
| rename rack as Instance
| stats sum(totalServers) as totalOrders, sum(missingServers) as  totalError, sum(missingPercentage) as missingRate by Instance
0 Karma

martinmasif
Explorer

Didn't get that last | as you want? And it's missing the missingRate to get the percentage.

0 Karma

to4kawa
Ultra Champion

well, Should I make all your query?
Instance totalOrders totalError missingRate
where does these come from?

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 ...