I have stats results from a search which form what amounts to a transaction per row on the order of several thousands of rows per hour. The transaction has relevant for this chart the following: _time, a flag "RED" or "GREEN", and a location code which is one of several codes. I want to bin _time by the hour and display a percentage as GREEN/(RED+GREEN) on a line graph with a line for each location in the series. This basically calculates the performance per location based on a percentage on the hour over time. I've tried several things and it's just not working, so maybe someone can shortcut me here.
simplified example for a time bucket 12345:
_time location_code flag
12345 A GREEN
12345 A GREEN
12345 A RED
12345 B RED
12345 C GREEN
12345 C GREEN
Thanks!
how about this:
|bucket _time span=1h|stats count(eval(flag="GREEN")) as GREEN count(eval(flag="RED")) as RED by _time location_code|eval percentage=round(GREEN/(GREEN+RED)*100,2)|fields - GREEN RED|eval {location_code}=percentage|fields - location_code percentage|stats values(*) as * by _time
i worked off of this and it seemed to work:
|makeresults|eval data="time=1506011682,location_code=A,flag=GREEN time=1506011682,location_code=A,flag=GREEN time=1506011682,location_code=A,flag=RED time=1506011682,location_code=B,flag=RED time=1506011682,location_code=C,flag=GREEN time=1506011682,location_code=C,flag=GREEN time=1506000882,location_code=A,flag=RED time=1506000882,location_code=A,flag=GREEN time=1506000882,location_code=A,flag=GREEN time=1506000882,location_code=B,flag=RED time=1506000882,location_code=C,flag=GREEN time=1506000882,location_code=C,flag=RED"|makemv data|mvexpand data|eval _raw=data|kv|eval _time=time|bucket _time span=1h|table _time location_code flag|stats count(eval(flag="GREEN")) as GREEN count(eval(flag="RED")) as RED by _time location_code|eval percentage=round(GREEN/(GREEN+RED)*100,2)