Splunk Search

Joining 3 sets of results from 2 indexes to graph

peterkn
Explorer

Say in table A I have just 1 column result:

Animal:
Chicken 
Chicken
Cow
Cat
Cow
Cow
Dog

My query of "stats count by Animal" Would return me with:

Chicken 2
Cow 3
Cat 1
Dog 1

Now I have table B

Animal  Name  Age:
Cat Mick 3
Cat 
Dog Mike 1
Dog Moose 

I would like to join the results to make a multi-column bar graph (or a stacked bar graph) with the following result (Animal,Animal Count, Has_name, Has Age)

Cow 3 X Y, Cat 1 X Y, Dog 1 X Y

where X is the number of the respective animal instance that appear in table B with a Name specified. Y is with an Age specified
So the final table would be

Cow 3 0 0, Cat 1 1 1, Dog 1 2 1

and as a result, the multi-column graph would have 3 grouped columns of Cow, Cat and Dog.

Any suggestion/help is greatly appreciated.

Tags (2)
0 Karma
1 Solution

woodcock
Esteemed Legend

Like this (adjust to find your way of accessing the data, instead of faking it like I did):

|noop|stats count AS Animal|eval Animal="Chicken,Chicken,Cow,Cat,Cow,Cow,Dog"|makemv delim="," Animal | mvexpand Animal | stats count BY Animal
| appendpipe [|noop|stats count AS Animal|eval Animal="1,2,3,4"|makemv delim="," Animal | mvexpand Animal
   | eval Name=case(Animal=1, "Mick", Animal=3, "Mike", Animal=4, "Moose")
   | eval Age=case(Animal=1, "3", Animal=3, "1")
   | eval Animal=case(Animal<3, "Cat", true(), "Dog") | stats count(*) AS * BY Animal]
| stats values(*) AS * BY Animal | fields Animal count Name Age | rename Age AS withAges Name AS withNames

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this (adjust to find your way of accessing the data, instead of faking it like I did):

|noop|stats count AS Animal|eval Animal="Chicken,Chicken,Cow,Cat,Cow,Cow,Dog"|makemv delim="," Animal | mvexpand Animal | stats count BY Animal
| appendpipe [|noop|stats count AS Animal|eval Animal="1,2,3,4"|makemv delim="," Animal | mvexpand Animal
   | eval Name=case(Animal=1, "Mick", Animal=3, "Mike", Animal=4, "Moose")
   | eval Age=case(Animal=1, "3", Animal=3, "1")
   | eval Animal=case(Animal<3, "Cat", true(), "Dog") | stats count(*) AS * BY Animal]
| stats values(*) AS * BY Animal | fields Animal count Name Age | rename Age AS withAges Name AS withNames
0 Karma

chimell
Motivator

hi peterkn
can you send your xml code ?

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...