Technical FAQs

Ask a Question

Struxureware Power Monitoring and Power Monitoring Expert - How can I delete a device and all of its data?

Issue
A meter was installed into the StruxureWare Power Monitoring (SPM) or Power Monitoring Expert (PME) system and has been logging data, waveforms, and events for any length of time. For whatever the reason, this meter's data is no longer required and it is desired that all traces of this meter be completely removed from the databases.

Product Line
StruxureWare Power Monitoring 7.0.x (SPM)
Power Monitoring Expert 7.2.x (PME)
Power Monitoring Expert 8.x (PME)

Environment
SQL Server Management Studio
ION Databases

Cause
A meter was added to the software but is no longer needed. Some reasons may include: data is no longer needed; spelling error; test meter data deletion; etc.

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.*  

1. Back up the following databases: ION_Network, ION_Data, ApplicationModules.
2. Stop the ION Log Inserter service. It will also be necessary to stop the Windows services related to the Application Modules features. In SPM 7.0.x, these services start with the name "Schneider Electric"; in PME 7.2.x and PME 8, these services were re-branded and start with "ApplicationModules".


3. Open the Management Console and locate the device in the list. Take note of the full group.name of the device as this information will be needed later to configure a SQL script. NOTE: It is entirely possible the device is not in the Management Console. If this is the case, it is likely the full group.name is already known.
4. Delete the device from the Management Console. If the following prompt appears, select Cancel and then proceed to Step 5:
4a. If PME 8.0, be sure to delete this source from any configured sw alarms, Comms Loss or otherwise. If you do not do this, the Alarming system will recreate the source.
 


5. If there are logical devices associated with the device to be deleted, the logical devices must first be removed. Take note of each group.name of the logical devices as these will be needed later to configure a SQL script. Once each of the logical devices has been deleted from the Management Console, repeat Step 4 with the original device.
6. Once all devices have been documented by group.name and deleted from the Management Console, the following SQL scripts must be configured to delete each device individually.
NOTE:
For SPM 7.0.x and PME 7.2.x, use SQL Script "SPM7_delete_device_v3.sql"
For PME 8, use SQL Script "PME8_delete_device_v3.sql"

6a. Toward the top of the attached script, there is a code segment that looks like this:

-- will need to put in group and name for device to be deleted - in this example device name is 'test.8600'
SET @oldGroupName = 'test'
SET @oldDeviceName = '8600'

6b. Replace 'test' with the group documented earlier. Be sure to leave the single quote characters.
6c. Replace '8600' with the name documented earlier. Be sure to leave the single quote characters.

7. Save and then execute the SQL script. NOTE: There may be additional cleanup required if the VIP was used to log data from the deleted device. Links to the VIP modules that reference the deleted device will have to be removed manually using Designer. If this is not done, the System Log in the Management Console will being throwing errors.
8. Restart the four services stopped at the beginning of this procedure.

Note: It may be necessary to delete entries associated with the dbo.Gate entries. This can be done with the following SQL query, again replacing text.8600 as was done above.

DECLARE @find varchar(250)
SET @find = 'A.TestRemove';

DECLARE @ChannelID_Temp int
Set @ChannelID_temp = (SELECT ChannelID from PortChannelAffinity Where PortID in (SELECT ID FROM Port WHERE GateID in (SELECT ID FROM Gate WHERE name like @find)))

DELETE FROM connectionRequest WHERE PortID in (SELECT ID FROM Port WHERE GateID in (SELECT ID FROM Gate WHERE name like @find))
DELETE FROM Security WHERE DeviceID in (Select ID FROM Device WHERE PortID in(SELECT ID FROM Port WHERE GateID in (SELECT ID FROM Gate WHERE name like @find)))
DELETE FROM Device WHERE PortID in (SELECT ID FROM Port WHERE GateID in (SELECT ID FROM Gate WHERE name like @find))
DELETE FROM PortState WHERE PortID in (SELECT ID FROM Port WHERE GateID in (SELECT ID FROM Gate WHERE name like @find))
DELETE FROM PortChannelAffinity WHERE PortID in (SELECT ID FROM Port WHERE GateID in (SELECT ID FROM Gate WHERE name like @find))
DELETE FROM Channel WHERE ID = @ChannelID_temp
DELETE FROM Port WHERE GateID in (SELECT ID FROM Gate WHERE name like @find)
DELETE FROM Gate WHERE name like @find
 
Was this helpful?
What can we do to improve the information ?