NiagaraFramework BQL expression

개요

Niagara Framework 에서 사용하기위한 BQL 예제모음

쿼리 예제 모음

Down Status

bql:select name as 'Name',out.value as 'Value',slotPath as 'Ord' from control:ControlPoint where status.down = 'true'

Disabled Status

bql:select name as 'Name',out.value as 'Value',slotPath as 'Ord' from control:ControlPoint where status.disabled = 'true'

overridden Status

bql:select name as 'Name',out.value as 'Value',slotPath as 'Ord' from control:ControlPoint where status.overridden = 'true'

Active Alarm Point Name and Status

bql:select name as 'Point Name',out as 'Point Status' from control:ControlPoint where status.alarm = 'true'

Active Alarms Filtered by Alarm Class

bql:select * from alarm:AlarmSourceExt where alarmClass = 'defaultAlarmClass' and status.alarm = true

LastOkTime

bql:select parent.name as 'Device', down as 'Down', lastOkTime as 'Last Ok Time', lastFailTime as 'Last Fail Time' from driver:PingHealth order by down DESC

Query Login/ Logout Records

station:|history:/stationName/SecurityHistory?period=last7Days|bql:select timestamp.toDateString as 'Date',timestamp.toTimeString as 'Time',operation as 'Operation',userName as 'User' where operation like 'Login' or operation like 'Logout'

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

local:|fox:|history:/ETRI_12_ELEC/AITest01?period=yesterday|view:workbench:CollectionTable

history:|bql:select *

bql:history:/StationName/F3_LAB1_TEMP?period=timeRange;start=2022-09-09T09:00:00.000+09:00;end=2022-09-16T10:00:00.000+09:00|view:workbench:CollectionTable

bql:history:HistoryRollup.rollup(history:RollupInterval 'oneMin')

bql:select timestamp,value,status where timestamp.second=0 and status='0'

local:|foxs:|history:/StationName/F4_LAB2_HUMI?period=last7Days|bql:select timestamp, value, status where timestamp.second=0 and status='0'

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://IP/ord?history:/SungHan_OFFICE/AHU_AHU_Box_HUMI?period=timeRange;start=2022-12-28T00:00:00.000+09:00;end=2022-12-29T00:00:00.000+09:00|bql:select *

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

DB Query

SELECT DATE_FORMAT(FROM_UNIXTIME(TIMESTAMP/1000), '%Y-%m-%d %H:%i:%s') as timestamp_converted, VALUE FROM ase_korea_acu100_rmh;

SELECT DATE_FORMAT(FROM_UNIXTIME(TIMESTAMP/1000),'%Y-%m-%d %H:%i:%s') as timestamp_converted, value FROM ASE_KOREA_acu100_rmh 

WHERE FROM_UNIXTIME(TIMESTAMP/1000,'%Y') = 2023 

And FROM_UNIXTIME(TIMESTAMP/1000,'%m') = 01 AND 

FROM_UNIXTIME(TIMESTAMP/1000,'%d') = 29 AND 

FROM_UNIXTIME(TIMESTAMP/1000,'%H') = 03 AND 

FROM_UNIXTIME(TIMESTAMP/1000,'%i') = 00 AND 

FROM_UNIXTIME(TIMESTAMP/1000,'%s') < 01

참조

NiagaraModules.com : https://niagaramodules.com/exchange/niagaramods/point-status-queries

Github (mrupperman) : https://gist.github.com/mrupperman/8a0761bbb416b8ef1ca4f51c228f63bf