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