How to chart values that belong a group which is determined by similar field names

[jpass]

I'm wondering what the most efficient way to deal events that contain values that should be grouped based on the field names. For example, I would like to chart the counts the group names using the example event below.

Note: there is no way to tell exactly how many groups the event will contain. Some times there may be just 2 groups (Z_f1 and Z_f2) other times there may be ten or twenty (Z_f1, Z_f2, Z_f3 etc. etc.)

Show Z_f1_count by Z_f1_a1

Z_f1_a1: first_group_name
Z_f1_a2: 5
Z_f1_count: 32
Z_f2_a1: second_group_name
Z_f2_a2: 5
Z_f2_count: 49
Z_f3_a1: third_group_name
Z_f3_a2: 1
Z_f3_count: 100
Z_f4_a1: fourth_group_name
Z_f4_a2: 01
Z_f4_count: 49

[DalJeanis · Accepted solution]

|makeresults | eval _raw="Z_f1_a1: first_group_name,Z_f1_a2: 5,Z_f1_count: 32,Z_f2_a1: second_group_name,Z_f2_a2: 5,Z_f2_count: 49,Z_f3_a1: a_group_name,Z_f3_a2: 1,Z_f3_count: 100,Z_f4_a1: another_name,Z_f4_a2: 01,Z_f4_count: 49"
| append [|makeresults | eval _raw="Z_f1_a1: first_group_name,Z_f1_a2: 4,Z_f1_count: 36,Z_f2_a1: second_group_name,Z_f2_a2: 5,Z_f2_count: 53,Z_f3_a1: a_group_name,Z_f3_a2: 1,Z_f3_count: 100,Z_f4_a1: different_name,Z_f4_a2: 4,Z_f4_count: 32,Z_f5_a1: another_name,Z_f5_a2: 4,Z_f5_count: 32" | eval _time=_time+10]

| rex max_match=0 "\w+_a1\:\s(?<groupname>[^,]+),\w+_a2\:\s(?<groupnumber>[^,]+),\w+_count\:\s(?<groupcount>[^,]+),?"
| eval zipped=mvzip(groupname,groupcount,"!!!!")
| table _time zipped
| mvexpand zipped
| makemv delim="!!!!" zipped
| eval groupname=mvindex(zipped,0)
| eval count=mvindex(zipped,1)
| table _time groupname count
| chart sum(count) over _time by groupname

producing this

_time                 a_group_name  another_name  different_name  first_group_name   second_group_name   
2/28/2017  3:14:38 AM 100           49                            32                 49                  
2/28/2017  3:14:48 AM 100           32            32              36                 53     

[DalJeanis]

It puts the group names in alphabetical order. If you need them in another order, then some kind of sorting prefix will need to be added.

[jpass]

worked perfectly thank you so much.

[DalJeanis]

glad to help.

[DalJeanis]

1) is it always Z_f1, Z_f2 etc, or does that vary as well?
2) what kind of chart are you wanting to produce?
3) do you want to match charts across events by the group name?

[jpass]

Yes, Z_f always stays the same. Only the number changes. So the number is what ties the fields together.
I would like to see this look like this in a table with the group name as the column headers and the count value under neath like this:

first_group_name second_group_name third_group_name fourth_group_name
32 49 100 49