I have data events which share the properties of index, location, drink_type, drink_available
example data:
1) index=common, location="A", drink_type=lemonade, drink_available=yes
2) index=common, location="A", drink_type=cola, drink_available=no
3) index=common, location="B", drink_type=lemonade, drink_available=no
4) index=common, location="B", drink_type=cola, drink_available=yes
5) index=common, location="C", drink_type=lemonade, drink_available=no
6) index=common, location="C", drink_type=cola, drink_available=no
I'd like to generate a pie chart with whether or not each location has at least 1 drink available. For this example
location A has at least 1 drink
location B has at least 1 drink
location C doesn't have drinks available
Pie-chart for "at least 1 drink for each location" would be 2 for "yes" and 1 for "no".
I know how to create pie-charts, but I don't know how to perform this search, it seems as if I need to "group" events with the same location and perform a "sub-search" to see if there is at least 1 type of drink available.
The logic i'm trying to get across:
if( (drink_type=lemonade AND drink_available=yes) OR
(drink_type=cola AND drink_available=yes),
atleast1drink=true,atleast1drink=false)
I'm new to this and any help would be appreciated. Thanks.
Like this:
|makeresults
| eval raw="location=A, drink_type=lemonade, drink_available=yes::location=A, drink_type=cola, drink_available=no::location=B, drink_type=lemonade, drink_available=no::location=B, drink_type=cola, drink_available=yes::location=C, drink_type=lemonade, drink_available=no::location=C, drink_type=cola, drink_available=no"
| makemv delim=:: raw
| mvexpand raw
| rename raw AS _raw
| kv
| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"
| stats count(eval(drink_available="yes")) AS drink_available BY location
| eval drink_available=if((drink_available=0), "No", "Yes")
| stats count BY drink_available
Try this -
index=common
| rename COMMENT as "This creates a record for each location with count=the number of drinks available. "
| rename COMMENT as "The count as junk portion makes sure that every location gets a record. "
| stats count as junk count(eval(drink_available=yes)) as drinkcount by location
| rename COMMENT as "count up the number of locations with some available and with none available. "
| stats count(eval(drinkcount>0)) as SomeAvailable count(eval(drinkcount=0)) as NoneAvailable
@TommyRay106, your might have to shed more light on your use case conditions. Your explanation and final logic seems to be off. What do you mean by 2 for "yes" and 1 for "no", it does not conform with your example searches. Following is some clue based on your examples and what I understand so far from explanation (however, for actual query I would need more details):
Following base search checks for events with location and drink types present and drink_available as yes
index=common, location="*", drink_type="*", drink_available=yes
| stats count by location
If you want the count of all drink_types per location if atleast one of the drink type is available ("yes"), you can try the following
index=common, location="*", drink_type="*", drink_available="*"
| stats count values(drink_available) as drink_available by location
| search drink_available="yes"
| table location count
There could be several such combination of queries. Please define your specific use case so that we can assist you with exact query.