info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



Create a Microsoft SQL Server Audit Report using Powershell
by BF (Principal Consultant; Architecture; Engineering)
2017-07-31









Powershell Code:


$dt = Get-Date

$dtformatted = Get-Date -Format g
$dtformatted = $dtformatted.Replace(':','')
$dtformatted = $dtformatted.Replace('/','-')
$dtformatted = $dtformatted.Replace(' ','-')

$ServerName = "NYC01;

$Directory = "G:\Reports\"

$Filename = "MSSQLAuditReport-$ServerName-$dtformatted.htm"

$To = "xyz@nycxyz.com"

$From = "reports@nycxyz.com"

$SMTP = "mail.nycxyz.com"

$filepath="$Directory$Filename"

$IsBodyEmail = "0" #0 = Exclude Email Body and only Attachment Sent

cls

Function Get-MSSQLAuditReport
{

#Test Directory Exists
If(Test-Path -Path $Directory)
{

Write-host "Success | Directory Exists"

#Test Directory and File Exists
If(Test-Path $filepath)
{

Remove-Item $filepath -Force
If($?) {Write-Host "Success | Delete Previous File"}
}

#Test Server is up
If(Test-Connection -ComputerName $ServerName -Count 5 -Quiet)
{

Write-host "Success | Test-Connection to Server"

#Start Report Creation
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$ServerObject = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $ServerName

$styles = "<style>TH{text-align:left;text-decoration: underline;padding:3px;} TD{padding:3px;} BODY{background-color:silver;} TABLE{background-color:white;font-size:12px;font-family:Segoe UI; box-shadow: 3px 3px 3px #888; border: thin solid grey;};</style>"

"[MICROSOFT SQL SERVER - AUDIT REPORT - $ServerName - $dt]" | Out-File -append $filepath

$ServerObject.Information.Properties |Select Name, Value | ConvertTo-HTML -head $styles -body “</br><u><H1>Server:</H1></u></br>” | Out-File -append $filepath
If ($?) {Write-host "Success | Microsoft.SqlServer.Management.Smo.Server.Information.Properties"} Else {Write-host "Fail | Information.Properties"}

$ServerObject.Settings.Properties |Select Name, Value | ConvertTo-HTML -head $styles -body “<H1>Server Settings:</H1>” | Out-File -append $filepath
If ($?) {Write-host "Success | Microsoft.SqlServer.Management.Smo.Server.Settings.Properties"} Else {Write-host "Fail | Settings.Properties"}

$ServerObject.Configuration.Properties |Select DisplayName, Description, Minimum, Maximum, RunValue, ConfigValue | ConvertTo-HTML -head $styles -body “<H1>Configuration:</H1>” | Out-File -append $filepath
If ($?) {Write-host "Success | Microsoft.SqlServer.Management.Smo.Server.Configuration.Properties"} Else {Write-host "Fail | Configuration.Properties"}

$ServerObject.Databases | Select Name, ActiveConnections, Size, LastBackupDate, PrimaryFilePath | ConvertTo-HTML -head $styles -body “<H1>Databases:</H1>” | Out-File -append $filepath
If ($?) {Write-host "Success | Microsoft.SqlServer.Management.Smo.Server.Databases"} Else {Write-host "Fail | Database"}

$ServerObject.Logins | Select Name, LoginType, HasAccess, DenyWindowsLogin, PassworPolicyEnforced, PasswordExpirationEnabled, MustChangePassword, IsLocked, IsDisabled, IsPasswordExpired | ConvertTo-HTML -head $a -body “<H1>Logins:</H1>” | Out-File -append $filepath
If ($?) {Write-host "Success | Microsoft.SqlServer.Management.Smo.Server.Logins"} Else {Write-host "Fail | Logins"}

Write-host "Success | Report Created"


Function SendEmail
{

Param($from,$to,$subject,$htmlFileName,$attachments,$smtpServerName)

$receipients = $to

If($IsBodyEmail -eq "1")
{

$body = Get-Content $htmlFileName

}

$body = New-Object System.Net.Mail.MailMessage $from, $receipients, $subject, $body

$body.Attachments.Add($attachments)

$body.isBodyhtml = $true

$smtpServer = $smtpServerName

$smtp = new-object Net.Mail.SmtpClient($smtpServer)

$smtp.Send($body)
}


SendEmail -from $From -to $To -subject “Microsoft SQL Server - Audit Report – $ServerName - $dt” -htmlfilename $filepath -attachments $filepath -smtpservername $SMTP
if($?) {Write-host "Success | Send Email"} Else {Write-host "Fail | Send Email"
}

}

}

}

Return

#Get-Help Get-Member –full;
#$ServerObject | gm | more;
#$ServerObject.Information
#$ServerObject.Information | Select Parent, Product, Edition, VersionString | FT -auto;
#$ServerObject.Information.Properties
#$ServerObject.UserOptions.Properties
#$ServerObject.Configuration.Properties
#$ServerObject.Databases | Select Name, PrimaryFilePath, ActiveConnections, LastBackupDate, Size, UserAccess, Status, SpaceAvailable
#$ServerObject.Logins Name, PassworPolicyEnforced, PasswordExpirationEnabled, LoginType, MustChangePassword, IsLocked, IsDisabled, IsPasswordExpired, HasAccess, DenyWindowsLogin
#cls



Image 1: Powershell Output:




Image 2: Report Emailed (Attachment; HTML File):





Resources:

microsoft.sqlserver.management.smo.server