using append with mstats and eval

[winknotes]

The following query is being used to model IOPs before and after moving a load from one disk array to another.  The "pre-load" snapshot is captured by the first mstats command, while the append is gathering the number of IOPs over time for the load being moved onto the array.  I'll then simply add the IOPs from both queries to get what it would look like if that load existed on that array for the period of time I'm querying.  I'm getting accurate data for both mstats commands, but my calculated field isn't showing any values.  I've done a ton of searching and trial and error but can't find a way to do this without an append or to get it to work with an append/appendcols. Any help would be appreciated. 

Array_Name and sgname are dimensions for grouping results.  

| mstats sum(HostIOs) as HostIOs WHERE index=my_index AND Array_Name=myarray span=5m by sgname
| append[mstats sum(HostIOs) as sgIOs WHERE index=my_index AND sgname=my_sg span=5m by sgname]
| eval totalIOPs=sgIOs+HostIOs
| timechart sum(HostIOs) as preload sum(totalIOPs) as postload span=5m

I suspect the append is getting added to the results AFTER everything else runs but I can't seem to make anything work.  Hopefully it's clear what I'm after.  

[richgalloway]

After the second mstats command, you'll have a bunch of metrics with HostIOs followed by a bunch of metrics with sgIOs.  None of them will contain both HostIOs and sgIOs so the totalOPSs field will not be what you expect.

The solution is to merge the two sets of metrics before doing the eval.

I don't have a lot of experience with metrics. but give this a try.

| mstats sum(HostIOs) as HostIOs WHERE index=my_index AND Array_Name=myarray span=5m by sgname
| append[mstats sum(HostIOs) as sgIOs WHERE index=my_index AND sgname=my_sg span=5m by sgname]
| mstats max(HostIOs) as HostIOs, max(sgIOs) as sgIOs by sgname
| eval totalIOPs=sgIOs+HostIOs
| timechart sum(HostIOs) as preload sum(totalIOPs) as postload span=5m

[winknotes]

Unfortunately that didn't work.  It throws an error that mstats has to be the first command.  So unless they're appended it won't let me string together several mstats commands.  I did try a second append but that didn't work either.  

| mstats sum(HostIOs) as HostIOs WHERE index=my_index AND Array_Name=myarray span=5m by sgname
| append[mstats sum(HostIOs) as sgIOs WHERE index=my_index AND sgname=my_sg span=5m by sgname]
| append [mstats max(HostIOs) as HostIOs, max(sgIOs) as sgIOs by sgname]
| eval totalIOPs=sgIOs+HostIOs
| timechart sum(HostIOs) as preload sum(totalIOPs) as postload span=5m

[richgalloway]

I'd experiment with using regular stats in place of the third mstats (without append).

[winknotes]

I would have thought this would work but no go.  

| mstats sum(HostIOs) as HostIOs WHERE index=my_index AND Array_Name=myarray span=5m by sgname
| appendcols [mstats sum(HostIOs) as sgIOs WHERE index=my_index AND sgname=my_sg span=5m]
| stats sum(HostIOs) as preload sum(eval(totalIOPs=HostIOs+sgIOs)) as postload by _time

It only returns HostIOs and sgIOs but not the calculated field totalIOPs.  I just don't get it.  

[winknotes]

Still no luck.  I've tried so many variations to no avail.  I'm just not sure if this is possible.  

[winknotes]

I think I've found something that works though I think there's probably a more elegant way to accomplish this.  

Minutes ago I read where mstats doesn't support subsearches and that append is the only way to accomplish what I'm wanting.  So I essentially have to gather both sets of data in my append query to allow me to add them together as I need.  

| mstats sum(HostIOs) as HostIOs WHERE index=my_index AND Array_Name=myarray span=5m by sgname
| append[mstats sum(HostIOs) as sgIOs WHERE index=my_index AND sgname=my_sg OR Array_Name=myarray span=5m by sgname]
| timechart sum(HostIOs) as preload sum(sgIOs) as postload span=5m