Set-StrictMode -Version 2.0 try { Add-Type -AssemblyName System.IO.Compression.FileSystem -ErrorAction Stop } catch { Write-Warning "Could not add the zip .net type. Please zip the files manually" } #Region Functions function Zip-Directory { [CmdletBinding()] Param ( [Parameter(Mandatory = $True)] [string]$DestinationFileName, [Parameter(Mandatory = $True)] [string]$SourceDirectory, [Parameter(Mandatory = $False)] [string]$CompressionLevel = "Optimal", [Parameter(Mandatory = $False)] [switch]$IncludeParentDir ) $CompressionLevel = [System.IO.Compression.CompressionLevel]::$CompressionLevel [System.IO.Compression.ZipFile]::CreateFromDirectory($SourceDirectory, $DestinationFileName, $CompressionLevel, $IncludeParentDir) } function Invoke-Sqlcmd2 { [CmdletBinding(DefaultParameterSetName = 'Query')] [OutputType([System.Management.Automation.PSCustomObject], [System.Data.DataRow], [System.Data.DataTable], [System.Data.DataTableCollection], [System.Data.DataSet])] param ( [Parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, ValueFromRemainingArguments = $false, HelpMessage = 'SQL Server Instance required...')] [Alias('Instance', 'Instances', 'ComputerName', 'Server', 'Servers')] [ValidateNotNullOrEmpty()] [string[]]$ServerInstance, [Parameter(Position = 1, Mandatory = $false, ValueFromPipelineByPropertyName = $true, ValueFromRemainingArguments = $false)] [string]$Database, [Parameter(Position = 2, Mandatory = $true, ValueFromPipelineByPropertyName = $true, ValueFromRemainingArguments = $false, ParameterSetName = 'Query')] [string]$Query, [Parameter(Position = 2, Mandatory = $true, ValueFromPipelineByPropertyName = $true, ValueFromRemainingArguments = $false, ParameterSetName = "File")] [ValidateScript({ Test-Path $_ })] [string]$InputFile, [Parameter(Position = 3, Mandatory = $false, ValueFromPipelineByPropertyName = $true, ValueFromRemainingArguments = $false, ParameterSetName = "Query")] [Parameter(Position = 3, Mandatory = $false, ValueFromPipelineByPropertyName = $true, ValueFromRemainingArguments = $false, ParameterSetName = "File")] [System.Management.Automation.PSCredential]$Credential, [Parameter(Position = 4, Mandatory = $false, ValueFromPipelineByPropertyName = $true, ValueFromRemainingArguments = $false)] [Int32]$QueryTimeout = 600, [Parameter(Position = 5, Mandatory = $false, ValueFromPipelineByPropertyName = $true, ValueFromRemainingArguments = $false)] [Int32]$ConnectionTimeout = 15, [Parameter(Position = 6, Mandatory = $false, ValueFromPipelineByPropertyName = $true, ValueFromRemainingArguments = $false)] [ValidateSet("DataSet", "DataTable", "DataRow", "PSObject", "SingleValue")] [string]$As = "DataRow", [Parameter(Position = 7, Mandatory = $false, ValueFromPipelineByPropertyName = $true, ValueFromRemainingArguments = $false)] [System.Collections.IDictionary]$SqlParameters, [Parameter(Position = 8, Mandatory = $false)] [switch]$AppendServerInstance ) Begin { if ($InputFile) { $filePath = $(Resolve-Path $InputFile).path $Query = [System.IO.File]::ReadAllText("$filePath") } Write-Verbose "Running Invoke-Sqlcmd2 with ParameterSet '$($PSCmdlet.ParameterSetName)'. Performing query '$Query'" If ($As -eq "PSObject") { #This code scrubs DBNulls. Props to Dave Wyatt $cSharp = @' using System; using System.Data; using System.Management.Automation; public class DBNullScrubber { public static PSObject DataRowToPSObject(DataRow row) { PSObject psObject = new PSObject(); if (row != null && (row.RowState & DataRowState.Detached) != DataRowState.Detached) { foreach (DataColumn column in row.Table.Columns) { Object value = null; if (!row.IsNull(column)) { value = row[column]; } psObject.Properties.Add(new PSNoteProperty(column.ColumnName, value)); } } return psObject; } } '@ Try { Add-Type -TypeDefinition $cSharp -ReferencedAssemblies 'System.Data', 'System.Xml' -ErrorAction stop } Catch { If (-not $_.ToString() -like "*The type name 'DBNullScrubber' already exists*") { Write-Warning "Could not load DBNullScrubber. Defaulting to DataRow output: $_" $As = "Datarow" } } } } Process { foreach ($SQLInstance in $ServerInstance) { Write-Verbose "Querying ServerInstance '$SQLInstance'" if ($Credential) { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $SQLInstance, $Database, $Credential.UserName, $Credential.GetNetworkCredential().Password, $ConnectionTimeout } else { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $SQLInstance, $Database, $ConnectionTimeout } $conn = New-Object System.Data.SqlClient.SQLConnection $conn.ConnectionString = $ConnectionString Write-Debug "ConnectionString $ConnectionString" #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller if ($PSBoundParameters.ContainsKey('Verbose')) { $conn.FireInfoMessageEventOnUserErrors = $true $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { Write-Verbose "$($_)" } $conn.add_InfoMessage($handler) } Try { $conn.Open() } Catch { Write-Error $_ continue } $cmd = New-Object system.Data.SqlClient.SqlCommand($Query, $conn) $cmd.CommandTimeout = $QueryTimeout if ($SqlParameters -ne $null) { $SqlParameters.GetEnumerator() | ForEach-Object { If ($_.Value -ne $null) { $cmd.Parameters.AddWithValue($_.Key, $_.Value) } Else { $cmd.Parameters.AddWithValue($_.Key, [DBNull]::Value) } } > $null } $ds = New-Object system.Data.DataSet $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd) [void]$da.fill($ds) $conn.Close() if ($AppendServerInstance) { #Basics from Chad Miller $Column = new-object Data.DataColumn $Column.ColumnName = "ServerInstance" $ds.Tables[0].Columns.Add($Column) Foreach ($row in $ds.Tables[0]) { $row.ServerInstance = $SQLInstance } } switch ($As) { 'DataSet' { $ds } 'DataTable' { $ds.Tables } 'DataRow' { $ds.Tables[0] } 'PSObject' { #Scrub DBNulls - Provides convenient results you can use comparisons with #Introduces overhead (e.g. ~2000 rows w/ ~80 columns went from .15 Seconds to .65 Seconds - depending on your data could be much more!) foreach ($row in $ds.Tables[0].Rows) { [DBNullScrubber]::DataRowToPSObject($row) } } 'SingleValue' { $ds.Tables[0] | Select-Object -ExpandProperty $ds.Tables[0].Columns[0].ColumnName } } } } } #Invoke-Sqlcmd2 Function Remove-Comma { param ([parameter(position = 0, Mandatory = $true)] [string]$text) if ($text.substring(0, 1) -eq ",") { $text = $text.substring(1, $text.length - 1) } if ($text.substring($text.length - 1, 1) -eq ",") { $text = $text.substring(0, $text.length - 1) } Write-Output $text } #Region from SQLPSX function Get-SqlServer { param ( [Parameter(Position = 0, Mandatory = $true)] [string]$sqlserver, [Parameter(Position = 1, Mandatory = $false)] [string]$username, [Parameter(Position = 2, Mandatory = $false)] [string]$password, [Parameter(Position = 3, Mandatory = $false)] [string]$StatementTimeout = 0 ) #When $sqlserver passed in from the SMO Name property, brackets #are automatically inserted which then need to be removed $sqlserver = $sqlserver -replace "\[|\]" Write-Verbose "Get-SqlServer $sqlserver" if ($Username -and $Password) { $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $sqlserver, $username, $password } else { $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $sqlserver } $con.Connect() $server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $con #Some operations might take longer than the default timeout of 600 seconnds (10 minutes). Set new default to unlimited $server.ConnectionContext.StatementTimeout = $StatementTimeout $server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], "IsSystemObject") $server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], "IsSystemObject") $server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject") $server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.UserDefinedFunction], "IsSystemObject") #trap { "Check $SqlServer Name"; continue} $server.ConnectionContext.Connect() Write-Output $server } #Get-SqlServer function Get-SqlConnection { [cmdletbinding()] param ( [Parameter(Position = 0, Mandatory = $true)] [string]$sqlserver, [Parameter(Position = 1, Mandatory = $false)] [string]$username, [Parameter(Position = 2, Mandatory = $false)] [string]$password ) Write-Verbose "Get-SqlConnection $sqlserver" if ($Username -and $Password) { $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $sqlserver, $username, $password } else { $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $sqlserver } $con.Connect() Write-Output $con } #Get-ServerConnection #EndRegion from SQLPSX Function Write-Log { [CmdletBinding()] Param ( [Parameter(Mandatory = $true, Position = 1)] [ValidateNotNullOrEmpty()] [String]$path, [Parameter(Mandatory = $true, Position = 2)] [ValidateNotNullOrEmpty()] [String]$Message, [Parameter(Mandatory = $true, Position = 3)] [String]$ErrorLogFileName, [Parameter(Mandatory = $false, Position = 4)] [ValidateNotNullOrEmpty()] [Switch]$Error, [Parameter(Mandatory = $false, Position = 5)] [ValidateNotNullOrEmpty()] [Switch]$TerminatedError ) try { if (!(Test-Path $path)) { New-Item -Path $path -ItemType Directory -ErrorAction stop | Out-Null } $Fullpath = "$($path)\$($ErrorLogFileName).log" if ($PSBoundParameters.ContainsKey('Verbose')) { $Message = "[verbose]$($TimeStamp) - $($Message)" Write-Verbose $Message } if ($PSBoundParameters.ContainsKey('Error')) { $Message = "[Error]$($TimeStamp) - $($Message)" Write-Warning $Message } if ($PSBoundParameters.ContainsKey('TerminatedError')) { $Message = "[Terminated Error]$($TimeStamp) $($Message)" $Message | out-file -FilePath $Fullpath -Append Throw "$($Message)" } $Message | out-file -FilePath $Fullpath -Append } catch { Throw "Error processing the log file. Script terminated $($_.Exception.Message)" } } ` Function Get-SQLWmiInfo { param ( [Parameter(Position = 0, Mandatory = $true)] [string]$MachineName ) [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null $SQLWmi = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $MachineName Write-output $SQLWmi } #EndRegion Functions #Region Assembly if ([Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") -eq $null -or ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") -eq $null)) { Throw "SMO not avaliable" } #EndRegion Assembly #Region Variables $scriptRoot = Split-Path -Parent $MyInvocation.MyCommand.Definition $PSScriptRoot = $scriptRoot $ArrayVLF = @() #parameter $PathForOutputFiles = "$($scriptRoot)\output" $FileName = $null $ErrorActionPreference = "Stop" $VerbosePreference = "Continue" $Global:TimeStamp = Get-Date -Format "MM-dd-yyyy hh:mm:ss" $PathWriteLog = "$($scriptRoot)\Log" $ErrorLogfileName = "LOG_General_$(Get-Date -Format 'yyyyMMdd_hhmmss').log" try { if (!(Test-Path $PathWriteLog)) { New-Item -Path $PathWriteLog -ItemType Directory -ErrorAction stop | Out-Null } } catch { Write-Error "Could not create the path $($PathWriteLog). Aborting script.." Throw } if (-not ((New-Object Security.Principal.WindowsPrincipal ([Security.Principal.WindowsIdentity]::GetCurrent())).IsInRole([Security.Principal.WindowsBuiltinRole]::Administrator))) { Write-Log -Message "You must run Windows PowerShell as Administrator - Elevated Mode" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose Throw } if (!(Test-Path "$PSScriptRoot\InstanceNames.txt")) { "YourInstanceName1", "Server\YourInstanceName2" | Out-File "$PSScriptRoot\InstanceNames.txt" Write-Log "Please fill the file $PSScriptRoot\InstanceNames.txt with the name of the SQL Server Instances" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose Return } try { $instancesNames = Get-Content "$PSScriptRoot\InstanceNames.txt" -ErrorAction 'Stop' } catch { "YourInstanceName1", "Server\YourInstanceName2" | Out-File "$PSScriptRoot\InstanceNames.txt" Write-Log "Please fill the file $PSScriptRoot\InstanceNames.txt with the name of the SQL Server Instances" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose Return } if ($instancesNames -contains "YourInstanceName1" -or $instancesNames -contains "Server\YourInstanceName2") { Write-log "Please fill the file $PSScriptRoot\InstanceNames.txt with the name of the SQL Server Instances" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose Return } Write-Log -Message "Checking for Connection in the instances and if current account is SA" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose $TestedInstanceName = @() try { $instancesNames | ForEach-Object { try { $con = Get-SqlConnection -sqlserver $_ -verbose:$false -ea stop if ($con) { if ($con.FixedServerRoles -like "*SysAdmin*") { $TestedInstanceName += $_ } else { Write-log -Message "User is not SYSADM on the instance $($_). This Instace will be skipped." -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose } } else { Write-log -Message "Could not connect to the instance $($_)" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose } } catch { Write-Log -Message "Could not connect to the instance $($_)" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose } } } catch { Write-Log -Message "An error occcurred. Please run the script again" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -Error -TerminatedError } #EndRegion Variables if (!(Test-Path $pathForOutputFiles)) { try { New-Item -Path $pathForOutputFiles -ItemType Directory -ErrorAction stop | Out-Null } catch { Write-Warning "Could not create the path $($pathForOutputFiles). Aborting script.." Throw } } $InputFile = $null $TestedInstanceName | ForEach-Object { $PathForOutputFiles = "$($scriptRoot)\output" $SQLWmiInstanceName = "" $ArrayServerProperties = @() $ArrayDatabaseProperties = @() $PsObjectDatabaseProperties = $null $ArrayDatabaseFiles = @() $ArraySQLAgent = @() $ArrayOtherChecks = @() $splitedInstanceName = $_ -split "\\" $MachineName = $splitedInstanceName[0] $InstanceName = $splitedInstanceName[1] $createdpath = $true if ($InstanceName) { if (!(Test-Path "$($PathForOutputFiles)\$($MachineName)_$($InstanceName)")) { try { New-Item -ItemType directory -Path "$($PathForOutputFiles)\$($MachineName)_$($InstanceName)" | Out-Null } catch { Write-log "Could not create the $($PathForOutputFiles)\$($MachineName)_$($InstanceName) path - Skipping the instance check" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose $createdpath = $false } } $PathForOutputFiles = "$($PathForOutputFiles)\$($MachineName)_$($InstanceName)" $FileName = "$($PathForOutputFiles)\$($MachineName)_$($InstanceName)_" $FullInstanceName = "$($MachineName)\$InstanceName" $SQLWmiInstanceName = $InstanceName $ErrorLogfileName = "$($MachineName)_$($InstanceName)_BestPractices_$(Get-Date -Format 'yyyyMMdd_hhmmss')" $CSVNames = "$($MachineName)_$($InstanceName)" } else { if (!(Test-Path "$($PathForOutputFiles)\$($MachineName)")) { try { New-Item -ItemType directory -Path "$($PathForOutputFiles)\$($MachineName)" | Out-Null } catch { Write-Log "Could not create the $($PathForOutputFiles)\$($MachineName) path - Skipping the instance check" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose $createdpath = $false } } $PathForOutputFiles = "$($PathForOutputFiles)\$($MachineName)" $FileName = "$($PathForOutputFiles)\$($MachineName)_" $FullInstanceName = $MachineName $SQLWmiInstanceName = 'MSSQLSERVER' $ErrorLogfileName = "$($MachineName)_BestPractices_$(Get-Date -Format 'yyyyMMdd_hhmmss')" $CSVNames = "$($MachineName)" } If ($createdpath) { Write-log -Message "Starting gathering" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose Try { Get-ChildItem -Path "$($PathForOutputFiles)\$($CSVNames)*.csv" | Remove-Item -Force } catch { Write-Log -Error "Could not remove the old csv files from Instance $($FullInstanceName) . : Error $($_.Exception.Message) " -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -TerminatedError } try { $Server = Get-SqlServer -sqlserver $FullInstanceName Write-Log -Message "Connection Successfully instance - $($FullInstanceName)" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose } catch { Write-Log -Error "Error connecting on instance $($FullInstanceName) : Error $($_.Exception.Message)" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName } Write-Log -Message "Trying to connect on WMI" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose #Connecting on WMI Try { $SQLWmi = Get-SQLWmiInfo -MachineName $MachineName Write-Log -Message "Connection Successfully on WMI" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose } catch { Write-Log -Error "Cound not connect on WMI Properties : Error $($_.Exception.Message)" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName } Write-Log -Message "Trying to connect to instance - $($FullInstanceName) " -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose #Removing the last csv if it exists Write-Log -Message "Removing the olds csv files..." -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName #Region Instance Name New-Object psobject -property @{ 'InstanceName' = $FullInstanceName } | Select InstanceName | Export-Csv -force -NoClobber -NoTypeInformation -Path "$($FileName)InstanceName.csv" #EndRegion Instance name #Region Check 1 SQL Server version and edition: report if earlier than SQL 2008 Write-Log -Message "Checking the SQL Server Edition...." -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName $FQDN = ([System.Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties() | Select HostName, DomainName) $SQLVersionHigherThan10 = $true $SQL = " select serverproperty('productversion') version, serverproperty('productlevel') level , serverproperty('edition') edition" $ServerSQL = Invoke-Sqlcmd2 -ServerInstance $FullInstanceName -Query $SQL if ($ServerSQL.Version.Substring(0, 2) -like "*9*" -or $ServerSQL.Version.Substring(0, 2) -like "*8*") { $SQLVersionHigherThan10 = $false if ($ServerSQL.Version.Substring(0, 2) -like "*9*") { $SQLVersion = 9 } else { $SQLVersion = 8 } try { New-Object PSobject -Property @{ 'Machine Name' = $MachineName 'Net Bios Name' = "$($FQDN.HostName).$($FQDN.HostName)" 'Product Version' = "$($ServerSQL.version);Yellow" 'Product Level' = $ServerSQL.level 'Product Edition' = $ServerSQL.edition } | Select-Object @{ N = "Machine Name"; E = { $_."Machine Name" } }, @{ N = "Net Bios Name"; E = { $_."Net Bios Name" } }, @{ N = "Product Version"; E = { $_."Product Version" } }, @{ N = "Product Level"; E = { $_."Product Level" } }, @{ N = "Product Edition"; E = { $_."Product Edition" } } | Export-Csv -force -NoClobber -NoTypeInformation -Path "$($FileName)SQLEdition.csv" } catch { Write-Log -Error "Could not generate the SQLEdition.csv file. : Error $($_.Exception.Message) " -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName } } else { $SQLVersion = $Server.Version.Major Try { New-Object PSobject -Property @{ 'Machine Name' = $MachineName 'Net Bios Name' = "$($FQDN.HostName).$($FQDN.HostName)" 'Product Version' = if ([int]$Server.Version.Major -lt 10) { "$($Server.VersionString);Yellow" } else { $Server.VersionString } 'Product Level' = $Server.productLevel 'Product Edition' = $Server.edition 'Is Clustered' = $Server.IsClustered } | Select-Object @{ N = "Machine Name"; E = { $_."Machine Name" } }, @{ N = "Net Bios Name"; E = { $_."Net Bios Name" } }, @{ N = "Product Version"; E = { $_."Product Version" } }, @{ N = "Product Level"; E = { $_."Product Level" } }, @{ N = "Product Edition"; E = { $_."Product Edition" } }, @{ N = "Is Clustered"; E = { $_."Is Clustered" } } | Export-Csv -force -NoClobber -NoTypeInformation -Path "$($FileName)SQLEdition.csv" } catch { Write-Log -Error "Could not generate the SQLEdition.csv file. : Error $($_.Exception.Message) " -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName } } #EndRegion Check 1 SQL Server version and edition: report if earlier than SQL 2008 #Region Check 2 Server Protocols and Port (WMI) - TCPIP Write-Log -Message "Checking the SQL Server Protocols TCPIP...." -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName $SQLWmiProtocols = $SQLWmi.ServerInstances["$SQLWmiInstanceName"] If ($SQLWmiProtocols) { $TCPIP = @{ 'Protocol Name' = 'TCP' 'Is Enabled' = $SQLWmiProtocols.serverprotocols['Tcp'].IsEnabled 'Has Multiple IP Addresses' = $SQLWmiProtocols.serverprotocols['Tcp'].HasMultiIPAddresses 'Port SQL Server listening' = $SQLWmiProtocols.serverprotocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties[1].Value } New-Object PSOBJECT -Property $TCPIP | Select @{ N = 'Protocol Name'; E = { $_."Protocol Name" } }, @{ N = 'Is Enabled'; E = { $_."Is Enabled" } }, @{ N = 'Has Multiple IP Addresses'; E = { $_."Has Multiple IP Addresses" } }, @{ N = 'Port SQL Server listening'; E = { $_."Port SQL Server listening" } } | Export-Csv -force -NoClobber -NoTypeInformation -Path "$($FileName)TCPIP.csv" } else { Write-Log -Error "Could not generate the Server Protocols csv files. " -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName } #EndRegion Check 2 Server Protocols and Port (WMI) - TCPIP #Region Output xp_msver try { Invoke-Sqlcmd2 -ServerInstance $FullInstanceName -Database master -Query "exec xp_msver" | Select @{ N = 'Property Name'; E = { $_.Name } }, @{ N = 'Value'; E = { $_.Character_value } } | Export-Csv -force -NoClobber -NoTypeInformation -Path "$($FileName)xpmsver.csv" } catch { Write-Log -Error "Could not generate the xpmsver.csv file. : Error $($_.Exception.Message) " -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName } #EndRegion Output xp_msver #Region SQL Server Configuration Write-Log -Message "Checking the SQL Server Configuration...." -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose Try { Invoke-Sqlcmd2 -ServerInstance $FullInstanceName -Database master -Query "sp_configure" | Select @{ N = 'Configuration Name'; E = { $_.name } }, @{ N = 'Configuration Value'; E = { $_.config_value } }, @{ N = 'Run Value'; E = { $_.run_value } } | Export-Csv -force -NoClobber -NoTypeInformation -Path "$($FileName)ServerConfiguration.csv" } catch { } #EndRegion SQL Server Configuration #Region SQL Server service account member of local Administrators Try { if (!($InstanceName)) { $ServiceSearch = "MSSQLSERVER" } else { $ServiceSearch = "MSSQL`$$($instancename)" } $SQLWMIService = $SQLWmi.Services[$ServiceSearch] if ($SQLWMIService) { $isAdministrator = (&{ net localgroup administrators } | Where { $_ -eq ($SQLWMIService.ServiceAccount -split "\\")[1] }) Write-Log -Message "Checking SQL Service Account ...." -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose New-Object psobject -Property @{ 'Service Account' = $SQLWMIService.ServiceAccount 'Start Mode' = $SQLWMIService.StartMode 'Is Local Administrator' = if ($isAdministrator) { "True;Red" } else { "False" } 'Startup Parameters' = $SQLWMIService.StartupParameters -replace ";", "|" } | Select @{ N = 'Service Account'; E = { $_."Service Account" } }, @{ N = 'Start Mode'; E = { $_."Start Mode" } }, @{ N = 'Is Local Administrator'; E = { $_."Is Local Administrator" } }, @{ N = 'Startup Parameters'; E = { $_."Startup Parameters" } } | Export-Csv -force -NoClobber -NoTypeInformation -Path "$($FileName)ServiceAccount.csv" } } catch { Write-Log -Error "Could not generate the Service Account.csv file : Error $($_.Exception.Message) " -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName } #EndRegion SQL Server service account member of local Administrators #Region Page File Try { Write-Log -Message "Checking Page File Settings ...." -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose $issue = "" $PageFileSize = (Get-WmiObject -ComputerName $MachineName -Class Win32_PageFileUsage).AllocatedBaseSize/1MB $PageFileAutomatic = (Get-WmiObject -ComputerName $MachineName -Class Win32_computersystem | select @{ N = 'AutomaticManagedPagefile'; E = { $_."AutomaticManagedPagefile" } }).AutomaticManagedPagefile New-Object psobject -Property @{ "Managed Page File" = if ($PageFileAutomatic) { "$($PageFileAutomatic);Red"; $issue += "Pagefile setting should be manually set and with a maximum value of 16 GB " } else { $PageFileAutomatic } "Size (MB)" = if ($PageFileSize -gt 16) { "$($PageFileSize);Red"; $issue += "Pagefile setting should be set with a maximum value of 16 GB" } else { $PageFileSize } } | Select @{ N = 'Managed Page File'; E = { $_."Managed Page File" } }, @{ N = 'Size (MB)'; E = { $_."Size (MB)" } }, @{ N = 'Issue'; E = { if ($issue) { "$($issue);RED" } else { "No Issues" } } } | Export-Csv -force -NoClobber -NoTypeInformation -Path "$($FileName)PageFile.csv" } catch { Write-Log -Error "Could not generate the Page File.csv file : Error $($_.Exception.Message) " -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName } #EndRegion Page File #Check Something #Just to explain the color alghoritm try { Write-Log -Message "Checking Something ...." -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose New-Object psobject -property @{ "Checking Something Red" = 'Something Red - not good;RED' "Checking Something Yellow" = 'Something Yellow - not too bad;Yellow' "Checking Something Green" = 'Something Green - good;Green' } | Export-Csv -force -NoClobber -NoTypeInformation -Path "$($FileName)CheckSomething.csv" } catch { Write-Log -Error "Could not generate the CheckSomething.csv file : Error $($_.Exception.Message) " -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName } ######################################## # # # Your Checks Here # ######################################## Write-Log -Message "Gathering Finished " -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose #Region Zipping Files $PathForOutputZipFile = "$($PathForOutputFiles)\Zip" if (!(Test-Path "$($PathForOutputZipFile)")) { try { New-Item -ItemType directory -Path "$($PathForOutputZipFile)" | Out-Null } catch { Write-Log -Message "Could not create the $($PathForOutputZipFile) for zip files " -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose } } try { Remove-Item -Path "$($PathForOutputZipFile)\$($csvnames)_BestPractices.zip" -ErrorAction SilentlyContinue -Force Zip-Directory -DestinationFileName "$($PathForOutputZipFile)\$($csvnames)_BestPractices.zip" -SourceDirectory "$($PathForOutputFiles)" Write-Log -Message "Zip File at $($FileName)BestPractices.zip" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose } catch { if ($_.exception -notlike "*because it is being used by another process*") { Write-Log -Message "Could not zip the files. Please do it manually. " -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose } else { Write-Log -Message "Zip File at $($FileName)BestPractices.zip" -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName -verbose } } #EndRegion Zipping Files } else { Write-Log -Error "Could not create the path to export the data : " -path $PathWriteLog -ErrorLogFileName $ErrorLogfileName } }