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

[moorte]

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