How do you run the stats count against multiple columns?

[splunker1981]

Hi all,

I'm trying to find a way to combine multiple searches into 1, but all efforts have failed. I'd like to run the equivalent of | stats count by column against multiple columns. Sample data and desired results below. Any pointers would be greatly appreciated.

 T1           T2           T3          T4
 36.650000    16.050000    1.366667    74
 44.866667    40.016667    1.366667    74
 54.966667    16.050000    1.366667    74
 36.650000    57.950000    2.483333    74
 36.650000    16.050000    2.150000    74
 36.650000    40.016667    2.150000    2.150000
 36.650000    16.050000    2.150000    74

Results (desired - layout can be in any format). As mentioned, right now I am doing |stats count by T1, another search with |stats count by T2 and so on.

T1          T2             T3           T4
36.65000  5   16.050000  4   1.366667 3   74       6
44.866667 1   40.016667  2   2.150000 3   2.150000 1
54.966667 1   57.950000  1   2.483333 1

[kmaron]

There may be a better way to do this but I think this works.

    | eventstats count as T1count by T1
    | eventstats count as T2count by T2
    | eventstats count as T3count by T3
    | eventstats count as T4count by T4
    | eval T1 = T1."!".T1count
    | eval T2 = T2."!".T2count
    | eval T3 = T3."!".T3count
    | eval T4 = T4."!".T4count
    | stats values(T1) as T1values values(T2) as T2values values(T3) as T3values values(T4) as T4values
    | rex field=T1values "(?<T1>.*?)!(?<T1_count>.*?)$"
    | rex field=T2values "(?<T2>.*?)!(?<T2_count>.*?)$"
    | rex field=T3values "(?<T3>.*?)!(?<T3_count>.*?)$"
    | rex field=T4values "(?<T4>.*?)!(?<T4_count>.*?)$"
    | table T1 T1_count T2 T2_count T3 T3_count T4 T4_count

[cmerriman]

how about something like this:

|makeresults|eval data="T1=36.650000,T2=16.050000,T3=1.366667,T4=74 T1=44.866667,T2=40.016667,T3=1.366667,T4=74 T1=54.966667,T2=16.050000,T3=1.366667,T4=74 T1=36.650000,T2=57.950000,T3=2.483333,T4=74 T1=36.650000,T2=16.050000,T3=2.150000,T4=74 T1=36.650000,T2=40.016667,T3=2.150000,T4=2.150000 T1=36.650000,T2=16.050000,T3=2.150000,T4=74"|makemv data|mvexpand data|rename data as _raw |kv|fields T*|fields - _raw _time|fieldsummary|fields field values
|makemv values delim="},{"|mvexpand values|rex field=values max_match=0 "\"value\"\:\"(?<value>[\d\.]+)\"\,\"count\"\:(?<count>\d+)"|eval value_count=value."|".count|eval{field}=value_count|fields - field values count value value_count|stats values(*) as *

fieldsummary will give you the data you desire and then i'm just doing some stuff to help break it out into the format you're looking for.

[kamlesh_vaghela]

@splunker1981

Have you tried appendcols with all searches? If not then can you please try it? Just add stats count in all searches and rename count using unique name across all the searches. like | stats count as Count_T1 .

Please check my sample search for reference.

| makeresults | eval T1="36.650000 44.866667 54.966667 36.650000 36.650000 36.650000 36.650000" | eval T1=split(T1," ") | stats count as Count_T1 by T1
| appendcols [ | makeresults | eval T2="16.050000 40.016667 16.050000 57.950000 16.050000 40.016667 16.050000" | eval T2=split(T2," ") | stats count as Count_T2 by T2 ] | appendcols [| makeresults | eval T3="1.366667 1.366667 1.366667 2.483333 2.150000 2.150000 2.150000" | eval T3=split(T3," ") | stats count as Count_T3 by T3] | appendcols [| makeresults | eval T4="74 74 74 74 74 2.150000 74" | eval T4=split(T4," ") | stats count as Count_T4 by T4] | table T1 Count_T1 T2 Count_T2 T3 Count_T3 T4 Count_T4