Splunk Search

Cant generate proper table with percentage and BY clause together

Engager

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

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

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

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

Engager

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

0 Karma

Ultra Champion

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

0 Karma