Charting results by a _time bucket and a calculated percentage of a count of events flagged in the bucket (in separate series on one chart)

luminarycrush — Fri, 22 Sep 2017 02:29:43 GMT

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!

Re: Charting results by a _time bucket and a calculated percentage of a count of events flagged in the bucket (in separate series on one chart)

cmerriman — Fri, 22 Sep 2017 11:43:00 GMT

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)

topic Re: Charting results by a _time bucket and a calculated percentage of a count of events flagged in the bucket (in separate series on one chart) in Dashboards & Visualizations

Charting results by a _time bucket and a calculated percentage of a count of events flagged in the bucket (in separate series on one chart)

Re: Charting results by a _time bucket and a calculated percentage of a count of events flagged in the bucket (in separate series on one chart)