Build SQL Server audit reports with Powershell

Introduction

When you are tasked with conducting an audit at a client’s site or on the environment you manage, you might find it necessary to automate the audit process in order to save time. However, it can be challenging to extract information from either the PowerShell console or a text file.

Here, the idea would be to propose a solution that could generate audit reports to quickly identify how the audited environment is configured. We will attempt to propose a solution that will automate the generation of audit reports.

In broad terms, here are what we will implement:

• Define the environment we wish to audit. We centralize the configuration of our environments and all the parameters we will use.

• Define the checks or tests we would like to perform.

• Execute these checks. In our case, we will mostly use dbatools to perform the checks. However, it’s possible that you may not be able to use dbatools in your environment for security reasons, for example. In that case, you could replace calls to dbatools with calls to PowerShell functions.

• Produce an audit report.

Technologies

Here are the technologies we will use in our project :

• SQL Server
• Powershell
• Windows Server
• JSON

Dependencies

In our example, we use the dbatools module in oder to get some information related to the environment(s) we audit.

Reference : https://dbatools.io/

Global architecture

Here is how our solution will work :

• We store the configuration of our environment in a JSON file. This avoids storing certain parameters in the PowerShell code.
• We import our configuration (our JSON file). We centralize in one file all the checks, tests to be performed. The configuration stored in the JSON file is passed to the tests.
• We execute all the tests to be performed, then we generate an HTML file on which we apply a CSS stylesheet.
• We can then send this information by email (for example).

Here are some details about the structure of our project :

What does it look like ?

How does it work ?

Implementation

Code – A basic implementation :

dbi-audit-config.json :

[
	{
		"Name": "app.computername",
		"Value": [
					  "WS-2022-A",
                      "ad"
				  ],
		"Description": "List of Windows Servers that Windows-based tests will run against"
	},
	{
		"Name": "app.sqlinstance",
		"Value": [
					  "WS-2022-A\\INSTANCEA,1433"
				  ],
		"Description": "List of SQL Server instances that SQL-based tests will run against"
	},
	{
		"Name": "dbi-app.checkcomputersinformation.enabled",
		"Value": "True",
		"Description": "Get some information on OS level"
	},
	{
		"Name": "dbi-app.checkoperatingsystem.enabled",
		"Value": "True",
		"Description": "Perform some OS checks"
	},
	{
		"Name": "dbi-app.checksqlsystemconfiguration.enabled",
		"Value": "True",
		"Description": "Check some SQL Server system settings"
	}
]

dbi-audit-controls-library.ps1 :

#We import our configuration
$AuditConfig = [PSCustomObject](Get-Content .\dbi-audit-config.json | Out-String | ConvertFrom-Json)

#We retrieve the values contained in our json file. Each value is stored in a variable
$Computers = $AuditConfig | Where-Object { $_.Name -eq 'dbi-app.computername' } | Select-Object Value
$SQLInstances = $AuditConfig | Where-Object { $_.Name -eq 'dbi-app.sqlinstance' } | Select-Object Value
$UnitFileSize = ($AuditConfig | Where-Object { $_.Name -eq 'app.unitfilesize' } | Select-Object Value).Value

#Our configuration file allow to enable or disable some checks. We also retrieve those values.
$EnableCheckComputersInformation = ($AuditConfig | Where-Object { $_.Name -eq 'dbi-app.checkcomputersinformation.enabled' } | Select-Object Value).Value
$EnableCheckOperatingSystem = ($AuditConfig | Where-Object { $_.Name -eq 'dbi-app.checkoperatingsystem.enabled' } | Select-Object Value).Value
$EnableCheckSQLSystemConfiguration = ($AuditConfig | Where-Object { $_.Name -eq 'dbi-app.checksqlsystemconfiguration.enabled' } | Select-Object Value).Value

#Used to invoke command queries
$ComputersList = @()
$ComputersList += $Computers | Foreach-Object {
   $_.Value 
}


function Get-ComputersInformation()
{
    if ($EnableCheckComputersInformation -eq $True)
    { 
        $ComputersInformationList = @()
        $ComputersInformationList += $Computers | Foreach-Object { 
                                                    Get-DbaComputerSystem -ComputerName $_.Value | 
                                                        Select-Object ComputerName, Domain, NumberLogicalProcessors, 
                                                        NumberProcessors, TotalPhysicalMemory
        }
    }
        
    return $ComputersInformationList
}


function Get-OperatingSystem()
{
    if ($EnableCheckOperatingSystem -eq $True)
    {    
        $OperatingSystemList = @()
        $OperatingSystemList += $Computers | Foreach-Object {
                                                Get-DbaOperatingSystem -ComputerName $_.Value | 
                                                    Select-Object ComputerName, Architecture, OSVersion, ActivePowerPlan
        }
    }

    return $OperatingSystemList
}


function Get-OSDiskSpace()
{
    if ($EnableCheckOSDiskSpace -eq $True)
    {   
        $OSDiskSpaceList = @()
        $OSDiskSpaceList += $Computers | Foreach-Object {
                                            Get-DbaDiskSpace -ComputerName $_.Value -Unit $UnitFileSize | 
                                                Select-Object ComputerName, Name, Label, Capacity, Free, PercentFree, BlockSize, FileSystem
        }
    }
    
    return $OSDiskSpaceList
}


function Get-SQLServerServiceState()
{
    if ($EnableCheckSQLServerServiceState -eq $True)
    {
        $SQLServerServiceStateList = @()
        $SQLServerServiceStateList += $Computers | Foreach-Object {
                                            Get-DbaService -ComputerName $_.Value | 
                                                Select-Object ComputerName, ServiceName, ServiceType, DisplayName, StartName, State, StartMode
        }
    }

    return $SQLServerServiceStateList
}

dbi-audit-main.ps1 :

. .\dbi-audit-controls-library.ps1

$HTMLHeader = '<!DOCTYPE html><head><meta charset="utf-8"/><link href="..\dbi-audit-style.css" rel="stylesheet"></head><body><div id="sidebar"></div>'
$HTMLFooter = "</body></html>"

$HeaderMapping = @{
    ComputersInformation = "Computers Information"
    OperatingSystem = "Operation System Information"
    OSDiskSpace = "Disk Space Information"
    SQLServerServiceState = "SQL Server Services State"
}

$Checks = [ordered] @{}
$Checks['ComputersInformation'] = Get-ComputersInformation
$Checks['OperatingSystem'] = Get-OperatingSystem
$Checks['OSDiskSpace'] = Get-OSDiskSpace
$Checks['SQLServerServiceState'] = Get-SQLServerServiceState

$Output = ''

$Checks.Keys | ForEach-Object {
    
    $TitleSection = $HeaderMapping[$_]
    $Output += $Checks[$_] | ConvertTo-HTML -Pre "<div class=`"$_`">" -Post "</div>" -Property * -Fragment
    $Output = $Output -replace "(?s)<colgroup>.*?</colgroup>", "<h2>$TitleSection</h2>"  
    $Output = $Output -Replace '<td>Stopped','<td class="control_failed">Stopped'
    $Output = $Output -Replace '<td>Running','<td class="control_passed">Running'
    $Output = $Output -Replace '<td>Balanced','<td class="control_failed">Balanced'
    $Output = $Output -Replace '<td>High performance','<td class="control_passed">High performance'
}

$HTML = $HTMLHeader + $Output + $HTMLFooter
$HTML | Out-File ".\html_output\dbi-audit.html" -Encoding utf8

dbi-audit-style.css :

th,
td {
  border: 1px solid rgb(160 160 160);
  padding: 8px 10px;
}

th[scope='col'] {
  background-color: #505050;
  color: #fff;
}

th[scope='row'] {
  background-color: #d6ecd4;
}

td {
  text-align: center;
}

tr:nth-of-type(even) {
  background-color: #eee;
}

table {
  border-collapse: collapse;
  border: 2px solid rgb(140 140 140);
  font-family: sans-serif;
  font-size: 0.8rem;
  letter-spacing: 1px;
  -moz-box-shadow: 0 0 3px #888;
  -webkit-box-shadow: 0 0 5px#888;
  box-shadow: rgba(50, 50, 93, 0.25) 0px 2px 5px -1px, rgba(0, 0, 0, 0.3) 0px 1px 3px -1px;
}

tr .control_failed {
	color: #F7F7F7;
	background-color: #BE4025;
}

tr .control_passed {
	color: #F7F7F7;
	background-color: #16BA00;
}

caption {
  caption-side: bottom;
  padding: 10px;
}

html {
    padding:0;
    margin:0;
}

body {
	background-color:#fbfbfb;
    margin:0;
    padding: 0;
}

h2 {
	font-family: "Gill Sans", sans-serif;
    text-shadow: 2px 2px 2px #c9c9c9;	
}

#sidebar {
    float:left;
    background-color:#ffa733;
    width:4%;
    margin-right:20px; 
    height:100vh;
    box-shadow: 5px 10px #154c79;
}

#sidebar .dbi_audit {
    display: inline-block;
    transform: rotate(-90deg);
    transform-origin: top left;
}

How does it run ?

.\dbi-audit-main.ps1

Output (what does it really look like ?) :

Nice to have

Let’s say I would like to add new checks. How would I proceed ?

• Edit the dbi-audit-config.json and add your check
• Edit the dbi-audit-checks.ps1
• Retrieve the information related to your check

$EnableCheckSQLDatabaseQueryStore = ($AuditConfig | Where-Object { $_.Name -eq 'app.checksqldatabasequerystore.enabled' } | Select-Object Value).Value

• Add another function

function Get-SQLDatabaseQueryStore()
{
    if ($EnableCheckSQLDatabaseQueryStore -eq $True)
    {
        $SQLDatabaseQueryStoreList = @()
        $SQLDatabaseQueryStoreList += $SQLInstances | Foreach-Object {
                                                        Test-DbaDbQueryStore -SqlInstance $_.Value | 
                                                            Where-Object { $_.Name -eq 'ActualState' -and $_.Database -ne 'master' -and $_.Database -ne 'msdb' -and $_.Database -ne 'model' -and $_.Database -ne 'tempdb' } | 
                                                                Select-Object ComputerName, SQLInstance, Database, Value
        }
    }

    return $SQLDatabaseQueryStoreList
}

• Call it in the dbi-audit-main.ps1 script

$HeaderMapping = @{
    ComputersInformation = "Computers Information"
    OperatingSystem = "Operation System Information"
    OSDiskSpace = "Disk Space Information"
    SQLServerServiceState = "SQL Server Services State"
    SQLDatabaseQueryStore = "Query Store"
}

$Checks = [ordered] @{}
$Checks['ComputersInformation'] = Get-ComputersInformation
$Checks['OperatingSystem'] = Get-OperatingSystem
$Checks['OSDiskSpace'] = Get-OSDiskSpace
$Checks['SQLServerServiceState'] = Get-SQLServerServiceState
$Checks['SQLDatabaseQueryStore'] = Get-SQLDatabaseQueryStore

End result :

What about sending the report through email ?

• We could add a function that send an email with an attachment.
  • Edit the dbi-audit-controls-library file
    • Add a function Send-EmailWithAuditReport
    • Add this piece of code to the function :

Send-MailMessage -SmtpServer mysmtpserver -From 'Sender' -To 'Recipient' -Subject 'Audit report' -Body 'Audit report' -Port 25 -Attachments $Attachments

• Edit the dbi-audit-main.ps1
• Add a call to the Send-EmailWithAuditReport function Send-EmailWithAuditReport -Attachments $Attachments

Conclusion

The main idea of this solution is to be able to use the same functions while applying a different rendering. To achieve this, you would need to change the CSS file.

This would allow having the same code to perform the following tasks:

• Audit
• Health check
• …

Thank you, Amine Haloui.