many results in subsearch?

[BertKraan]

Hello, newbie here...

index="prd_stats" sourcetype=appman:linux host=foo* attribute=CPUUtilization earliest=-1month@month latest=-0month@month | stats avg(value) by host

When I execute this search I get about 350.000 matching events and 40 results which I expect since I have 40 servers.
Now I want to compare this result with that from the month before so I constructed a search with a subsearch:

index="prd_stats" sourcetype=appman:linux host=foo* attribute=CPUUtilization earliest=-2month@month latest=-1month@month | eval ReportKey="Voorlaatste maand" | append [search index="prd_stats" sourcetype=appman:linux host=foo* attribute=CPUUtilization earliest=-1month@month latest=-0month@month | eval ReportKey="Laatste maand" | stats avg(value) by reportkey

However executing this search results in "Subsearch produced 50000 results, truncating to maxout 50000". I understand from documentation I shouldn't fiddle with the settings in limits.conf, so:

How can I limit the amount of results in my subsearch without losing the information?

Thanks in advance for your time.

Bert

[wpreston]

Hi there! It appears that you can get your information without needing a subsearch, but you'll need to use some stats and eval magic with relative time. Basically, you'll perform one search that has all of the data you need, then use stats to average by host and eval your report key at the same time. Try the following, be sure to substitute "value" with the field name you want to use for the average:

index="prd_stats" sourcetype=appman:linux host=foo* attribute=CPUUtilization earliest=-2month@month latest=-0month@month | fields _time value host | stats avg(eval(if(relative_time(_time,"@mon")=relative_time(now(),"-1mon@mon"),value,NULL))) AS "Laatste maand" avg(eval(if(relative_time(_time,"@mon")=relative_time(now(),"-2mon@mon"),value,NULL))) AS "Voorlaatste maand" by host

The relative_time function in the eval statement compares the month in each event to the month it's looking for (either last month or the month before), which is a really handy little feature. Each eval statment basically says "if the month I'm looking for and the month of the event are equal, add the value to the average calculation for this column." I also added a fields section to your search to speed things up. I hope this helps, or is along the lines of what you're looking for.

[BertKraan]

This answers my question totally! Thanks, now I'll try to understand the magic of stats, eval and relative time.

[melonman]

How about this? appending 2 stats resuls so you don't have to face the limitation of subsearch.

index="prd_stats" sourcetype=appman:linux host=foo* attribute=CPUUtilization earliest=-2month@month latest=-1month@month | stats avg(value) as avg by host | eval ReportKey="2 month ago" | append [search index="prd_stats" sourcetype=appman:linux host=foo* attribute=CPUUtilization earliest=-1month@month latest=@month | stats avg(value) avg by host | eval ReportKey="1 month ago"  ] | xyseries host ReportKey avg

This search will give you a table, where hosts on Y, ReportKeys on X, and avg(count) on X-Y crossed.