Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save EitanBlumin/16d095b71d7fe0c9e4b032b4c80f64a0 to your computer and use it in GitHub Desktop.
Save EitanBlumin/16d095b71d7fe0c9e4b032b4c80f64a0 to your computer and use it in GitHub Desktop.
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.
You'll have to increase Maximum Non-XML data to a large number such as 1048576 to avoid truncation.
(Query Results > SQL Server > Results to Grid)
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:
2022-06-03 - Fixed unicode bug and added full image URLs
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\scans\scan_2022-06-03T11-20-32.json', 'SQL Server', 'https://eitanbluminblog.files.wordpress.com/2020/11/sqlvm.png?h=16'),
('C:\Temp\sqldb_scan.json', 'SQL Database', 'https://eitanbluminblog.files.wordpress.com/2020/11/sqldb.png?h=16'),
('C:\Temp\sqlmi_scan.json', 'SQL Managed Instance', 'https://eitanbluminblog.files.wordpress.com/2020/11/sqlmi.png?h=16'),
('C:\Temp\sqldw_scan.json', 'Azure Synapse', 'https://eitanbluminblog.files.wordpress.com/2020/11/sqlsynapse.png?h=16')
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] NVARCHAR(1000) COLLATE database_default
, [Description] nvarchar(MAX) COLLATE database_default
, [Rationale] nvarchar(MAX) COLLATE database_default NULL
, [Query] nvarchar(MAX) COLLATE database_default NULL
, [RuleType] nVARCHAR(256) COLLATE database_default NULL
, [ColumnNames] nvarchar(MAX) COLLATE database_default NULL
, [RemediationScript] nvarchar(MAX) COLLATE database_default NULL
, [RemediationDescription] nvarchar(MAX) COLLATE database_default NULL
, [PlatformIcons] nVARCHAR(MAX) COLLATE database_default NULL
, [PlatformLabels] nVARCHAR(MAX) COLLATE database_default NULL
);
DECLARE Paths CURSOR
LOCAL FAST_FORWARD
FOR
SELECT SourcePath, PlatformName, PlatformIconPath
FROM #SourceFiles
OPEN Paths
WHILE 1 = 1
BEGIN
FETCH NEXT FROM Paths INTO @SourcePath, @PlatformName, @PlatformIconPath;
IF @@FETCH_STATUS <> 0 BREAK;
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(CONVERT(nvarchar(MAX), 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>'
;
END
CLOSE Paths;
DEALLOCATE Paths;
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 CONVERT(nvarchar(MAX), @HTMLHeader) END
+ REPLACE(REPLACE(REPLACE(
CONVERT(nvarchar(MAX), @HTMLTemplateCategoryStart)
, '{{Category}}', CONVERT(nvarchar(MAX), [CategoryTitle]))
, '{{RuleCount}}', CONVERT(nvarchar(MAX),COUNT(*)))
, '{{CategoryId}}', CONVERT(nvarchar(MAX), [Category]))
WHEN [Severity] IS NOT NULL AND [RuleId] IS NULL THEN -- Severity header
REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT(nvarchar(MAX), @HTMLTemplateSeverityStart)
, '{{Severity}}', CONVERT(nvarchar(MAX), [Severity]))
, '{{RuleCount}}', CONVERT(nvarchar(MAX),COUNT(*)))
, '{{SeverityNum}}', CONVERT(nvarchar(MAX), [Severity_Num]))
, '{{CategoryId}}', CONVERT(nvarchar(MAX), [Category]))
ELSE -- Data Row
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT(nvarchar(MAX), @HTMLTemplatePerRow)
,'{{RuleId}}', CONVERT(nvarchar(MAX), [RuleId]))
,'{{Title}}', CONVERT(nvarchar(MAX), [Title]))
,'{{Description}}', CONVERT(nvarchar(MAX), [Description]))
,'{{Rationale}}', CONVERT(nvarchar(MAX), [Rationale]))
,'{{Query}}',ISNULL([Query], CONVERT(nvarchar(MAX), N'')))
,'{{RemediationScript}}', CONVERT(nvarchar(MAX), ISNULL([RemediationScript],N'')))
,'{{RemediationDescription}}', [RemediationDescription])
,'{{Severity}}', CONVERT(nvarchar(MAX), [Severity]))
,'{{PlatformLabels}}', CONVERT(nvarchar(MAX), [PlatformLabels]))
END
+ CASE WHEN [RuleId_Desc] = 1 THEN @HTMLTemplateSeverityEnd ELSE CONVERT(nvarchar(MAX), N'') END
+ CASE WHEN Everything_Desc = 1 THEN @HTMLFooter ELSE CONVERT(nvarchar(MAX), 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(MAX),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(MAX),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