/Parse Vulnerability Assessment Result Files into HTML.sql
Last active Feb 16, 2021
Script to parse multiple Vulnerability Assessment Tool result files into an HTML reference list ( https://eitanblumin.com/sql-vulnerability-assessment-tool-rules-reference-list/ )
/****************************************************************** | |
Parse SQL Vulnerability Assessment Tool Results | |
*********************************************** | |
Author: Eitan Blumin | https://www.eitanblumin.com | |
Description: | |
Use this script to parse a Vulnerability Assessment Tool | |
results file into a relational structure and save in an HTML page. | |
This will output the T-SQL queries used by VAT behind the scenes | |
and their respective meta-data, as displayed in the VAT. | |
Also, if you uncomment two additional lines in the SQL | |
results below, then it will also output the results | |
saved in your specific results file. | |
Instructions: | |
1. Use SSMS to run a vulnerability check on your database. | |
2. Save the results file to a location accessible by a SQL Server. | |
3. Change the file paths to the JSON files in the insertion into #SourceFiles at line 47. | |
4. Run the script. | |
5. Copy the entirety of the "Contents" column into a text editor. | |
6. Find the line that says "Replace this line with the contents from the Nav column". | |
7. Copy the entirety of the "Nav" column and use it to replace the line found in step 6. | |
8. Save the text file as a web page with the "html" extension. | |
9. Open the web page with your favorite browser and review the results. | |
Change Log: | |
2020-11-11 - Added detailed instructions for saving results as a web page, add support for multiple platforms | |
2020-01-27 - Fixed severity anchor names and used textual anchors instead of meaningless numbers | |
2020-01-14 - Added Split_On_Upper_Case | |
2020-01-13 - Added comments, moved some columns around, added SINGLE_CLOB option | |
2019-07-21 - Initial version | |
*******************************************************************/ | |
GO | |
USE tempdb | |
GO | |
IF OBJECT_ID('tempdb..#SourceFiles') IS NOT NULL DROP TABLE #SourceFiles; | |
CREATE TABLE #SourceFiles | |
( | |
SourcePath NVARCHAR(4000), | |
PlatformName SYSNAME, | |
PlatformIconPath NVARCHAR(4000) | |
); | |
INSERT INTO #SourceFiles | |
(SourcePath, PlatformName, PlatformIconPath) | |
VALUES | |
('C:\Temp\sqlsrv_scan.json', 'SQL Server', 'sqlvm.png'), | |
('C:\Temp\sqldb_scan.json', 'SQL Database', 'sqldb.png'), | |
('C:\Temp\sqlmi_scan.json', 'SQL Managed Instance', 'sqlmi.png'), | |
('C:\Temp\sqldw_scan.json', 'Azure Synapse', 'sqlsynapse.png') | |
GO | |
IF OBJECT_ID('tempdb..#data') IS NOT NULL DROP TABLE #data; | |
IF OBJECT_ID('dbo.Split_On_Upper_Case') IS NOT NULL DROP FUNCTION dbo.Split_On_Upper_Case; | |
GO | |
-- Source: https://stackoverflow.com/questions/23470794/split-words-with-a-capital-letter-in-sql | |
Create Function dbo.Split_On_Upper_Case(@Temp VarChar(1000)) | |
Returns VarChar(1000) | |
AS | |
Begin | |
Declare @KeepValues as varchar(50) | |
Set @KeepValues = '%[^ ][A-Z]%' | |
While PatIndex(@KeepValues collate Latin1_General_Bin, @Temp) > 0 | |
Set @Temp = Stuff(@Temp, PatIndex(@KeepValues collate Latin1_General_Bin, @Temp) + 1, 0, ' ') | |
Return @Temp | |
End | |
GO | |
SET NOCOUNT ON; | |
DECLARE @j NVARCHAR(MAX), @cmd NVARCHAR(MAX); | |
DECLARE @SourcePath NVARCHAR(4000), @PlatformName SYSNAME, @PlatformIconPath NVARCHAR(4000); | |
IF OBJECT_ID('tempdb..#data') IS NOT NULL DROP TABLE #data; | |
CREATE TABLE #data | |
( | |
[CategoryTitle] NVARCHAR(1000) COLLATE database_default | |
, [Category] NVARCHAR(1000) COLLATE database_default | |
, [Severity] NVARCHAR(1000) COLLATE database_default | |
, [Severity_Num] TINYINT | |
, [RuleId] VARCHAR(256) COLLATE database_default PRIMARY KEY CLUSTERED | |
, [Title] VARCHAR(1000) COLLATE database_default | |
, [Description] VARCHAR(MAX) COLLATE database_default | |
, [Rationale] VARCHAR(MAX) COLLATE database_default NULL | |
, [Query] VARCHAR(MAX) COLLATE database_default NULL | |
, [RuleType] VARCHAR(256) COLLATE database_default NULL | |
, [ColumnNames] VARCHAR(MAX) COLLATE database_default NULL | |
, [RemediationScript] VARCHAR(MAX) COLLATE database_default NULL | |
, [RemediationDescription] VARCHAR(MAX) COLLATE database_default NULL | |
, [PlatformIcons] VARCHAR(4000) COLLATE database_default NULL | |
, [PlatformLabels] VARCHAR(4000) COLLATE database_default NULL | |
); | |
DECLARE Paths CURSOR | |
LOCAL FAST_FORWARD | |
FOR | |
SELECT SourcePath, PlatformName, PlatformIconPath | |
FROM #SourceFiles | |
OPEN Paths | |
FETCH NEXT FROM Paths INTO @SourcePath, @PlatformName, @PlatformIconPath | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
SET @j = NULL; | |
SET @cmd = N' | |
SELECT @j = BulkColumn | |
FROM OPENROWSET ( | |
BULK ' + QUOTENAME(@SourcePath, N'''') + N' | |
--, SINGLE_CLOB -- uncomment this if the file is ASCII | |
, SINGLE_NCLOB -- uncomment this if the file is Unicode | |
) [FileName]' | |
EXEC sp_executesql @cmd, N'@j NVARCHAR(MAX) OUTPUT', @j OUTPUT; | |
IF ISJSON(@j) = 1 | |
PRINT N'Imported JSON is valid: ' + @SourcePath | |
ELSE | |
BEGIN | |
RAISERROR(N'Imported JSON is invalid: %s', 16,1,@SourcePath); | |
GOTO Quit | |
END | |
;WITH Src | |
AS | |
( | |
SELECT | |
dbo.Split_On_Upper_Case(JSON_VALUE(b.[value], '$.Category')) COLLATE database_default AS [CategoryTitle] | |
, JSON_VALUE(b.[value], '$.Category') COLLATE database_default AS [Category] | |
, JSON_VALUE(b.[value], '$.Severity') COLLATE database_default AS [Severity] | |
, CASE JSON_VALUE(b.[value], '$.Severity') WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 WHEN 'Low' THEN 3 ELSE 0 END AS [Severity_Num] | |
, b.[key] COLLATE database_default AS RuleId | |
, JSON_VALUE(b.[value], '$.Title') COLLATE database_default AS [Title] | |
, JSON_VALUE(b.[value], '$.Description') COLLATE database_default AS [Description] | |
, JSON_VALUE(b.[value], '$.Rationale') COLLATE database_default AS [Rationale] | |
, ISNULL(JSON_VALUE(b.[value], '$.QueryCheck.Query'), qc.[value]) COLLATE database_default AS [Query] | |
, JSON_VALUE(b.[value], '$.RuleType') COLLATE database_default AS [RuleType] | |
, JSON_QUERY(b.[value], '$.QueryCheck.ColumnNames') COLLATE database_default AS [ColumnNames] | |
, JSON_VALUE(r.[value], '$.Remediation.Script') COLLATE database_default AS [RemediationScript] | |
, JSON_VALUE(r.[value], '$.Remediation.Description') COLLATE database_default AS [RemediationDescription] | |
-- Uncomment the two lines below if you want to output the specific results for your server | |
--, JSON_VALUE(r.[value], '$.Status') COLLATE database_default AS [FindingStatus] | |
--, JSON_QUERY(r.[value], '$.QueryResults') COLLATE database_default AS [FindingQueryResults] | |
FROM OPENJSON(@j, '$.Scans') AS a | |
CROSS APPLY OPENJSON(a.[value], '$.Rules') AS b | |
CROSS APPLY OPENJSON(a.[value], '$.Results') AS r | |
OUTER APPLY (SELECT [value] FROM OPENJSON(b.[value], '$.QueryCheck') WHERE [key] = 'Query') AS qc | |
WHERE b.[key] = JSON_VALUE(r.[value], '$.RuleId') | |
) | |
MERGE INTO #data AS Trgt | |
USING Src ON Trgt.RuleId = Src.RuleId | |
WHEN NOT MATCHED THEN | |
INSERT ([CategoryTitle], [Category], [Severity], [Severity_Num], [RuleId], [Title], [Description] | |
, [Rationale], [Query], [RuleType], [ColumnNames], [RemediationScript], [RemediationDescription] | |
, [PlatformIcons], [PlatformLabels]) | |
VALUES ([CategoryTitle], [Category], [Severity], [Severity_Num], [RuleId], [Title], [Description] | |
, [Rationale], [Query], [RuleType], [ColumnNames], [RemediationScript], [RemediationDescription] | |
, N'<img height="16" src="' + @PlatformIconPath + N'" title=' + QUOTENAME(@PlatformName, N'"') + N' />' | |
, N'<a title=' + QUOTENAME(@PlatformName, N'"') + N'><img height="16" src="' + @PlatformIconPath + N'" /> ' + @PlatformName + N'</a>' | |
) | |
WHEN MATCHED THEN | |
UPDATE SET | |
[PlatformIcons] = ISNULL(Trgt.[PlatformIcons] + N' ', N'') + N'<img height="16" src="' + @PlatformIconPath + N'" title=' + QUOTENAME(@PlatformName, N'"') + N' />' | |
, [PlatformLabels] = ISNULL(Trgt.[PlatformLabels] + N', ', N'') + N'<a title=' + QUOTENAME(@PlatformName, N'"') + N'><img height="16" src="' + @PlatformIconPath + N'" /> ' + @PlatformName + N'</a>' | |
; | |
FETCH NEXT FROM Paths INTO @SourcePath, @PlatformName, @PlatformIconPath | |
END | |
DECLARE | |
@HTMLHeader NVARCHAR(MAX) | |
, @HTMLFooter NVARCHAR(MAX) | |
, @HTMLTemplateCategoryStart NVARCHAR(MAX) | |
, @HTMLTemplateCategoryEnd NVARCHAR(MAX) | |
, @HTMLTemplateSeverityStart NVARCHAR(MAX) | |
, @HTMLTemplateSeverityEnd NVARCHAR(MAX) | |
, @HTMLTemplatePerRow NVARCHAR(MAX) | |
, @HTMLNavHeader NVARCHAR(MAX) | |
, @HTMLNavFooter NVARCHAR(MAX) | |
, @HTMLNavCategoryStart NVARCHAR(MAX) | |
, @HTMLNavCategoryEnd NVARCHAR(MAX) | |
, @HTMLNavSeverityStart NVARCHAR(MAX) | |
, @HTMLNavSeverityEnd NVARCHAR(MAX) | |
, @HTMLNavPerRow NVARCHAR(MAX) | |
SET @HTMLHeader = N'<!doctype html> | |
<html lang="en"> | |
<head> | |
<meta charset="utf-8" /> | |
<meta name="viewport" content="width=device-width, initial-scale=1" /> | |
<link rel="stylesheet" href="assets/css/main.css" /> | |
<title>Vulnerability Assessment Tool Rules</title> | |
<style>pre { | |
/*background-color: lightgray;*/ | |
white-space: pre-wrap; /* css-3 */ | |
white-space: -moz-pre-wrap; /* Mozilla, since 1999 */ | |
white-space: -pre-wrap; /* Opera 4-6 */ | |
white-space: -o-pre-wrap; /* Opera 7 */ | |
word-wrap: break-word; /* Internet Explorer 5.5+ */ | |
}</style> | |
</head> | |
<body> | |
<!-- Replace this line with the contents from the Nav column --> | |
<main>' | |
SET @HTMLFooter = N' | |
</main> | |
</body></html>' | |
SET @HTMLTemplateCategoryStart = N'<section><header><h1 align="left"><a id="{{CategoryId}}"></a>{{Category}} - {{RuleCount}} rule(s)</h1></header>' | |
SET @HTMLTemplateCategoryEnd = N'</section>' | |
SET @HTMLTemplateSeverityStart = N'<section><header><h3 align="left"><a id="{{CategoryId}}_{{Severity}}"></a>Severity: {{Severity}}</h3></header> | |
<table cellspacing="0" border="1" cellpadding="3">' | |
SET @HTMLTemplateSeverityEnd = N'</table><p style="font-size: 12px"><a href="#nav">☝ back to top</a></p><hr></section>' | |
SET @HTMLTemplatePerRow = N'<tr><td><article> | |
<header><h3><a id="Rule_{{RuleId}}"></a>{{RuleId}}: {{Title}}</h3></header> | |
<p> | |
Applies To: {{PlatformLabels}}<br/> | |
<small style="font-size: 12px"><a href="#Rule_{{RuleId}}" title="anchor link">⚓ anchor link</a></small> | |
</p> | |
<details> | |
<summary>{{Description}}</summary> | |
<h4>Rationale:</h4><p>{{Rationale}}</p> | |
<h4>Query:</h4><p><pre>{{Query}}</pre></p> | |
<h4>Remediation:</h4> | |
<p>{{RemediationDescription}}</p> | |
<p><pre>{{RemediationScript}}</pre></p> | |
</details> | |
</article></td></tr>' | |
SET @HTMLNavHeader = N'<aside id="nav"><header><h1>Navigation</h1></header><nav><ul>' | |
SET @HTMLNavFooter = N'</ul></nav></aside>' | |
SET @HTMLNavCategoryStart = N'<li><a href="#{{CategoryId}}">{{Category}}</a> - {{RuleCount}} rule(s)<ul>' | |
SET @HTMLNavCategoryEnd = N'</ul></li>' | |
SET @HTMLNavSeverityStart = N'<li><a href="#{{CategoryId}}_{{Severity}}">Severity: {{Severity}}</a> - {{RuleCount}} rule(s)<ol>' | |
SET @HTMLNavSeverityEnd = N'</ol></li>' | |
SET @HTMLNavPerRow = N'<li><a href="#Rule_{{RuleId}}">{{RuleId}}</a>: {{Title}} {{PlatformIcons}}</li>' | |
--SET @HTMLNavSeverityStart = N'<li><a href="#{{CategoryId}}_{{Severity}}">Severity: {{Severity}}</a> - {{RuleCount}} rule(s)' | |
--SET @HTMLNavSeverityEnd = N'</li>' | |
--SET @HTMLNavPerRow = N'' | |
SELECT | |
Contents = | |
CASE | |
WHEN [Severity] IS NULL THEN -- Category header | |
CASE WHEN [CategoryId] > 1 THEN @HTMLTemplateCategoryEnd ELSE @HTMLHeader END | |
+ REPLACE(REPLACE(REPLACE( | |
@HTMLTemplateCategoryStart | |
, N'{{Category}}', [CategoryTitle]) | |
, N'{{RuleCount}}', CONVERT(nvarchar,COUNT(*))) | |
, N'{{CategoryId}}', [Category]) | |
WHEN [Severity] IS NOT NULL AND [RuleId] IS NULL THEN -- Severity header | |
REPLACE(REPLACE(REPLACE(REPLACE( | |
@HTMLTemplateSeverityStart | |
, N'{{Severity}}', [Severity]) | |
, N'{{RuleCount}}', CONVERT(nvarchar,COUNT(*))) | |
, N'{{SeverityNum}}', [Severity_Num]) | |
, N'{{CategoryId}}', [Category]) | |
ELSE -- Data Row | |
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( | |
@HTMLTemplatePerRow | |
,N'{{RuleId}}',[RuleId]) | |
,N'{{Title}}',[Title]) | |
,N'{{Description}}',[Description]) | |
,N'{{Rationale}}',[Rationale]) | |
,N'{{Query}}',ISNULL([Query], N'')) | |
,N'{{RemediationScript}}', ISNULL([RemediationScript],N'')) | |
,N'{{RemediationDescription}}', [RemediationDescription]) | |
,N'{{Severity}}', [Severity]) | |
,N'{{PlatformLabels}}', [PlatformLabels]) | |
END | |
+ CASE WHEN [RuleId_Desc] = 1 THEN @HTMLTemplateSeverityEnd ELSE N'' END | |
+ CASE WHEN Everything_Desc = 1 THEN @HTMLFooter ELSE N'' END | |
, Nav = | |
CASE | |
WHEN [Severity] IS NULL THEN -- Category header | |
CASE WHEN [CategoryId] > 1 THEN @HTMLNavCategoryEnd ELSE @HTMLNavHeader END | |
+ REPLACE(REPLACE(REPLACE( | |
@HTMLNavCategoryStart | |
, N'{{Category}}', [CategoryTitle]) | |
, N'{{RuleCount}}', CONVERT(nvarchar,COUNT(*))) | |
, N'{{CategoryId}}', [Category]) | |
WHEN [Severity] IS NOT NULL AND [RuleId] IS NULL THEN -- Severity header | |
REPLACE(REPLACE(REPLACE(REPLACE( | |
@HTMLNavSeverityStart | |
, N'{{Severity}}', [Severity]) | |
, N'{{RuleCount}}', CONVERT(nvarchar,COUNT(*))) | |
, N'{{SeverityNum}}', [Severity_Num]) | |
, N'{{CategoryId}}', [Category]) | |
ELSE -- Data Row | |
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( | |
@HTMLNavPerRow | |
,N'{{RuleId}}',[RuleId]) | |
,N'{{Title}}',[Title]) | |
,N'{{Description}}',[Description]) | |
,N'{{Rationale}}',[Rationale]) | |
,N'{{Query}}',ISNULL([Query], N'')) | |
,N'{{RemediationScript}}', ISNULL([RemediationScript],N'')) | |
,N'{{RemediationDescription}}', [RemediationDescription]) | |
,N'{{Severity}}', [Severity]) | |
,N'{{PlatformIcons}}', [PlatformIcons]) | |
END | |
+ CASE WHEN [RuleId_Desc] = 1 THEN @HTMLNavSeverityEnd ELSE N'' END | |
+ CASE WHEN Everything_Desc = 1 THEN @HTMLNavFooter ELSE N'' END | |
, COUNT(*) AS RuleCount | |
, * | |
FROM | |
( | |
SELECT | |
DENSE_RANK() OVER (ORDER BY [Category]) AS CategoryId | |
, DENSE_RANK() OVER (ORDER BY [Category] DESC) AS CategoryId_Desc | |
, DENSE_RANK() OVER (PARTITION BY [Category] ORDER BY [Severity_Num]) AS SeverityId | |
, RANK() OVER (PARTITION BY [Category], [Severity] ORDER BY [RuleId] DESC) AS RuleId_Desc | |
, RANK() OVER (ORDER BY [Category] DESC, [Severity_Num] DESC, [RuleId] DESC) AS Everything_Desc | |
, * | |
FROM #data | |
) AS q | |
GROUP BY GROUPING SETS | |
( | |
([Category],[CategoryTitle],[CategoryId],CategoryId_Desc) | |
,([Category],[CategoryTitle],[CategoryId],CategoryId_Desc,[Severity],[SeverityId],[Severity_Num]) | |
,([Category],[CategoryTitle],[CategoryId],CategoryId_Desc,[Severity],[SeverityId],[Severity_Num],RuleId_Desc,Everything_Desc,[RuleId],[Title],[Description],[Rationale],[Query],[RuleType],[ColumnNames],[RemediationScript],[RemediationDescription],[PlatformIcons],[PlatformLabels]) | |
) | |
ORDER BY CategoryId, ISNULL([SeverityId],0), RuleId | |
Quit: |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment