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!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...