Splunk Search

Count by values for two fields like a table

nkotha
New Member

I have events coming with two distinct key say "Key1" and "Key2". Expected value for these keys are 1 to 3. I want a table where I can see the count of each key for every possible value like...

Value | count(Key1) | count( Key2)
1 | 10 | 3
2 | 1 | 15
3 | 4 | 6

tried "| stats count(key1), count(key2) by value" but that doesn't work

0 Karma
1 Solution

woodcock
Esteemed Legend

This was another fun one! Like this (If confused, look at the results without each of the last 2 lines):

...  | multireport
        [ stats count AS "count(Key1)" BY Key1 ]
        [ stats count AS "count(Key2)" BY Key2 ]
     | eval Value=coalesce(Key1, Key2)
     | fields - Key* | stats first(*) AS * BY Value

View solution in original post

0 Karma

woodcock
Esteemed Legend

This was another fun one! Like this (If confused, look at the results without each of the last 2 lines):

...  | multireport
        [ stats count AS "count(Key1)" BY Key1 ]
        [ stats count AS "count(Key2)" BY Key2 ]
     | eval Value=coalesce(Key1, Key2)
     | fields - Key* | stats first(*) AS * BY Value

View solution in original post

0 Karma

Richfez
SplunkTrust
SplunkTrust

What does the example you provide | stats count(key1), count(key2) by value do that's incorrect? Can you provide a sample of 5 or 10 events and the actual results/totals you'd get using those 5 or 10 events?

Thanks - that would go a long ways toward helping everyone understand the issue, which would go a long ways toward getting some possible answers.

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.