Group data on a query which results in table data after multiple splits.

[npandit1020]

I have a query which results in to a table data.

I want to group the data and the count column should sum of grouped data.

but this just results in total of all the fields in all the row and shows up against all the values as same sum.

example log:

2021-11-15 11:17:25.899 level=INFO com.a.b.MyClass - Average latency=0.0 someRandomCount=12800 mySearchValue=SearchValue1=167,SearchValue2=154,SearchValue3=163 // AppId=3ba33f54-4588-49f8-9702-bf957392a029 

my Query for this log is:

mySearchValue="*"
| rex "mySearchValue=(?<sValue>[^\"]+) //"
| eval field1=split( sValue,",")
| rex field=field1 "(?<Field1>[^\,]+)\=(?<Field2>[^\,]*)"
| eval c=mvzip(Field1,Field2)
| table Field1,Field2
| mvexpand c
| rename Field1 as "My Values"
| rename Field2 as "Count"

Note the string against "mySearchValue" in my log is not fixed to have 3 values, it can have any number different values. But the format of each one of them would be same : someString=123 (comma seperated).

The above queries sample result comes like below:

My Values	Count
SearchValue1 SearchValue2 SearchValue3	167 154 163
SearchValue1 SearchValue2 SearchValue3	417 378 399

Each line is one row here, but the first section is extracted form first log encountered and splitted up in to rows then columns. and so on with other log lines.

I want this data to be grouped by My Values and sum respective Count values.

If I add : stats sum(Field2) AS "groupCount" by Field1

Then I do get distinct "My Values" but the count (for every row) comes out to be the same which is total of all values (in this case: 1678) .

[ITWhisperer · Accepted solution]

Try something like this

| makeresults 
| eval _raw="2021-11-15 11:17:25.899 level=INFO com.a.b.MyClass - Average latency=0.0 someRandomCount=12800 mySearchValue=SearchValue1=167,SearchValue2=154,SearchValue3=163 // AppId=3ba33f54-4588-49f8-9702-bf957392a029"


| rex "mySearchValue=(?<sValue>[^\"]+) //"
| eval field1=split( sValue,",")
| mvexpand field1
| rex field=field1 "(?<Field1>[^\,]+)\=(?<Field2>[^\,]*)"
| stats sum(Field2) as total by Field1