When using Norman Patch and Remediation/Norman Risk and Vulnerability Manager the area you designated for package storage might grow too large and you might run out of disk space.
The solution is to manually delete the packages from the storage folder, but this cannot be done without the SQL database being updated with the change. Since the SQL database contains information about packages downloaded by the server application and where those packages are stored, it is necessary to both retrieve, and then remove, that information from the database before actually deleting the files.
This guide will demonstrate how this is achieved.
To be able to perform the necessary tasks, you must have MS SQL Management Studio installed, as you will have to execute some SQL queries.
A guide to the installation of MS SQL Management Studio will not be given in this support article, but can be found online.
NOTE: It is recommended that you install MS SQL Management Studio 2008, since this will work for both MS SQL 2005 and MS SQL 2008.
Open SQL Management Studio and connect to the Norman Patch & Remediation/Risk and Vulnerability SQL database. The easiest way to connect to the server is to use “Windows Authentication”, but remember that you will have to be logged into Windows with the same username and password used when Norman Patch & Remediation/Risk and Vulnerability was installed. This is normally local administrator or, if the computer is part of a domain, domain administrator.
After connecting to the SQL server, you will see several folders with names like “Databases”, “Security” and so on. You need not open or do anything else in the Management windows right now.
Now you need to open the “removepatchtype.sql” file. The file should open in the MS SQL Management Studio window and present you with the script. In this script you need only change one variable.
Find the text where it says “SET @IMPACT = 'Critical'”.
Above this you will find a line beginning with ‘- -‘, followed by “Critical, Recommended, Critical – 01, …”.
These are the types of patches found in your patch server. The SQL script only works for one type of patch each to time you run it, so for each time you run the script you will have to change “SET @IMPACT = 'Critical'” to “SET @IMPACT = ‘Recommended’”, “SET @IMPACT = ‘Critical – 01’”, and so on to remove the packages for all the different types.
NOTE: DO NOT EXECUTE THE SCRIPT YET!
NOTE2: If any other part of the script is changed, you server might become broken and it must be reinstalled for it to work again.
You now have to get ready the “DeleteFiles.bat” file. Right click on the file and select “Edit”. This will open the file in a text editor, most likely Notepad.
If you do not have the “Edit” option, you can simply start Notepad and then open the “DeleteFiles.bat” file by selecting “File” and then “Open”.
Without closing the “DeleteFiles.bat” file, return to MS SQL Management Studio where you have the SQL script open. Before you run the script for the first time, make sure it says “SET @IMPACT = 'Critical'” and nothing else in the script has been changed.
On the upper left-hand side for the windows you will find a button that says “Execute”. Press this to run the script. Give the script time to finish. This might take some time depending on the number of packages and the hardware of the server.
When the script has finished, you will see a “Results” tab with 1 or more lines starting with “RMDIR /S /Q …”. Right click anywhere inside the “Results” tab and click “Select All”. Then right click again and select “Copy”.
Go back to your Notepad with the “DeleteFiles.bat” file open and paste the content you have just copied below “@ECHO OFF”.
If you wish to delete the files for other types of packages, you will have to change the SQL script before execution.
The only thing you need to change in the SQL script is the text “SET @IMPACT = 'Critical'”. Change this to “SET @IMPACT = ‘Recommended’”. After doing this, execute the script and copy the results into “DeleteFiles.bat” file as instructed in step 4.
Repeat step 4.1 for each package type you wish to delete.
At this point you will have a batch file that contains deletion instructions for all the package types you wish to delete. Remember to save the file.
There are two ways you can run the Batch file:
1. Open the Windows Command Line (CMD), navigate to the where the batch file is located, type in the name of the file and then press ‘Enter’. This will let you know if the batch file contained any errors or any of the locations provided did not exist.
2. Just double click on the batch file to run it. This will not provide you with any information, since the command line window will automatically close when batch file finishes.
If you run the SQL script for all the different package types and get no lines in the Results tab, your packages do not exist in your SQL database.
There can be different reasons for this, but the most likely scenario is that an upgrade from a previous version removed all the pointers to the packages on your server.
To remedy the issue, you can manually delete all the packages in the content folder.
Below is the standard path for the packages:
“C:\Program Files\Norman\Norman Risk and Vulnerability Manager\Content\00000000-0000-0000-0000-000000000000”
You can delete all the folders here except “EndpointComponents” and “GssComponents”.
After doing this you should “Reset” the “Replication” by going into the Norman Patch & Remediation/Risk and Vulnerability Management console, select ‘Tools’ | ‘Subscription update’ | ‘Reset ‘. Here you will have to tick ‘Cache both metadata and critical packages’ and then click ‘Update Now’.