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!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

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