Niagara Sample BQL Queries

Niagara Sample BQL Queries

Alarm Queries
local:|fox:|station:|slot:/|bql:select name as 'Point Name',out as 'Point Status' from control:ControlPoint where status.alarm = 'true'
local:|fox:|station:|slot:/|bql:select * from alarm:AlarmSourceExt where alarmClass = 'defaultAlarmClass' and status.alarm = true
alarm:|bql:select timestamp,alarmData.sourceName,sourceState,ackState,ackRequired,alarmData.msgText,alarmClass where alarmData.sourceName like 'B1J*' and alarmClass like '*1*' order by timestamp desc
alarm:|bql:select timestamp,alarmData.sourceName,sourceState,ackState,ackRequired,alarmData.msgText,alarmClass where alarmData.sourceName like 'B1J*' and alarmClass like '*2*' order by timestamp desc
alarm:|bql:select *

History Queries
local:|fox:|history:/MyStation/ZoneTemp|bql:select timestamp, value where timestamp in bqltime.today
history:|bql:select *

Component Queries
local:|fox:|station:|slot:/VAV_Bldg_1|bql:select slotPath as 'Path', displayName as 'Display', out.value as 'Output Value' from control:NumericPoint where displayName like '*ZN_T*'
local:|fox:|station:|slot:/VAV_Bldg_1|bql:select slotPath as 'Path', displayName as 'Display', out.value as 'Output Value' from control:NumericPoint where displayName like '*ZN_T*' or displayName like '*STPT*'
local:|fox:|station:|slot:/Exercises|bql:select * where slotPath like '*TrafficLight1'

Http Queries - Must be using Basic Hx Profile
http://192.168.7.194/ord?station:|alarm:/|bql:select%20*
http://192.168.7.194/ord?station:|alarm:/|bql:select%20timestamp,%20source%20where%20source%20like%20%27*Temperature*%27
http://192.168.7.194/ord?station:|history:/SqlDemo|bql:select%20*
http://192.168.7.194/ord?station:|history:/SqlDemo/ZoneTemperature|bql:select%20*
http://192.168.7.194/ord?station:|history:/SqlDemo/ZoneTemperature|bql:select%20status,%20value%20where%20timestamp%20in%20bqltime.lastweek     (for this you can also use bqltime.lastmonth, bqltime.last7days, etc)
http://192.168.7.194/ord?station:|history:/SqlDemo/ZoneTemperature?period=timeRange;start=2016-04-05T00:00:00.000-00:00;end=2016-04-17T00:00:00.000-00:00|bql:select%20status,%20value,%20timestamp
http://192.168.7.194/ord?station:|history:/SqlDemo/AuditHistory|bql:select%20*
http://192.168.7.194/ord?station:|slot:/|bql:select%20name,out.value%20as%20%27Absolute%20Ord%27%20from%20control:NumericPoint

Very useful. Thanks for sharing. If you ever want to try, I've included BQL support to pyhaystack, a Python module that can connect to Niagara AX and Niagara4 and using nhaystack (see www.project-haystack.org) you can grad data from the Jace or server.

You, my friend, are a gentleman and a scholar. Thanks for sharing this!

Like!!!

This query is also useful for embedding specific results in a PX page -

COUNT ITEMS IN A DRIVER (ETC) -
station:|slot:/Home/Power|bql:select count(out) as 'Count' from control:ControlPoint
Then to put in an ORD for a graphic (cell:0,0 = Column A / Row 1)
station:|slot:/Home/Power|bql:select count(out) as 'Count' from control:ControlPoint|cell:0,0

@op - any ideas if it is possible to query the alarm database?
For example I want to find ANY alarm that contains the word Boiler?
You can trawl the history database - so it must be possible to get to the alarmdb?
Nevermind - actual read down a few lines in the alarm section!

Thanks !

One thing I'm just puzzling over now… I've got a station with some Modbus devices (modbusTcp:ModbusTcpDevice) and some BACnet (bacnet:bacnetDevice).

All are sub-classes of BDevice (aka device:Device).

• station:|slot:/|bql:select * from modbusTcp:ModbusTcpDevice gives me all the Modbus devices and not the BACnet ones.
• station:|slot:/|bql:select * from bacnet:BacnetDevice gives me all the BACnet devices and not the Modbus ones.
• station:|slot:/|bql:select * from device:Device gives me … drumroll … nuffin!

Is there a way to search for objects that are sub-classes of some given class, or is that simply not supported in BQL?

I'm trying to select components that owns at least a slot of a given type. Does anybody know hoy can I do that?

@sjlongland, I'm a little late on this but

station:|slot:/|bql:select * from driver:Device

Is there a way to indicate whether a number in the result of a BQL expression exceeds some value?

I'd like to know if any Zone temperature of approx. 350 VAV's exceeds 72.

My BQL is station:|slot:/|bql:select * from control:ControlPoint where parent.displayName like '-1A-' and displayName like '*ZN-T' and out.value > '72'.

I just can't figure out how to use the result to give me a true or false.

Also, is there a way to display the maximum number of the same query? TIA

Example for BQL queries w/ a colon in the slot name

// 1. '$3a' is the colon encoded for the slot
// 2. '$24' is the '$' of $3a' encoded again for the BQL query
public static final String EncodedColon = "$243a";

Example for existence of slot n:history
station:|slot:/Drivers|bql:select * from control:NumericPoint where slotExists('n$243ahistory')

@sammaniamsam I am trying to use the program service to find all points that have a slot named "emergencyActive", your query works if I just do a bql query using ctrl+L but I cannot get it to work in the program service. I cannot figure out how to get the ( ) around the slot name using the program service.

Has anybody in here figured out a way to limit the number of results returned or even better limit and set an offset for paginating the results? I've tried adding limit and count to the BQL query without success. Any suggestions or help would be greatly appreciated.