EitanBlumin/Parse Vulnerability Assessment Result Files into HTML.sql

## Parse Vulnerability Assessment Result Files into HTML.sql
/******************************************************************
		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:
	/******************************************************************
	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: