topic Re: Table showing empty cells in Feedback

Table showing empty cells

Simona11 — Tue, 04 Mar 2025 15:43:15 GMT

Hello everyone!

I am currently navigating and learning how to calculate and use specific commands. I am currently trying to add new columns to a table where I want to add the average of ghost% and missing% based on a location. However, it shows empty cells. Also, I want to show the values eventually in a chart in percentages and not decimals. Can anyone guide me in what is wrong with this query? Thank you! chart count by AREAID event | | eval Ghost=max(0,Ghost-Missing) |

eval "Ghost %"=Ghost/TotalTubs*100,"Missing %"=Missing/TotalTubs*100 |

fields AREAID "Missing" "Ghost" "Missing %" "Ghost %" |

stats avg(Missing) as avg_missing, avg(Ghost) as avg_ghost, by AREAIDsum(TotalTubs) as total_tubs by AREAID |

eval "Average Missing %"=avg_missing/total_tubs*100, "Average Ghost %"=avg_ghost/total_tubs*100 |

table AREAID avg_missing avg_ghost total_tubs "Average Missing %" "Average Ghost %" |

fields "AREAID", "Average Ghost %", "Average Missing %", "Ghost %", "Missing %", "total_tubs"

Re: Table showing empty cells

kiran_panchavat — Tue, 04 Mar 2025 10:51:00 GMT

@Simona11

This query generates dummy data for five locations (AREAID), assigns random values for Ghost, Missing, and TotalTubs, then calculates their percentages and averages. Finally, it summarizes the average missing and ghost percentages per location and displays them in a table.

| makeresults count=5 
| streamstats count 
| eval AREAID=case(count=1, "A1", count=2, "A2", count=3, "A3", count=4, "A4", count=5, "A5") 
| eval Ghost=random()%100, Missing=random()%80, TotalTubs=200+random()%300 
| eval Ghost=max(0, Ghost-Missing) 
| eval "Ghost %"=round(Ghost/TotalTubs*100,2), "Missing %"=round(Missing/TotalTubs*100,2) 
| stats avg(Missing) as avg_missing, avg(Ghost) as avg_ghost, sum(TotalTubs) as total_tubs by AREAID 
| eval "Average Missing %"=round(avg_missing/total_tubs*100,2), "Average Ghost %"=round(avg_ghost/total_tubs*100,2) 
| table AREAID avg_missing avg_ghost total_tubs "Average Missing %" "Average Ghost %"

Re: Table showing empty cells

Simona11 — Tue, 04 Mar 2025 10:53:33 GMT

I will have a look over my break. Thank you!

Re: Table showing empty cells

iamsahilshaiks — Thu, 03 Apr 2025 20:26:49 GMT

@Simona11

Your Splunk query has several issues that are likely causing the empty cells and incorrect results.

| chart count by AREAID event
| eval Ghost = max(0, Ghost - Missing)
| eval "Ghost %" = Ghost / TotalTubs * 100, "Missing %" = Missing / TotalTubs * 100
| fields AREAID Missing Ghost "Missing %" "Ghost %"
| stats avg(Missing) as avg_missing, avg(Ghost) as avg_ghost, sum(TotalTubs) as total_tubs by AREAID
| eval "Average Missing %" = avg_missing / total_tubs * 100, "Average Ghost %" = avg_ghost / total_tubs * 100
| table AREAID avg_missing avg_ghost total_tubs "Average Missing %" "Average Ghost %"

If you want to display this in a percentage-based chart, use:

| chart avg("Average Missing %") as "Average Missing %", avg("Average Ghost %") as "Average Ghost %" by AREAID

This will generate a chart where AREAID is on the x-axis, and the average percentages are on the y-axis.

Re: Table showing empty cells

moorte — Thu, 19 Jun 2025 17:03:09 GMT

Any time that you use the stats command, anything that is not specifically called out in the by statement (for example | stats count by fieldx, fieldy, fieldz will no longer be part of your dataset. In the example you will only have fieldx, fieldy, and fieldz) if you want to keep your fields to be used after the stats command use the values command.

| stats values(fielda) as fielda, values(fieldb) as fieldb count by fieldx, fieldy, fieldz
Or
| stats values(*) as * count by fieldx, fieldy, fieldz
though values(*) as * is a lot more performance intensive than calling out the fields with values