using EVAL to calculate daily average with subsearch

[etotman]

I'm trying to calculate a daily average using the eval command. The search below produces two numerical fields Total and Total2, but the eval command at the end does not produce a result. There is no dailyavg variable. What did I do wrong?

source="report.csv" earliest=-27d@h latest=-26d@h | eventstats sum(SpaceUsed_Total) as Total | append [search source="report.csv" earliest=-1d | eventstats sum(SpaceUsed_Total) as Total2] | eval dailyavg = (Total2-Total)/26 | top dailyavg


[acharlieh · Accepted solution]

The issue is you wind up with no events with both Total and Total2.

source="report.csv" earliest=-27d@h latest=-26d@h | eventstats sum(SpaceUsed_Total) as Total
| append [search source="report.csv" earliest=-1d | eventstats sum(SpaceUsed_Total) as Total2]
| eval dailyavg = (Total2-Total)/26 | top dailyavg

The first line will add a Total field to every event. You'll then add events from the second search, and put Total2 on each of those. Then when dailyavg is calculated you there are no events with both, so dailyavg is null. I'm not quite sure if I follow your logic, but you may want to try:

source="report.csv" earliest=-27d@h latest=-26d@h | stats sum(SpaceUsed_Total) as Total
| append [search source="report.csv" earliest=-1d | stats sum(SpaceUsed_Total) as Total2]
| stats first(*) as * | eval dailyavg = (Total2-Total)/26