Technical FAQs

Ask a Question

Finding all Sources that Log a Specific Quantity

Issue
How to find out what sources/nodes in the power monitoring system (ION Enterprise, SPM, PME) are logging data for a specific quantity.

Product Line
ION Enterprise 6.0, Struxureware Power Monitoring 7.0, Power Monitoring Expert 7.2

Environment
SQL Server Management Studio (SSMS)
Database Manager

Cause
It may not be possible to check all the nodes in the system one by one to find out if a Data Recorder is logging one specific quantity if there are too many devices in the system.  As such it is desirable to write a SQL statement to return the result set.

Resolution
Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.

The solution is to write a query to return the desired result directly from the database.  Since the database schema has changed in newer versions of the software, below is the solution for each version.

Based on the software version, use SSMS to run one of the queries given below.  Replace the <quantity_id> by the actual quantityid of the measurement.

Assumption: The quantityid of the measurement in question is known, or the user is capable of finding the quantityid by searching the 'quantity' table in ION_Data database.


1.  ION Enterprise 6.0 and earlier versions

use ion_data
select * from source where id in
(select distinct dls.sourceid
from datalog dl inner join
datalogstamp dls on dl.datalogstampid = dls.id
where dl.quantityid =
<quantity_id>)

2.  Struxureware Power Monitoring and later versions

use ion_data
select * from source where id in
(select distinct dl2.sourceID
from datalog2 dl2 inner join
quantity q on dl2.quantityid = q.id
where q.id = 
<quantity_id>)

 
Was this helpful?
What can we do to improve the information ?