SQL Server - Query for Generating Device, Point and Alarm List

SQL Server - Query for Generating Device, Point and Alarm List

The SQL query for generating a device, point, and alarm list is as follows:


SELECT TAG.LABEL AS URL
, D.NAME AS DEVICE
, P.NAME AS POINT
, ISNULL(E.DESCR,'') AS ALARM
FROM EG.POINT AS P
JOIN EG.DEVICE AS D
ON P.OBJ_ID = D.DEVICE_ID
JOIN EG.CONT_ADDR AS ADDR ON ADDR.OBJ_ID = D.DEVICE_ID
JOIN EG.TAG AS TAG ON TAG.TAG_ID = ADDR.CONT_ADDR_ID
LEFT OUTER JOIN EG.EVENT AS E
ON P.POINT_ID = E.POINT_ID
LEFT OUTER JOIN EG.V_PROP_VAL PV
ON PV.PROP_VAL_OBJ_ID = D.DEVICE_ID
AND PV.PROP_CODE = 'DELETED'
LEFT OUTER JOIN EG.V_PROP_VAL PV2
ON PV2.PROP_VAL_OBJ_ID = P.POINT_ID
AND PV2.PROP_CODE = 'DELETED'
WHERE PV.PROP_VAL IS NULL
AND PV2.PROP_VAL IS NULL
ORDER BY URL, DEVICE,POINT,ALARM


The above also includes the link URL, which can help identify where the devices are located on the network.

    • Related Articles

    • SQL Server - Query for Generating an Alarm Point List

      SELECT TAG.LABEL AS URL , D.NAME AS DEVICE , P.NAME AS POINT , E.DESCR AS ALARM FROM EG.POINT AS P JOIN EG.DEVICE AS D ON P.OBJ_ID = D.DEVICE_ID JOIN EG.CONT_ADDR AS ADDR ON ADDR.OBJ_ID = D.DEVICE_ID JOIN EG.TAG AS TAG ON TAG.TAG_ID = ...

    • SQL Server - Query for Generating an Non-Alarm Point List

      SELECT TAG.LABEL AS URL , D.NAME AS DEVICE , P.NAME AS POINT FROM EG.POINT AS P JOIN EG.DEVICE AS D ON P.OBJ_ID = D.DEVICE_ID JOIN EG.CONT_ADDR AS ADDR ON ADDR.OBJ_ID = D.DEVICE_ID JOIN EG.TAG AS TAG ON TAG.TAG_ID = ADDR.CONT_ADDR_ID LEFT OUTER JOIN ...

    • SQL Server - Manual Device Deletion

      If you need to remove a device from the dashboard and it is not possible to use the Delete button on the Administration > Devices page, you can do it through SQL Management Studio. Marking a Device as Deleted In Management Studio run the following ...

    • SQL Server - Removing (1) from Device Names on the Dashboard

      If a duplicate name occurs on one of the Device Gateways the SQL server will add (1) to the end of the name to distinguish it from the original. Once the duplicate naming issue is solved you may want to remove the (1) from the end of the device name. ...

    • SQL Server - Deleting Alarm State to Clear False Alarms from the Dashboard

      Occasionally, the alarm status on the dashboard will miss a clear event and it is necessary to delete the alarm state from the database in order to fix the problem. The following procedure steps through this event. Removing an alarm state from the DB ...