Can appendcols be used for grouping?

[tccooper]

I have the following query

index="XXXXXXXXXX" Device="*FPB*" OR Device="*VAV*" Point_Name="ActFlow" 
|bin span=15m _time
|stats last(Value) as AirFlow by Device, _time
|appendcols [|search index="XXXXXXXXXX" Device="*FPB*" OR Device="*VAV*"  Point_Name="ActFlowSP" |bin span=15m _time |stats last(Value) as Setpoint by Device, _time]
|eval difference = (1 - (Setpoint/AirFlow))
|where difference > .2
|fields _time, Device, difference

The issue is the results of the subquery are not grouping based off the Device. Suggestions on how to fix?

[woodcock]

You approach is sound so you have a bug somewhere. This analogous run-anywhere example shows that it works:

index=_* sourcetype=*splunk* OR sourcetype=*web* 
| rename sourcetype AS Device 
| eval Point_Name="ActFlow" 
| eval Value=random() 
| bin span=15m _time 
| stats last(Value) as AirFlow by Device, _time 
| appendcols 
    [| search index=_* sourcetype=*splunk* OR sourcetype=*web* 
    | rename sourcetype AS Device 
    | eval Point_Name="ActFlowSP" 
    | eval Value=random() 
    | bin span=15m _time 
    | stats last(Value) as Setpoint by Device, _time] 
| eval difference = (1 - (Setpoint/AirFlow)) 
| where difference > .2 
| fields _time, Device, difference

The problem is probably that you have "holes" in your 15-minute buckets. Let's use timechart, which has the handy benefit of building empty buckets, to our advantage. Does this work for you (You must still beware divide-by-zero)?

index="XXXXXXXXXX" Device="*FPB*" OR Device="*VAV*" Point_Name="ActFlow"
| timechart span=15m last(Value) AS AirFlow BY Device
| untable _time Device AirFlow
| appendcols 
    [| search index="XXXXXXXXXX" Device="*FPB*" OR Device="*VAV*" Point_Name="ActFlowSP"
    | timechart span=15m last(Value) AS Setpoint BY Device
    | untable _time Device Setpoint] 
| eval difference = (1 - (Setpoint/AirFlow)) 
| where difference > .2 
| fields _time, Device, difference

[somesoni2]

The appendcols doesn't do grouping/join, it just places columns/rows from two queries side by side (this can shed some light). I would suggest trying like this (avoiding append-subsearch altogether).

index="XXXXXXXXXX" Device="*FPB*" OR Device="*VAV*" Point_Name="ActFlow" OR Point_Name="ActFlowSP"
| eval AirFlow=if(Point_Name="ActFlow",Value,null())
| eval Setpoint=if(Point_Name="ActFlowSP",Value,null()
|bin span=15m _time
 |stats last(AirFlow) as AirFlow last(Setpoint) as Setpoint by Device, _time
|eval difference = (1 - (Setpoint/AirFlow))
 |where difference > .2
|fields _time, Device, difference