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:
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.