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