topic Re: How to count unique events pr class in Splunk Search

How to count unique events pr class

lakromani — Thu, 02 May 2019 06:53:18 GMT

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

Re: How to count unique events pr class

DavidHourani — Thu, 02 May 2019 06:58:55 GMT

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

Re: How to count unique events pr class

lakromani — Thu, 02 May 2019 07:10:25 GMT

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.

Re: How to count unique events pr class

lakromani — Thu, 02 May 2019 07:14:13 GMT

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

Re: How to count unique events pr class

DavidHourani — Thu, 02 May 2019 07:18:09 GMT

I edited my answer 🙂 try again with the new query above 🙂

Re: How to count unique events pr class

lakromani — Thu, 02 May 2019 07:26:51 GMT

Ahh, did work perfectly, thanks.
Accepted 🙂

Re: How to count unique events pr class

jeffland — Thu, 02 May 2019 07:32:43 GMT

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.

Re: How to count unique events pr class

lakromani — Thu, 02 May 2019 07:57:29 GMT

I know about the makeresult, but did not find any solution on how to add cars and colors. Can you give me a hint?

Re: How to count unique events pr class

DavidHourani — Thu, 02 May 2019 08:01:36 GMT

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"]