Need help with splunk query with aggregation over repeated pattern.

[splunknovice]

I am trying to construct from my log which logs sla tracking metrics like below:
Message="Metric1=11887,Metric2=179544,Metric3=157892,Metric4=61,Metric5=3438"

Here's the query I am trying with.

host=testmachine* INFO source=/var/log/tomcat/test/app.log | rex max_match=100 field=Message "(?\w+)=(?\d+) us," | stats avg(latency) by metric

With the above query, the aggregation seem to be happening across all the matches, i.e. all the metric show the same value of avg(latency). Is there a way I can relate the metric and latency together?

[somesoni2]

See my updated answer below. This should do it.

[splunknovice]

Thanks for a quick response.

Say I've 3 such entries in the log:
Message="Metric1=11887,Metric2=179544,Metric3=157892,Metric4=61,Metric5=3438"
Message="Metric1=10800,Metric2=179040,Metric3=157002,Metric4=60,Metric5=3428"
Message="Metric1=10007,Metric2=179030,Metric3=157101,Metric4=62,Metric5=3418"

I am expecting the output to be average across all the 3 Message field entries for a specific metric.

[somesoni2 · Accepted solution]

You can try something like this for starter.

host=testmachine* INFO source=/var/log/tomcat/test/app.log 
| rex max_match=100 field=Message "(?P<metric>[^=]+)=(?P<latency>[^,]+)[,]" |eval metric="Metric"| stats avg(latency) by metric

This should give you one entry per host.

Updated answer

Use following:

host=testmachine* INFO source=/var/log/tomcat/test/app.log | rex max_match=100 field=Message "(?P<metricdata>[^,]+)[,]" | table metricdata | mvexpand metricdata | rex field=metricdata "(?<metric>.*)=(?<latency>.*)" | stats avg(latency) by metric

[somesoni2]

would you mind closing the question, if everything is set for you?

[somesoni2]

Glad I could help.

[splunknovice]

It was mistake on my part. The last query you gave is working like charm. Big thanks to you for helping me construct this query which I was stuck for many hours!!!

[somesoni2]

How are the values coming up in latency column? Do they seem numbers? I used the sample that you provided and I am seeing data.

[splunknovice]

Thanks again. I think this is getting closer to what I am expecting. However now the avg(latency) column is empty for all rows. If I remove the | stats avg(latency) by metric, i see data in metric and latency columns as expected. Its the aggregation that's not returning anything.

[somesoni2]

I guess you're getting data in below form
metric avg(latency) Metric1 87346.000000

Metric2 87346.000000

Metric3 87346.000000

Metric4 87346.000000

What output you're expecting?