I'm trying to compare the average of a data field over two different time period, also including a few other comparison factors. My query looks like this (the comparison is simplified):
index=data sourcetype=source earliest=-30d@d latest=-16d@d
| stats dc(field) as count by date_mday, comp
| eventstats avg(count) as avg1, max(count) as max1 by comp
| fields comp avg1 max1
| join
[search index=data sourcetype=source ealiest=-15d@d latest=@d
| stats dc(field) as count2 by date_mday, comp
| eventstats avg(count2) as avg2, max(count2) as max2 by comp
| fields comp avg2 max2]
| where (avg2>avg1 AND max2>max1)
I get numbers, but the numbers don't look right. From what I see, the average is calculated by the days that have data in them, and the subsearch data is not correct. What am I doing wrong?
Here is my first suggestion - BTW, I don't think you should be using eventstats
index=data sourcetype=source earliest=-30d@d latest=-16d@d
| stats dc(field) as count by date_mday, comp
| stats avg(count) as avg1, max(count) as max1 by comp
| fields comp avg1 max1
| join [search index=data sourcetype=source ealiest=-15d@d latest=@d
| stats dc(field) as count2 by date_mday, comp
| stats avg(count2) as avg2, max(count2) as max2 by comp
| fields comp avg2 max2]
| where (avg2>avg1 AND max2>max1)
If that doesn't work, then you could manually calculate the average this way:
index=data sourcetype=source earliest=-30d@d latest=-16d@d
| stats dc(field) as count by date_mday, comp
| stats sum(count) as sum1, max(count) as max1 by comp
| eval avg1 = sum1 / 14
| fields comp avg1 max1
| join [search index=data sourcetype=source ealiest=-15d@d latest=@d
| stats dc(field) as count2 by date_mday, comp
| stats sum(count2) as sum2, max(count2) as max2 by comp
| eval avg2 = sum2 / 14
| fields comp avg2 max2]
| where (avg2>avg1 AND max2>max1)