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?
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
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