Splunk Search

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

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

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 🙂

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