Archive

How to count unique events pr class

lakromani
Builder

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
Tags (1)
0 Karma
1 Solution

DavidHourani
Super Champion

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

View solution in original post

0 Karma

jeffland
Champion

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.

lakromani
Builder

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

0 Karma

DavidHourani
Super Champion

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

DavidHourani
Super Champion

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

View solution in original post

0 Karma

lakromani
Builder

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.

0 Karma

lakromani
Builder

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

DavidHourani
Super Champion

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

0 Karma

lakromani
Builder

Ahh, did work perfectly, thanks.
Accepted 🙂

.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!