Splunk Search
Highlighted

## Count of values per column

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.

Tags (4)
1 Solution
Highlighted

## Re: Count of values per column

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.

``````...|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*
``````
Highlighted

## Re: Count of values per column 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
``````
Speak Up for Splunk Careers!