If the area that you designate for package storage grows too large and you run out of Disk Space when using Norman Patch & Remediation and Vulnerability Manager, you will need to manually delete the packages from the storage folder. 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.
PrerequisitesIn order to perform the necessary tasks, you must have MS SQL Management Studio installed, as you will have to execute SQL Queries.
You will also need to download the following two files: removepatchtype.sql and DeleteFiles.bat
Step 1- Connect to the SQL ServerOpen the 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 the local administrator, or if the computer is part of a domain, the domain administrator.
Step 2- Setting up the SQL Script
- After connecting to the SQL server, you will see several folders with names like Database, Security and so on. You do not need to do anything else in the Management Windows right now.
- Open the removepatchtype.sql file. The file should open in the MS SQL Management Studio window and present you with the script
- 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 time you run the script, you will have to change the “SET @IMPACT = 'Critical'” to “SET @IMPACT = ‘Recommended’”, “SET @IMPACT = ‘Critical – 01’”, and so on to remove the packages for all the different types
- Do Not Execute the Script Yet
If any other part of the script is changed, your server may become broken and it must be reinstalled for it to work again.
Step 3- Setting up the Batch File
- You must now get the DeleteFiles.bat file ready
- Right-click on the file and select Edit which will open the file in a text editor, most likely Notepad
- If you do not have the Edit option, start Notepad and open the DeleteFiles.bat file by selecting File and Open
Step 4- Running the SQL Script
- Without closing the DeleteFiles.bat file, return to MS SQL Management Studio where you have the SQL script open
- Before running the script, ensure that it says SET @IMPACT = 'Critical' and that nothing else in the script has been changed
- On the upper left-hand side click Execute to run the script
- When the scrip has finished, you will see a Results tab with 1 or more lines starting with RMDIR /S /Q …
- Click anywhere inside the Results tab and click Select All
- Right click and select Copy
- Go back to your notepad with the DeleteFiles.bat file open and paste the content you have just copies below @ECHO OFF
Step 4.1- Running the SQL Script for Other Patch Types
- 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' to SET @IMPACT = ‘Recommended’
- Execute the script
- Copy the results into the DeleteFiles.bat file as instructed in step 4
- Repeat Step 4.1 for each package that you wish to delete
Step 5- Running the Batch Script
At this point you will have a batch file that contains deletion instructions for all the package types that you wish to delete. Remember to save the file.
There are two ways to run the Batch File:
- Open the Windows Command Line (CMD)
- Navigate to where the batch file is located
- Type in the name of the file and press Enter
- This will let you know if the batch file contained any errors or any of the locations provided did not exist
- 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 the batch file finishes
TroubleshootingIf you run the SQL script for all of the different package types and no lines are generated in the Results tab, your packages do not exist in your SQL database.
There can be many reasons for this, the most likely being that an upgrade from a previous version removed all of the pointers to the packages on your server.
To resolve this issue, you can follow the steps below to manually delete all of the packages in the content folder:
- Navigate to C:\Program Files\Norman\Norman Risk and Vulnerability Manager\Content\00000000-0000-0000-0000-000000000000
- Delete all the folders here except EndpointComponents and GssComponents.
- Reset the Replication by going into by going into the Norman Patch & Remediation/Risk and Vulnerability Management console
- Select Tools > Subscription Update
- Click Reset
- Select Cache both metadata and critical packages
- Click Update Now