Splunk Search

How do you run the stats count against multiple columns?

splunker1981
Path Finder

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
Tags (2)
0 Karma

kmaron
Motivator

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
0 Karma

cmerriman
Super Champion

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.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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
0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!