Splunk Search

Count of values per column

tawollen
Path Finder

I have a table like this that is generated by a | stats values(value1) values(value2) values(value3) values(value4) by host

host    col1    col2    col3    col4
host1   20      30      50      100     
host2   20      25      50      90      
host3   40      50      50      100     
host4   40      55      50      100

What I am trying to get is a count of each of the values that are in "col1", etc

col1    col2    col3    col4
20 - 2  30-1    50-4    100-3
40 - 2  25-1            90-1
        50-1
        55-1

It doesn't need to be exactly that format, (e.g 20-2) but I want to know how many values of 20 and 40 are in value1,etc.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

your current search giving columns host col1 col2 col3 col4
| untable host column val | stats count by column val | eval col=val."-".count
| eval temp=1 | chart values(col) over temp by column | fields - temp

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

your current search giving columns host col1 col2 col3 col4
| untable host column val | stats count by column val | eval col=val."-".count
| eval temp=1 | chart values(col) over temp by column | fields - temp
0 Karma

cmerriman
Super Champion

this is not the most elegant of answers, i admit. I'm not sure of a better way, however. It isn't very flexible if you're going to have more columns, because of the eventstats.

add something like this to the end of your search:

...|foreach col* [eval <<FIELD>>_count=mvcount(<<FIELD>>)]|eventstats sum(col1_count) as col1_count by col1|eventstats sum(col2_count) as col2_count by col2|eventstats sum(col3_count) as col3_count by col3|eventstats sum(col4_count) as col4_count by col4|foreach col* [eval <<FIELD>>='<<FIELD>>'+" - "+'<<FIELD>>_count']|stats values(col*) as col*

this is a runanywhere for how i got to the answer.

|makeresults|eval data="host=host1,col1=20,col2=30,col3=50,col4=100 host=host2,col1=20,col2=25,col3=50,col4=90 host=host3,col1=40,col2=50,col3=50,col4=100 host=host4,col1=40,col2=55,col3=50,col4=100"|makemv data|mvexpand data|rename data as _raw|kv|table host col*|foreach col* [eval <<FIELD>>_count=mvcount(<<FIELD>>)]|eventstats sum(col1_count) as col1_count by col1|eventstats sum(col2_count) as col2_count by col2|eventstats sum(col3_count) as col3_count by col3|eventstats sum(col4_count) as col4_count by col4|foreach col* [eval <<FIELD>>='<<FIELD>>'+" - "+'<<FIELD>>_count']|stats values(col*) as col*
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...