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

Issue
A meter was installed into the Power Monitoring Expert (PME) or StruxureWare Power Monitoring (SPM) 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
Power Monitoring Expert 7.2.x
Power Monitoring Expert 8.x
Power Monitoring Expert 9.0
Power Monitoring Expert 2020
Power Monitoring Expert 2021
Power Monitoring Expert 2022
Power Monitoring Expert 2023

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 following services:
   • ION Log Inserter service
   • ION Application Modules Core Services Host
   • ION Application Modules Core Servicesx64 Host
   • ION Application Modules Data Services Host
   • ION Application Modules Data Servicesx64 Host
   • ION Application Modules Provider Engine Host
   • ION Application Modules Provider Enginex64 Host

NOTE: The Service Host related services were re-branded a during a few releases for clarity. Below are the related services for each version of the software:

PME 9.0 - PME 2022:

PME 7.2.x - PME 8.x:

3. Open the Management Console and locate the device in the list. Take note of the full "GroupName.DeviceName" of the device as this information will be needed later to configure a SQL script.

NOTE: It is possible that the device is not in the Management Console. If this is the case, it is likely the full "GroupName.DeviceName" is already known because it is an option in a report. Devices that have been deleted from Management Console and have data in the ION_Data database have their Name saved in the ION_Data dbo.Source table.

4. If the devices to be deleted have been renamed, then the "Name" Column in the ION_Network dbo.SRC_Source table needs to be referenced to get the proper device Name to enter into the script because the attached scripts reference the "Name" Column from the ION_Data databases (not the DisplayName column). Follow the steps below to confirm the Name of a device:
   1. Expand the ION_Network table tree
   2. Right click on the dbo.SRC_Source table and select top 1000 rows
   3. Navigate to your device by scrolling, or filtering for the DisplayName as shown in the image below.
   4. In this example, use the device name as the string shown under the "Name" column:
      • GroupName = "Test"
      • DeviceName = "Old"
5. Delete the device from Management Console. If Management Console does not allow the device to be deleted because it is associated with a Logical device, then proceed to Step 5:
   • Be sure to delete this source from any configured software alarms or VIP programs or the system will recreate the source create errors in the System Log.
6. If there are logical devices associated with the device to be deleted, the logical devices must first be removed. Take note of each "GroupName.DeviceName" 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.
7. Once all devices have been documented by "GroupName.DeviceName" and deleted from the Management Console, the following SQL scripts must be configured to delete each device individually.
8. Download the correct version of the script for the version of PME that is being used and follow the appropriate steps:
   • For PME 9.0 and newer:
     1. Download "DeleteSource_BatchDeleteDataPME2020_V04 3.sql" for PME 2020 and up or "PME9_DeleteSource_BatchDeleteData_V09.sql" for PME 9.0 and PME 9.1 and run the script in SSMS to create the stored procedure "spDM_DeleteSourceAndBatchDeleteData" in the ION_Data database that batch deletes data at 2500 rows per batch until all related data is deleted.
     2. Call the stored procedure to delete the device:
        
        USE [ION_Data]
        GO
        spDM_DeleteSourceAndBatchDeleteData 'GroupName.DeviceName'
        GO
        
     3. If multiple devices need to be deleted, then download "PME2020 SQLQuery to Delete multiple devices 3.sql" and list the devices in the Values section while maintaining the single quote characters to have it call the stored procedure created in step 1 for as many devices as needed
   • For SPM 7.0.x to PME 8.x
     1. Download "PME8_delete_device_v3.sql"for PME 8.x or "SPM7_delete_device_v3.sql" for PME 7.2.x and SPM 7.0.x.
     2. Toward the top of the attached scripts, 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
        
     3. Replace 'test' with the "GroupName" documented earlier for physical devices. Be sure to leave the single quote characters.
     4. Replace '8600' with the "DeviceName" documented earlier. Be sure to leave the single quote characters.
     5. Save and then execute the SQL script.
     6. Restart the services stopped at the beginning of this procedure.
        
   • For virtual devices on SPM 7.0.x to PME 8.x
     • Download "PME8_delete_virtual_device_v3.sql"for PME 8.x or "SPM7_delete_virtual_device_v3.sql" for PME 7.2.x and SPM 7.0.x.
     • Toward the top of the attached scripts, there is a code segment that looks like this:

3. Replace 'test' with the "DeviceName" of the virtual device. Be sure to leave the single quote characters.
4. Save and then execute the SQL script.
5. Restart the services stopped at the beginning of this procedure.

It may be necessary to delete entries associated with the dbo.Gate entries. This can be done with the following SQL query, after replacing "test.8600" with the correct "GroupName.DeviceName".

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