Trying to sum values of fields with similar names

[srobinsonxtl]

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

[cmerriman · Accepted solution]

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

[s2_splunk]

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.

[s2_splunk]

Try this: yoursearch | stats sum(20A*) as 20A, sum(20B*) as 20B, sum(20C*) as 20C | table 20*

[srobinsonxtl]

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

[s2_splunk]

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.