All,
I have dates where the field names are:
20A1,20A2,20A3,20B1,20B2,20B3,20C1,20C2,20C3
1,3,4,5,5,5,6,6,6
I am trying the sum fields:
20A1,20A2,20A3 to get the value 8 as 20A,
20B1,20B2,20B3 to get the value of 15 as 20B
20C1,20C2,20C3 to get the value of 18 as 20C
Final Results
20A,20B,20C
8,15,18
Thanks,
Stephen Robinson
you can add this to the end:
|foreach 20*1 20*2 20*3 [eval <<MATCHSTR>>='<<FIELD>>'+'<<FIELD>>'+'<<FIELD>>'|rename A as 20A, B as 20B, C as 20C]|fields 20A 20B 20C
seems to work with the data you supplied.
|makeresults|eval data="20A1=1 20A2=3 20A3=4 20B1=5 20B2=5 20B3=5 20C1=6 20C2=6 20C3=6"|makemv data|mvexpand data|eval _raw=data|kv|rename * as 20*|stats values(*) as *|fields - 20data 20_time 20_raw 20_mkv_child 20_kv
|foreach 20*1 20*2 20*3 [eval <<MATCHSTR>>='<<FIELD>>'+'<<FIELD>>'+'<<FIELD>>'|rename A as 20A, B as 20B, C as 20C]|fields 20A 20B 20C
you can add this to the end:
|foreach 20*1 20*2 20*3 [eval <<MATCHSTR>>='<<FIELD>>'+'<<FIELD>>'+'<<FIELD>>'|rename A as 20A, B as 20B, C as 20C]|fields 20A 20B 20C
seems to work with the data you supplied.
|makeresults|eval data="20A1=1 20A2=3 20A3=4 20B1=5 20B2=5 20B3=5 20C1=6 20C2=6 20C3=6"|makemv data|mvexpand data|eval _raw=data|kv|rename * as 20*|stats values(*) as *|fields - 20data 20_time 20_raw 20_mkv_child 20_kv
|foreach 20*1 20*2 20*3 [eval <<MATCHSTR>>='<<FIELD>>'+'<<FIELD>>'+'<<FIELD>>'|rename A as 20A, B as 20B, C as 20C]|fields 20A 20B 20C
New suggestion: I have no idea if there is a more elegant way of doing it and I am not sure how it work with your dataset, but this is what does work for the simple case I tested:
yoursearch | table 20*
| transpose
| eval FieldGroup=substr(column,1,3)
| stats sum(row *) as Sum* by FieldGroup
| addtotals
You can add another | transpose
at the end to change the way the data is presented. Hopefully, this gets you closer.
Try this: yoursearch | stats sum(20A*) as 20A, sum(20B*) as 20B, sum(20C*) as 20C | table 20*
yoursearch | stats sum(20A*) as 20A, sum(20B*) as 20B, sum(20C*) as 20C | table 20* this doesn't work and gives me the following error:
Error in 'stats' command: The number of wildcards between field specifier '20A*' and rename specifier '20A'
do not match. Note: empty field specifiers implies all fields, e.g. sum() == sum(*)
However; yoursearch | stats sum(20A*) as 20A*, sum(20B*) as 20B*, sum(20C*) as 20C* | table 20* works but gives me:
20A1 20A2 20A3 20B1 20B2 20B3 20C1 20C2 20C3
Thank you for your time to help me out.
Thanks,
Stephen Robinson
Yeah, sorry, I should've tested this beforehand; I added a new suggestion below. I am not a search expert, so hopefully someone with more SPL-fu can provide a better/easier way of addressing your use case.