topic Re: Need help with splunk query with aggregation over repeated pattern. in Splunk Search

Need help with splunk query with aggregation over repeated pattern.

splunknovice — Fri, 06 Dec 2013 21:55:20 GMT

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?

Re: Need help with splunk query with aggregation over repeated pattern.

somesoni2 — Fri, 06 Dec 2013 22:40:53 GMT

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?

Re: Need help with splunk query with aggregation over repeated pattern.

somesoni2 — Fri, 06 Dec 2013 22:43:51 GMT

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

Re: Need help with splunk query with aggregation over repeated pattern.

splunknovice — Fri, 06 Dec 2013 23:02:06 GMT

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.

Re: Need help with splunk query with aggregation over repeated pattern.

somesoni2 — Sat, 07 Dec 2013 01:19:03 GMT

See my updated answer below. This should do it.

Re: Need help with splunk query with aggregation over repeated pattern.

splunknovice — Sat, 07 Dec 2013 01:51:41 GMT

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.

Re: Need help with splunk query with aggregation over repeated pattern.

somesoni2 — Sat, 07 Dec 2013 01:59:14 GMT

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.

Re: Need help with splunk query with aggregation over repeated pattern.

splunknovice — Sat, 07 Dec 2013 02:16:50 GMT

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!!!

Re: Need help with splunk query with aggregation over repeated pattern.

somesoni2 — Sat, 07 Dec 2013 02:20:12 GMT

Glad I could help.

Re: Need help with splunk query with aggregation over repeated pattern.

somesoni2 — Sat, 07 Dec 2013 02:37:00 GMT

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