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
|
|
|
|
|
|