Need Help Searching for an Average of Sums

[m7perkins]

I am logging memory utilization by process every 15 minutes which gives "x" number of memory data points where "x" is the number of running processes. For example, if there are 100 processes running on the server, I will end up with 100 values for memory utilization each being the amount of memory that is being used by each specific process. The sum of these values will equal the total amount of consumed memory in the server. The logging data is consumed by splunk as one CSV file delivered every 15 minutes by the Splunk Universal Forwarder.

I would like to get an average of the sum of these values over a given time frame. For example, searching over the past 8 hours would return one number - the average amount of consumed memory over that 8 hour period. I'm having a hard time figuring out how to make this work. I need to get the sum of consumed memory for each CSV file and then get an average of that sum - at least I think that is what I need to do.

[richgalloway]

Perhaps this will help get you started.

<your base search> | bucket span=15m memory | streamstats sum(memory) as MemUse | stats avg(MemUse) as AvgMemUse

[DalJeanis]

@richgalloway - need to fix typo - | bucket span=15m _time, and then | stats sum(memory) as MemUse by _time.

@m7perkins - richgalloway's answer as corrected should give you the answer you asked for -- first sum the data for each 15 minutes, then average those records over the length of time that you want. | bucket span=15m memory | streamstats sum(memory) as MemUse | stats avg(MemUse) as AvgMemUse

However, I worry about your initial assumptions. Assuming the "log" is what was running at an instant in time, then it's correct to sum the individual records in order to arrive at the total usage. However, you also have to ensure that the _time is going to be set correctly, and will not move the records outside of the 15-minute box. If you are depending on the processing time as _time, then typical real life performance will not always keep the transaction in the right 15m bucket.

In another questions a few months back I proposed a solution to this, which was to spread the transactions out by +-5 min before binning them. If all the groups posted exactly right, this would give you three of the same in each period, but if they vary by a few minutes, you will get at least one in each 15m increment, and usually 3, but sometimes as many as 5 or 6. The overall average will be very close to the same.

Assuming that all the records in the same csv get the same _time, whatever the _time might be, then it goes like this -

<your base search> 
| rename COMMENT as "group your records by _time"
| stats sum(memory) as MemUse by _time 

| rename COMMENT as "calculate when the real earliest _time will bin."
| eventstats min(_time) as mintime    
| eval mintime=900*floor(mintime/900)

| rename COMMENT as "Fan them out at -5min, 0 and +5m, then bin the resulting records into 15m groups"
| eval fan=mvrange(-300,+301,300)
| mvexpand fan
| eval _time = _time + fan
| bin _time span=15m 

| rename COMMENT as "Fan them out at -5min, 0 and +5m, then bin the resulting records into 15m groups"
| stats avg(MemUse) as MemUse, min(mintime) as mintime by _time      

| rename COMMENT as "Kill records that got fanned before the beginning or after the end"
| where (_time <= now()) AND _time >=mintime

| rename COMMENT as "Now calculate your averages"
| stats avg(MemUse) as avgMemUse