Mass Delete IP Address Range Locations

[CVannest]

I have over 400 IP Address Range Locations and I want to re-work how they are setup. I have the new CSV to import my new layout, but I can't find a way to mass delete the existing ones. Can anyone provide some help here?

[Jeff_Ziegler]

Thanks. It is appreciated.

[esr]

The constraint errornoted is because IP locations are referenced in another table, tblAssets. You could circumvent this by changing the locationID for your assets prior to running a delete script. The script below will change all your asset ip locations to the built-in "undefined" and delete all other IP locations.

Update tblAssets set locationID = -1
GO
Delete from tsysIPlocations where locationID <> -1
GO

As noted above by Lansweeper themselves, always caution when tinkering with your database. Backup before making changes and test in dev if possible.

[Jeff_Ziegler]

Nope.

The DELETE statement conflicted with the REFERENCE constraint "FK_tblAssets_tsysIPLocations". The conflict occurred in database "lansweeperdb", table "dbo.tblAssets", column 'LocationID'.
The statement has been terminated.

Possibly set all tblAssets.LocationIPs to -1 first, then drop them...?

[Jeff_Ziegler]

Similar question - I want to mass delete all IP Locations from the tsysIPLocations table. Easy enough. But I see referential integrity to at least tblAssets. I don't see any others in the tbl* tables (at least not identified), but unsure about other tsys* tables.

Is this reference updated via procedure, or would it be expected that after I drop all records in tsysIPLocations (except 'Undefined') I need to also update all tblAssets.LocationIds to -1?

[Bruce_B]

There's unfortunately no functionality available in the front end to mass delete IP Ranges, though you could delete them using a database script:

• When executing a database script, we always recommend backing up your installation
  
• Modify the script below with the name of the relevant scanning server.
  
• Paste the script below under Configuration\Database Scripts and click the Execute SQL Code button. Do note this will delete all IP Range scanning targets for the scanning server in the script

Delete from tsysIPScanRanges where servername ='scanservername'