Count of values per column

[tawollen]

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.

[somesoni2 · Accepted solution]

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

[cmerriman]

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*