Script to Delete All Devices and Applications from a Location

Owned by Daniel Stuart-Kelly
Last updated: Nov 03, 2017 by Conor McCann
3 min read

A customer contacted us to request assistance deleting all devices and applications from a location in iQSonar Version 3

Following this process will irretrievably erase data relating to the specific location from an iQSonar v3 Database. You will not be able to recover data after running these commands on an iQSonar v3 Database.

Please ensure that you have copied or backed up any data you need to retain.

Pl;ease ensure that you are deleting the correct location ID.



Step-by-step guide

Following SQL Script deletes all data from LocationID 148 as an example. Any LocationID can be substituted to delete all devices and applications from that Location before finally deleting the location.

Select * from Location where LocationID in (148)

-- Delete the Troubleshooting Information
Delete from DeviceTroubleshooting
Delete from ApplicationTroubleshooting

-- Get the Device and Application ID's from the Appropriate Location
select * from Device where LocationID = 148
select * from FoundDevice where LocationID = 148
Select * from APplication where LocationID = 148
Select * from FoundApplication where LocationID = 148

-- Get the ApplicationID's to be deleted based on their Location ID
SElect ApplicationID from Application where LocationID = 148

-- Delete the Application references on other tables
Delete from MapApplicationNodeFoundDevice where FOundDEviceID in (Select FoundDEviceID from FOundDEvice where LocationID = 148)
Delete from MapApplicationNodeFoundApplication where FoundApplicationID in (SElect FoundApplicationID from FOundApplication where LocationID = 148)

Delete from dbo.MapApplicationNodeFoundDevice where ApplicationNodeID in (
Select ApplicationNodeID from APplicationNOde where ApplicatioNID in (select ApplicationID from Application Where LocationID = 148))
Delete from APplicationNOde where ApplicatioNID in (select ApplicationID from Application Where LocationID = 148)

delete from MSClusterIPv4Address where MSClusterNetworkInterfaceID in (
select MSClusterNetworkInterfaceID from MSClusterNetworkInterface where MSClusterNetworkID in (
SElect MSClusterNetworkID from MSClusterNetwork where ApplicationID in (
SElect APplicationID from Application where LocationID = 148)))

Delete from MSClusterNetworkInterface where MSClusterNetworkID in (
SElect MSClusterNetworkID from MSClusterNetwork where ApplicationID in (
SElect APplicationID from Application where LocationID = 148))

Delete from MSClusterNetwork where ApplicationID in (
SElect APplicationID from Application where LocationID = 148)

-- Using exec ApplicationEX_De Stored Procedure. This will need to be run using the output from the first
Select 'Exec ApplicationEX_de', ApplicationID from Application where LocationID = 148

-- Example Exec ApplicationEX_de ####


-- Delete the Found APplication Histories
Delete from FoundApplicationScanHistoryDebug where FOundApplicationScanHIstoryID in 
(Select FOundAPplicationScanHIstoryID from FoundApplicationScanHistory where FoundApplicationID in 
(SElect FoundAPplicationID from FOundAPplication where LocationID = 148))

Delete from FoundApplicationScanHistory where FoundApplicationID in 
(SElect FoundAPplicationID from FOundAPplication where LocationID = 148)

Delete from FoundApplicationCredentialHistory where FoundApplicationID in 
(SElect FoundAPplicationID from FOundAPplication where LocationID = 148)

Delete from FoundApplication where LocationID = 148


-- Find the DEviceID's in scope for the Delete
select DeviceID from Device where LocationID = 148

-- Delete the DeviceID's returned from the query using deviceex_de procedure
Select 'Exec DeviceEX_de', DeviceID from Device where LocationID = 148

-- Example Exec DeviceEX_De ####

-- Delete the Found DEvice Histories

Delete from FoundDeviceScanHistoryDebug where FOundDeviceScanHIstoryID in 
(Select FOundDeviceScanHIstoryID from FoundDeviceScanHistory where FoundDeviceID in 
(SElect FoundDeviceID from FOundDevice where LocationID = 148))

Delete from FoundDeviceScanHistory where FoundDeviceID in 
(SElect FoundDeviceID from FOundDevice where LocationID = 148)

Delete from FoundDeviceCredentialHistory where FoundDeviceID in 
(SElect FoundDeviceID from FOundDevice where LocationID = 148)

Delete from FOundDevicePOrt where FoundDEviceID in 
(SElect FoundDeviceID from FOundDevice where LocationID = 148)

Delete from FoundDevice where LocationID = 148


-- Deleting the IPRanges and Location

Delete from MapLocationCredential where LocationID = 148
Delete from MapLocation_Server where LocationID = 148
Delete from MapIPRange_Server where IPRangeID in (Select IPRangeID from IPRange where LocationID = 148)
Delete from MapIPRangePortList where IPRangeID in (Select IPRangeID from IPRange where LocationID = 148)
Delete from IPRange where LocationID = 148
Delete from Location where LocatioNID = 148

-- DONE!