Splunk Search

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

TommyRay106
New Member

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.

0 Karma

woodcock
Esteemed Legend

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

DalJeanis
SplunkTrust
SplunkTrust

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

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...