Highlighted
Respected Contributor.. jmatheny Respected Contributor..
Respected Contributor..
177 views

Purge old scans from WebInspect database

Jump to solution

So I ran a query on my WebInspect database and see that I have a LOT of scan data in the db for a lot of scans that were deleted from the WebInspect Enterprise GUI.

ScanState     count
3                        1807
4                           463
5                             84
9                                6
18                        127
23                    15692

Currently, I have 1344 scans in my WebInspect Enterprise console. According to the numbers above, there are 15,692 scans in the db that failed to archive, and 127 that are "deleting".

How can I purge all of the info for these orphan scans from the database and reclaim the space?

I know that I can run DELETE FROM Scan WHERE ScanID='Specific ScanID', but this doesn't delete all remnants of the scan and free up the space in the database.

@ebell @HansEnders 

0 Likes
1 Solution

Accepted Solutions
Micro Focus Expert
Micro Focus Expert

Re: Purge old scans from WebInspect database

Jump to solution

Please read the header for how to best utilize this script. But it is worth repeating the following:

  • Make a backup of your database first
  • Run this during off-peak hours
  • Run in small batches
  • Make sure to rebuild the DB indexes after each run
/*

** ENSURE YOU HAVE A FULL BACKUP OF THE DATABASE **

@Date is the date for which you want to delete all previous scans.

We suggest finding your oldest scan, and testing this against it first.
This script uses the WIE task service to delete scans in a similar fashion to
how they would be deleted if a user selected them in the UI, and then selected delete, so it’s relatively safe, but it can cause a large number of scans to be queued for delete, and it can lead to poor WIE performance while running.

Also suggest scheduling it to run during off peak hours, and only do so in small batches.

For example, delete everything older than 1/1/2015 one night, then do everything older than 2/1/2015 the next, or something like that.

Make sure to rebuild the DB indexes AFTER EACH RUN.

*/

DECLARE @Date DATETIME
DECLARE @ScanID UNIQUEIDENTIFIER
SET @Date = '1/1/2014'
DECLARE ScansToDelete CURSOR for 
	SELECT ScanID FROM Scan WHERE ScanEndTime < @Date 
OPEN ScansToDelete 
FETCH NEXT FROM ScansToDelete INTO @ScanID 
WHILE @@FETCH_STATUS = 0 
BEGIN 
	EXEC ScanRemoveDeletedData @ScanID, null
	FETCH NEXT FROM ScansToDelete INTO @ScanID 
END
CLOSE ScansToDelete 
DEALLOCATE ScansToDelete

View solution in original post

3 Replies
Micro Focus Expert
Micro Focus Expert

Re: Purge old scans from WebInspect database

Jump to solution

Please read the header for how to best utilize this script. But it is worth repeating the following:

  • Make a backup of your database first
  • Run this during off-peak hours
  • Run in small batches
  • Make sure to rebuild the DB indexes after each run
/*

** ENSURE YOU HAVE A FULL BACKUP OF THE DATABASE **

@Date is the date for which you want to delete all previous scans.

We suggest finding your oldest scan, and testing this against it first.
This script uses the WIE task service to delete scans in a similar fashion to
how they would be deleted if a user selected them in the UI, and then selected delete, so it’s relatively safe, but it can cause a large number of scans to be queued for delete, and it can lead to poor WIE performance while running.

Also suggest scheduling it to run during off peak hours, and only do so in small batches.

For example, delete everything older than 1/1/2015 one night, then do everything older than 2/1/2015 the next, or something like that.

Make sure to rebuild the DB indexes AFTER EACH RUN.

*/

DECLARE @Date DATETIME
DECLARE @ScanID UNIQUEIDENTIFIER
SET @Date = '1/1/2014'
DECLARE ScansToDelete CURSOR for 
	SELECT ScanID FROM Scan WHERE ScanEndTime < @Date 
OPEN ScansToDelete 
FETCH NEXT FROM ScansToDelete INTO @ScanID 
WHILE @@FETCH_STATUS = 0 
BEGIN 
	EXEC ScanRemoveDeletedData @ScanID, null
	FETCH NEXT FROM ScansToDelete INTO @ScanID 
END
CLOSE ScansToDelete 
DEALLOCATE ScansToDelete

View solution in original post

Respected Contributor.. jmatheny Respected Contributor..
Respected Contributor..

Re: Purge old scans from WebInspect database

Jump to solution

Is there a way that this query can be modified to delete scans that are in a specific scan state (i.e. scan state 4).

Would this work?

DECLARE ScansToDelete CURSOR for 
	SELECT ScanID FROM Scan WHERE ScanState = 4 
OPEN ScansToDelete 

 @ebell 

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Purge old scans from WebInspect database

Jump to solution

Sounds reasonable; however, I would:

  1. Make sure you have a backup of your database
  2. Test in non-production first
  3. Include the rest of the initial script. 🙂
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.