Splunk Search

Create a piechart where each field has a group of options for fulfillment

New Member

I have data events which share the properties of index, location, drinktype, drinkavailable
example data:
1) index=common, location="A", drinktype=lemonade, drinkavailable=yes
2) index=common, location="A", drinktype=cola, drinkavailable=no

3) index=common, location="B", drinktype=lemonade, drinkavailable=no
4) index=common, location="B", drinktype=cola, drinkavailable=yes

5) index=common, location="C", drinktype=lemonade, drinkavailable=no
6) index=common, location="C", drinktype=cola, drinkavailable=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( (drinktype=lemonade AND drinkavailable=yes) OR
(drinktype=cola AND drinkavailable=yes),

I'm new to this and any help would be appreciated. Thanks.

0 Karma

Re: Create a piechart where each field has a group of options for fulfillment


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

| eval message="Happy Splunking!!!"

0 Karma

Re: Create a piechart where each field has a group of options for fulfillment


Try this -

| 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 
0 Karma

Re: Create a piechart where each field has a group of options for fulfillment

Esteemed Legend

Like this:

| 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
0 Karma