I need help with stats in Splunk
Let's say you have these example data:
| stats count | eval car="Opel" | eval color="green"
| append [| stats count | eval car="Ford" | eval color="blue"]
| append [| stats count | eval car="Opel" | eval color="yellow"]
| append [| stats count | eval car="BMW" | eval color="black"]
| append [| stats count | eval car="Opel" | eval color="yellow"]
| append [| stats count | eval car="Ford" | eval color="green"]
| append [| stats count | eval car="Opel" | eval color="blue"]
| fields - count
Running it gives this table:
*car* *color*
Opel green
Ford blue
Opel yellow
BMW black
Opel yellow
Ford green
Opel blue
I need to create a table that counts number of car in each color pr each car type.
This is close, but not correct (it counts total cars and list the different colors)
| stats count | eval car="Opel" | eval color="green"
| append [| stats count | eval car="Ford" | eval color="blue"]
| append [| stats count | eval car="Opel" | eval color="yellow"]
| append [| stats count | eval car="BMW" | eval color="black"]
| append [| stats count | eval car="Opel" | eval color="yellow"]
| append [| stats count | eval car="Ford" | eval color="green"]
| append [| stats count | eval car="Opel" | eval color="blue"]
| fields - count
| stats count values(color) by car
car count values(color)
BMW 1 black
Ford 2 blue
green
Opel 4 blue
green
yellow
Here is how I would like the output to be: (count number of car pr color pr each car type
car count values(color)
BMW 1 black
Ford 1 blue
1 green
Opel 1 blue
1 green
2 yellow
Hi @lakromani,
What you're looking for is a ...| stats count by car, color
with some magic.
This should do the trick, let me know if it works out for you :
| stats count | eval car="Opel" | eval color="green"
| append [| stats count | eval car="Ford" | eval color="blue"]
| append [| stats count | eval car="Opel" | eval color="yellow"]
| append [| stats count | eval car="BMW" | eval color="black"]
| append [| stats count | eval car="Opel" | eval color="yellow"]
| append [| stats count | eval car="Ford" | eval color="green"]
| append [| stats count | eval car="Opel" | eval color="blue"]
| stats count by car, color
| sort -count
|stats list(color) as color list(count) as count by car
Cheers,
David
Just a side note, there is | makeresults
for when you want to create an event without any data. It's better than stats
because it can run directly on the search head. It's also got a count
attribute for when you need more than one event, check the docs here.
I know about the makeresult
, but did not find any solution on how to add cars and colors. Can you give me a hint?
it's actually pretty easy, similar to what you did but with makeresults
instead of |stats count
:
|makeresults | eval car="Opel" | eval color="green"
| append [| makeresults | eval car="Ford" | eval color="blue"]
| append [| makeresults | eval car="Opel" | eval color="yellow"]
| append [| makeresults | eval car="BMW" | eval color="black"]
| append [| makeresults | eval car="Opel" | eval color="yellow"]
| append [| makeresults | eval car="Ford" | eval color="green"]
| append [|makeresults | eval car="Opel" | eval color="blue"]
Hi @lakromani,
What you're looking for is a ...| stats count by car, color
with some magic.
This should do the trick, let me know if it works out for you :
| stats count | eval car="Opel" | eval color="green"
| append [| stats count | eval car="Ford" | eval color="blue"]
| append [| stats count | eval car="Opel" | eval color="yellow"]
| append [| stats count | eval car="BMW" | eval color="black"]
| append [| stats count | eval car="Opel" | eval color="yellow"]
| append [| stats count | eval car="Ford" | eval color="green"]
| append [| stats count | eval car="Opel" | eval color="blue"]
| stats count by car, color
| sort -count
|stats list(color) as color list(count) as count by car
Cheers,
David
Thanks for you reply. First one is close, but I would like to group it together.
Last one only counts number of colors, ignoring the number of cars in each color pr car group.
Your first result
car color count
BMW black 1
Ford blue 1
Ford green 1
Opel blue 1
Opel green 1
Opel yellow 2
If you look at my request, I would like one multivalue Opel with list of colors and the number of cars in each color.
From your answer I did make this workaround. Not the best.
Can not this be done in stats directly?
| stats count by car, color
| eval info=color.":".count
| stats values(info) by car
car values(info)
BMW black:1
Ford blue:1
green:1
Opel blue:1
green:1
yellow:2
I edited my answer 🙂 try again with the new query above 🙂
Ahh, did work perfectly, thanks.
Accepted 🙂