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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...