Table showing empty cells

[Simona11]

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"

[iamsahilshaiks]

@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.

[Simona11]

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

[kiran_panchavat]

@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 %"