dbi Blog

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
XML, XSLT and 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 (applying an XSLT style sheet) from the collected information.
We can then send this information by email (for example).

Here are some details about the structure of our project :

Folder	Type	File	Description	Details
dbi-audit	PS1 file	dbi-audit-config.json	Contains some pieces of information related to the environment you would like to audit.	The file is called by the dbi-audit-checks.ps1. We import that file and parse it. E.g. if you need to add new servers to audit you can edit that file and run a new audit.
dbi-audit	PS1 file	dbi-audit-checks.ps1	Store the checks to perform on the environment(s).	That file acts as a “library”, it contains all the checks to perform. It centralizes all the functions.
dbi-audit	PS1 file	dbi-audit-run.ps1	Run the checks to perform Transform the output in an html file.	It’s the most import file : It runs the checks to perform. It builds the html report and apply a stylesheet It can also send by email the related report
dbi-audit	XSL file	dbi-audit-stylesheet.xsl	Contains the stylesheet to apply to the HTML report.	It’s where you will define what you HTML report will look like.
html_output	Folder	–	Will contain the report audit produced.	It stores HTML reports.

What does it look like ?

How does it work ?

Implementation

Code – A basic implementation :

dbi-audit-config.json :

[
	{
		"Name": "dbi-app.computername",
		"Value": [
					  "TEST-SQL"
				  ],
		"Description": "Windows servers list to audit"
	},
	{
		"Name": "dbi-app.sqlinstance",
		"Value": [
					  "TEST-SQL\\INSTANCEA"
				  ],
		"Description": "SQL Server list to audit"
	},
	{
		"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-checks.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 
}


<#
    Get Computer Information
#>

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


<#
    Get OS Information
#>

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

    return $OperatingSystemList
}


<#
    Get SQL Server/OS Configuration : IFI, LockPagesInMemory
#>

function CheckSQLSystemConfiguration()
{
    if ($EnableCheckSQLSystemConfiguration)
    {   
        $SQLSystemConfigurationList = @()
        
        $SQLSystemConfigurationList += $Computers | Foreach-Object {
                                                        $ComputerName = $_.Value
                                                        Get-DbaPrivilege -ComputerName $ComputerName | 
                                                            Where-Object { $_.User -like '*MSSQL*' } |
                                                                Select-Object ComputerName, User, InstantFileInitialization, LockPagesInMemory
                                                        $ComputerName = $Null
        }
    }

    return $SQLSystemConfigurationList
}

dbi-audit-run.ps1 :

#Our configuration file will accept a parameter. It's the stylesheet to apply to our HTML report
Param(
  [parameter(mandatory=$true)][string]$XSLStyleSheet
)

# We import the checks to run
. .\dbi-audit-checks.ps1


#Setup the XML configuration
$ScriptLocation = Get-Location
$XslOutputPath = "$($ScriptLocation.Path)\$($XSLStyleSheet)"
$FileSavePath = "$($ScriptLocation.Path)\html_output"
[System.XML.XMLDocument]$XmlOutput = New-Object System.XML.XMLDocument
[System.XML.XMLElement]$XmlRoot = $XmlOutput.CreateElement("DbiAuditReport")
$Null = $XmlOutput.appendChild($XmlRoot)

#We run the checks. Instead of manually call all the checks, we store them in array
#We browse that array and we execute the related function
#Each function result is used and append to the XML structure we build
$FunctionsName = @("CheckComputersInformation", "CheckOperatingSystem", "CheckSQLSystemConfiguration")

$FunctionsStore = [ordered] @{}
$FunctionsStore['ComputersInformation'] = CheckComputersInformation
$FunctionsStore['OperatingSystem'] = CheckOperatingSystem
$FunctionsStore['SQLSystemConfiguration'] = CheckSQLSystemConfiguration

$i = 0
$FunctionsStore.Keys | ForEach-Object {
    
    [System.XML.XMLElement]$xmlSQLChecks = $XmlRoot.appendChild($XmlOutput.CreateElement($FunctionsName[$i]))
    $Results = $FunctionsStore[$_]

    foreach ($Data in $Results)
    {
        $xmlServicesEntry = $xmlSQLChecks.appendChild($XmlOutput.CreateElement($_))

        foreach ($DataProperties in $Data.PSObject.Properties)
        {
            $xmlServicesEntry.SetAttribute($DataProperties.Name, $DataProperties.Value)
        }
    }

    $i++
}

#We create our XML file
$XmlRoot.SetAttribute("EndTime", (Get-Date -Format yyyy-MM-dd_h-mm))
$ReportXMLFileName = [string]::Format("{0}\{1}_DbiAuditReport.xml", $FileSavePath, (Get-Date).tostring("MM-dd-yyyy_HH-mm-ss"))
$ReportHTMLFileName = [string]::Format("{0}\{1}_DbiAuditReport.html", $FileSavePath, (Get-Date).tostring("MM-dd-yyyy_HH-mm-ss"))
$XmlOutput.Save($ReportXMLFileName)

#We apply our XSLT stylesheet
[System.Xml.Xsl.XslCompiledTransform]$XSLT = New-Object System.Xml.Xsl.XslCompiledTransform
$XSLT.Load($XslOutputPath)

#We build our HTML file
$XSLT.Transform($ReportXMLFileName, $ReportHTMLFileName)

dbi-audit-stylesheet.xsl :

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
	<xsl:template match="DbiAuditReport">
		<xsl:text disable-output-escaping='yes'>&lt;!DOCTYPE html></xsl:text>
		<html>

		<head>
			<meta http-equiv="X-UA-Compatible" content="IE=9" />
			<style>
			body {
				font-family: Verdana, sans-serif;
				font-size: 15px;
				line-height: 1.5M background-color: #FCFCFC;
			}
			
			h1 {
				color: #EB7D00;
				font-size: 30px;
			}
			
			h2 {
				color: #004F9C;
				margin-left: 2.5%;
			}
			
			h3 {
				font-size: 24px;
			}
			
			table {
				width: 95%;
				margin: auto;
				border: solid 2px #D1D1D1;
				border-collapse: collapse;
				border-spacing: 0;
				margin-bottom: 1%;
			}
			
			table tr th {
				background-color: #D1D1D1;
				border: solid 1px #D1D1D1;
				color: #004F9C;
				padding: 10px;
				text-align: left;
				text-shadow: 1px 1px 1px #fff;
			}
			
			table td {
				border: solid 1px #DDEEEE;
				color: #004F9C;
				padding: 10px;
				text-shadow: 1px 1px 1px #fff;
			}
			
			table tr:nth-child(even) {
				background: #F7F7F7
			}
			
			table tr:nth-child(odd) {
				background: #FFFFFF
			}
			
			table tr .check_failed {
				color: #F7F7F7;
				background-color: #FC1703;
			}
			
			table tr .check_passed {
				color: #F7F7F7;
				background-color: #16BA00;
			}
			
			table tr .check_in_between {
				color: #F7F7F7;
				background-color: #F5B22C;
			}
			</style>
		</head>

		<body>
			<table>
				<tr>
					<td>
						<h1>Audit report</h1> </td>
				</tr>
			</table>
			<caption>
				<xsl:apply-templates select="CheckComputersInformation" />
				<xsl:apply-templates select="CheckOperatingSystem" />
				<xsl:apply-templates select="CheckSQLSystemConfiguration" /> </caption>
		</body>

		</html>
	</xsl:template>
	<xsl:template match="CheckComputersInformation">
		<h2>Computer Information</h2>
		<table>
			<tr>
				<th>Computer Name</th>
				<th>Domain</th>
				<th>Number Logical Processors</th>
				<th>Number Processors</th>
				<th>Total Physical Memory</th>
			</tr>
			<tbody>
				<xsl:apply-templates select="ComputersInformation" /> </tbody>
		</table>
	</xsl:template>
	<xsl:template match="ComputersInformation">
		<tr>
			<td>
				<xsl:value-of select="@ComputerName" />
			</td>
			<td>
				<xsl:value-of select="@Domain" />
			</td>
			<td>
				<xsl:value-of select="@NumberLogicalProcessors" />
			</td>
			<td>
				<xsl:value-of select="@NumberProcessors" />
			</td>
			<td>
				<xsl:value-of select="@TotalPhysicalMemory" />
			</td>
		</tr>
	</xsl:template>
	<xsl:template match="CheckOperatingSystem">
		<h2>Operating System</h2>
		<table>
			<tr>
				<th>Computer Name</th>
				<th>Architecture</th>
				<th>OS Version</th>
				<th>Power Plan</th>
			</tr>
			<tbody>
				<xsl:apply-templates select="OperatingSystem" /> </tbody>
		</table>
	</xsl:template>
	<xsl:template match="OperatingSystem">
		<tr>
			<td>
				<xsl:value-of select="@ComputerName" />
			</td>
			<td>
				<xsl:value-of select="@Architecture" />
			</td>
			<td>
				<xsl:value-of select="@OSVersion" />
			</td>
			<xsl:choose>
				<xsl:when test="@ActivePowerPlan = 'High performance'">
					<td class="check_passed">
						<xsl:value-of select="@ActivePowerPlan" />
					</td>
				</xsl:when>
				<xsl:otherwise>
					<td class="check_failed">
						<xsl:value-of select="@ActivePowerPlan" />
					</td>
				</xsl:otherwise>
			</xsl:choose>
		</tr>
	</xsl:template>
	<xsl:template match="CheckSQLSystemConfiguration">
		<h2>SQL System Configuration</h2>
		<table>
			<tr>
				<th>Computer Name</th>
				<th>User</th>
				<th>Instant File Initialization</th>
				<th>Lock Pages In Memory</th>
			</tr>
			<tbody>
				<xsl:apply-templates select="SQLSystemConfiguration" /> </tbody>
		</table>
	</xsl:template>
	<xsl:template match="SQLSystemConfiguration">
		<tr>
			<td>
				<xsl:value-of select="@ComputerName" />
			</td>
			<td>
				<xsl:value-of select="@User" />
			</td>
			<xsl:choose>
				<xsl:when test="@InstantFileInitialization = 'True'">
					<td class="check_passed">
						<xsl:value-of select="@InstantFileInitialization" />
					</td>
				</xsl:when>
				<xsl:otherwise>
					<td class="check_failed">
						<xsl:value-of select="@InstantFileInitialization" />
					</td>
				</xsl:otherwise>
			</xsl:choose>
			<xsl:choose>
				<xsl:when test="@LockPagesInMemory = 'True'">
					<td class="check_passed">
						<xsl:value-of select="@LockPagesInMemory" />
					</td>
				</xsl:when>
				<xsl:otherwise>
					<td class="check_in_between">
						<xsl:value-of select="@LockPagesInMemory" />
					</td>
				</xsl:otherwise>
			</xsl:choose>
		</tr>
	</xsl:template>
</xsl:stylesheet>

How does it run ?

.\dbi-audit-run.ps1 -XSLStyleSheet .\dbi-audit-stylesheet.xsl

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-checks.ps1
Retrieve the information related to your check

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

Add another function

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

    return $SQLServerServiceStateList
}

Call it in the dbi-audit-run script

$FunctionsName = @("CheckComputersInformation", "CheckOperatingSystem", "CheckSQLSystemConfiguration", "CheckSQLServerServiceState")

$FunctionsStore = [ordered] @{}
$FunctionsStore['ComputersInformation'] = CheckComputersInformation
$FunctionsStore['OperatingSystem'] = CheckOperatingSystem
$FunctionsStore['SQLSystemConfiguration'] = CheckSQLSystemConfiguration
$FunctionsStore['SQLServerServiceState'] = CheckSQLServerServiceState

Edit the dbi-audit-stylesheet.xsl to include how you would like to display the information you collected (it’s the most consuming time part because it’s not automated. I did not find a way to automate it yet)

<body>
		    <table>
			    <tr>
				    <td>
                        <h1>Audit report</h1>
                    </td>
			    </tr>
            </table>
            <caption>
                <xsl:apply-templates select="CheckComputersInformation"/>
                <xsl:apply-templates select="CheckOperatingSystem"/>
                <xsl:apply-templates select="CheckSQLSystemConfiguration"/>
                <xsl:apply-templates select="CheckSQLServerServiceState"/>
            </caption>
        </body>

...

 <xsl:template match="CheckSQLServerServiceState">
        <h2>SQL Server Services State</h2>
	    <table>
			<tr>
				<th>Computer Name</th>
				<th>Service Name</th>
				<th>Service Type</th>
                <th>Display Name</th>
                <th>Start Name</th>
                <th>State</th>
                <th>Start Mode</th>
			</tr>
		    <tbody>
			    <xsl:apply-templates select="SQLServerServiceState"/>
		    </tbody>
	    </table>
    </xsl:template>
    <xsl:template match="SQLServerServiceState">
        <tr>
            <td><xsl:value-of select="@ComputerName"/></td>
            <td><xsl:value-of select="@ServiceName"/></td>
            <td><xsl:value-of select="@ServiceType"/></td>
            <td><xsl:value-of select="@DisplayName"/></td>
            <td><xsl:value-of select="@StartName"/></td>
            <xsl:choose>
                <xsl:when test="(@State = 'Stopped') and (@ServiceType = 'Engine')">
                    <td class="check_failed"><xsl:value-of select="@State"/></td>
                </xsl:when>
                <xsl:when test="(@State = 'Stopped') and (@ServiceType = 'Agent')">
                    <td class="check_failed"><xsl:value-of select="@State"/></td>
                </xsl:when>
                <xsl:otherwise>
                    <td class="check_passed"><xsl:value-of select="@State"/></td>
                </xsl:otherwise>
            </xsl:choose>
            <td><xsl:value-of select="@StartMode"/></td>
        </tr>
    </xsl:template>

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-checks 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-run.ps1
- Add a call to the Send-EmailWithAuditReport function :
  - Send-EmailWithAuditReport -Attachments $Attachments

$ReportHTMLFileName = [string]::Format("{0}\{1}_DbiAuditReport.html", $FileSavePath, (Get-Date).tostring("MM-dd-yyyy_HH-mm-ss"))

...

SendEmailsWithReport -Attachments $ReportHTMLFileName

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 XSL stylesheet or create another XSL stylesheet and then provide to the dbi-audit-run.ps1 file the stylesheet to apply.

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

Audit
Health check
…

Post Views: 202

Build SQL Server audit reports with Powershell

Introduction

Technologies

Dependencies

Global architecture

Implementation

Nice to have

Conclusion

aminehaloui

Leave a Reply:

Related blog articles

Introduction

Technologies

Dependencies

Global architecture

Implementation

Nice to have

Conclusion

aminehaloui

Leave a Reply:

Related blog articles

SQL-Server: What are VLF’s and why should I care about them?

SQL Server Security: Ensure that SQL Server service accounts are not a member of the Windows local Administrator group

SQL Server: Manage large data ranges using partitioning

Attach database error (SMB Fileshare)