Comparing average

[sflunk]

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?

[lguinn2]

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)