PowerShell Scrip for OS and SQL health check

$users = "d@e.com"
$fromemail = "N@e.com"
$smtpserver = "e.com"
$starttime = Get-Date
$HTMLHeader = @"

My Systems Report "@ $HTMLEnd = @" "@ $a = "" $a = $a + "BODY{background-color:#FDF3E7;}" $a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}" $a = $a + "TH{border-width: 1px;padding: 1px;border-style: solid;border-color: black;;background-color:thistle}" $a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}" $a = $a + "" $colorTagTable = @{ Stopped = ' bgcolor="RED">Stopped<'; Running = ' bgcolor="Green">Running<'; OFFLINE = ' bgcolor="RED">OFFLINE<'; ONLINE= ' bgcolor="Green">ONLINE<' "ALL DATABASES ARE" = ' bgcolor="Green">ALL DATABASES ARE<' "ALL Databases has been" = ' bgcolor="Green">ALL Databases has been<'; "backup" = ' bgcolor="Green">backup<'; "in Last 24 Hours" = ' bgcolor="Green">in Last 24 Hours<'; "No Job Failed in Last 24 Hours" = ' bgcolor="Green">No Job Failed in Last 24 Hours<'; "Error Log" = ' bgcolor="Green">Error Log<'; "check didnot find out anything major" = ' bgcolor="Green">check didnot find out anything major<'; "but will still advise to please verify manually" = ' bgcolor="Green">but will still advise to please verify manually<'; "Server Might Have Memory Issue"= ' bgcolor="Red">Server Might Have Memory Issue<'; } $sql_server_info = "select @@servername as [SQLNetworkName], CAST( SERVERPROPERTY('MachineName') AS NVARCHAR(128)) AS [MachineName], CAST( SERVERPROPERTY('ServerName')AS NVARCHAR(128)) AS [SQLServerName], CAST( SERVERPROPERTY('IsClustered') AS NVARCHAR(128)) AS [IsClustered], CAST( SERVERPROPERTY('ComputerNamePhysicalNetBIOS')AS NVARCHAR(128)) AS [SQLService_Current_Node], serverproperty('edition') as [Edition], serverproperty('productlevel') as [Servicepack], CAST( SERVERPROPERTY('InstanceName') AS NVARCHAR(128)) AS [InstanceName], SERVERPROPERTY('Productversion') AS [ProductVersion],@@version as [Serverversion]" $sqlserverAgent = " IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#sql_agent_state')) BEGIN drop table #sql_agent_state END declare @sql_agent_service varchar(128),@state_sql_agent varchar(20) create table #sql_agent_state(service_name varchar(128) default 'SQLAgent ' ,state varchar(20)) insert into #sql_agent_state(state) exec xp_servicecontrol N'querystate',N'SQLServerAGENT' select service_name as ServiceName, replace(state,'.','') as Status from #sql_agent_state " $SQLServerDatabaseState = " IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tmp_database')) BEGIN drop table #tmp_database END declare @count int declare @name varchar(128) declare @state_desc varchar(128) select @count = COUNT(*) from sys.databases where state_desc not in ('ONLINE','RESTORING') create table #tmp_database (name nvarchar(128),state_desc nvarchar(128)) if @count > 0 begin Declare Cur1 cursor for select name,state_desc from sys.databases where state_desc not in ('ONLINE','RESTORING') open Cur1 FETCH NEXT FROM Cur1 INTO @name,@state_desc WHILE @@FETCH_STATUS = 0 BEGIN insert into #tmp_database values(@name,@state_desc) FETCH NEXT FROM Cur1 INTO @name,@state_desc END CLOSE Cur1 DEALLOCATE Cur1 end else begin insert into #tmp_database values('ALL DATABASES ARE','ONLINE') end select name as DBName ,state_desc as DBStatus from #tmp_database " $SQLJob = " declare @count int select @count = count(1) from msdb.dbo.sysjobs as sj join msdb.dbo.sysjobhistory as sjh on sj.job_id = sjh.job_id where sj.enabled != 0 and sjh.sql_message_id > 0 and sjh.run_date > CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112) and sjh.Step_id <= 1 if (@count >= 1) begin select distinct sj.name as SQLJobName from msdb.dbo.sysjobs as sj join msdb.dbo.sysjobhistory as sjh on sj.job_id = sjh.job_id where sj.enabled != 0 and sjh.sql_message_id > 0 and sjh.run_date > CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112) and sjh.Step_id <= 1 order by name end else begin Select 'No Job Failed in Last 24 Hours' as SQLJobName end " $SQLServerDatabaseBackup = " declare @backupcount int select @backupcount = count(1) from sys.databases where state != 6 and name not like 'Tempdb%' and name not in ( select database_name from msdb.dbo.backupset as bkupset join msdb.dbo.backupmediafamily as bkupmedf on bkupset.media_set_id = bkupmedf.media_set_id where type in ('D','I') and backup_start_date > (CONVERT(datetime,getdate()) - 1) ) if (@backupcount >= 1) begin select name as DBName, State_Desc as DBStatus,'Backup Not happened' as DBComments from sys.databases where state != 6 and name not like 'Tempdb%' and name not in ( select database_name from msdb.dbo.backupset as bkupset join msdb.dbo.backupmediafamily as bkupmedf on bkupset.media_set_id = bkupmedf.media_set_id where type in ('D','I') and backup_start_date > (CONVERT(datetime,getdate()) - 1) ) order by 1 end else begin Select 'ALL Databases has been' as DBName, 'backup' as DBStatus ,'in Last 24 Hours' as DBComments end " $SQLServerErrorlog = " declare @errorlogcount int IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#errorlog')) BEGIN DROP TABLE #errorlog END create table #errorlog(date_time datetime,processinfo varchar(123),Comments varchar(max)) insert into #errorlog exec sp_readerrorlog select @errorlogcount = count(*) from #errorlog where date_time > (CONVERT(datetime,getdate()) - 0.5) and Comments like '%fail%' and Comments like '%error%' and processinfo not in ('Server','Logon') if(@errorlogcount >= 1) begin select date_time as Date,processinfo as ProcessInfo, Comments from #errorlog where date_time > (CONVERT(datetime,getdate()) - 0.5) and Comments like '%fail%' and Comments like '%error%' and processinfo not in ('Server','Logon') end else begin select 'Error Log' as Date, 'check didnot find out anything major' as ProcessInfo, 'but will still advise to please verify manually' as Comments end " $sqlservermemorypressuredetection = "declare @totalmemoryused bigint declare @bufferpool_allocated bigint declare @query2008r2_total nvarchar(max) declare @query2012_total nvarchar(max) declare @version nvarchar(128) Set @query2008r2_total= 'select SUM(single_pages_kb+multi_pages_kb+virtual_memory_committed_kb+awe_allocated_kb)/1024 from sys.dm_os_memory_clerks' set @query2012_total = 'select SUM(pages_kb+virtual_memory_committed_kb+awe_allocated_kb)/1024 from sys.dm_os_memory_clerks' select @version = cast(SERVERPROPERTY('Productversion') as nvarchar(128)) if (@version like '11%') begin create table #tmp (value bigint) insert into #tmp Execute (@query2012_total) select @totalmemoryused = value from #tmp drop table #tmp end else begin create table #tmp_1 (value bigint) insert into #tmp_1 Execute (@query2008r2_total) select @totalmemoryused = value from #tmp_1 drop table #tmp_1 end select @bufferpool_allocated = cntr_value/1024 FROM sys.dm_os_performance_counters WHERE counter_name IN ('Target Server Memory (KB)') if (@bufferpool_allocated > @totalmemoryused) begin Select 'Server has no Memory Issue' as Comments end else begin select 'Server Might Have Memory Issue' as Comments end " $LongRunningQueries = " SELECT TOP 10 DB_NAME(qt.dbid) AS DBName, o.name AS ObjectName, qs.total_worker_time / 1000000 / qs.execution_count As Avg_MultiCore_CPU_time_sec, qs.total_worker_time / 1000000 as Total_MultiCore_CPU_time_sec, qs.total_elapsed_time / qs.execution_count / 1000000.0 AS Average_Seconds, qs.total_elapsed_time / 1000000.0 AS Total_Seconds, qs.execution_count as Count, qs.last_execution_time as Time, SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS Query FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id where last_execution_time > getdate()-1 ORDER BY average_seconds DESC " $CPUConsumingQuery = " SELECT TOP 10 DB_NAME(qt.dbid) as DBName, o.name AS ObjectName, qs.total_worker_time / 1000000 / qs.execution_count AS Avg_MultiCore_CPU_time_sec, qs.total_worker_time / 1000000 As Total_MultiCore_CPU_time_sec, qs.total_elapsed_time / 1000000 / qs.execution_count As Average_Seconds, qs.total_elapsed_time / 1000000 As Total_Seconds, (total_logical_reads + total_logical_writes) / qs.execution_count as Average_IO, total_logical_reads + total_logical_writes as Total_IO, qs.execution_count as Count, qs.last_execution_time as Time, SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.[text]) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1 ) as Query FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id where qs.execution_count > 5 --more than 5 occurences ORDER BY Total_MultiCore_CPU_time_sec DESC " $IOConsumingQuery = " SELECT TOP 10 DB_NAME(qt.dbid) AS DBName, o.name AS ObjectName, qs.total_elapsed_time / 1000000 / qs.execution_count As Average_Seconds, qs.total_elapsed_time / 1000000 As Total_Seconds, (total_logical_reads + total_logical_writes ) / qs.execution_count AS Average_IO, (total_logical_reads + total_logical_writes ) AS Total_IO, qs.execution_count AS Count, last_execution_time As Time, SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS Query FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id where last_execution_time > getdate()-1 ORDER BY average_IO DESC " $CPUPressure = " SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS Pct_Signal_CPU_Waits, 'if Perc_signal_cpu_waits is > 15%, it means we have CPU pressure' as Comment, CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS Pct_Resource_Waits FROM sys.dm_os_wait_stats " $WaitTimePercentage = "declare @totalwait_time_ms float select @totalwait_time_ms= sum(wait_time_ms) FROM sys.dm_os_wait_stats where wait_time_ms > 0 select top 10 wait_type as WaitEvent, wait_time_ms/1000 as Time_inSec, round(100*(cast(wait_time_ms as float)/@totalwait_time_ms),2) as PctUsed FROM sys.dm_os_wait_stats where wait_time_ms > 0 order by wait_time_ms desc " Function Create-PieChart() { param([string]$FileName) $FileName="c:\temp\chart-$servername" [void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") [void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization") $Chart = New-object System.Windows.Forms.DataVisualization.Charting.Chart $Chart.Width = 200 $Chart.Height = 190 $Chart.Left = 10 $Chart.Top = 10 $ChartArea = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea $Chart.ChartAreas.Add($ChartArea) [void]$Chart.Series.Add("Data") foreach ($value in $args[0]) { Write-Host "Now processing chart value: " + $value $datapoint = new-object System.Windows.Forms.DataVisualization.Charting.DataPoint(0, $value) $datapoint.AxisLabel = "Value" + "(" + $value + " GB)" $Chart.Series["Data"].Points.Add($datapoint) } $Chart.Series["Data"].ChartType = [System.Windows.Forms.DataVisualization.Charting.SeriesChartType]::Pie $Chart.Series["Data"]["PieLabelStyle"] = "Outside" $Chart.Series["Data"]["PieLineColor"] = "Black" $Chart.Series["Data"]["PieDrawingStyle"] = "Concave" ($Chart.Series["Data"].Points.FindMaxByValue())["Exploded"] = $true $Title = new-object System.Windows.Forms.DataVisualization.Charting.Title $Chart.Titles.Add($Title) $Chart.Titles[0].Text = "RAM Usage Chart (Used/Free)" $Chart.SaveImage($FileName + ".png","png") } $thresholdspace = 20 [int]$EventNum = 3 [int]$ProccessNumToFetch = 10 $servernames = get-content C:\Temp\ComputerName.txt foreach($servername in $servernames) { $HTMLmessage='' $HTMLHeader='' $HTMLMiddle='' $HTMLEnd='' $ListOfAttachments = @() $Report = @() $ServicesReport = @() $SystemEventsReport = @() $ApplicationEventsReport = @() $CurrentTime = Get-Date $PingResult = Test-Connection -ComputerName $servername -Count 1 -Quiet if($PingResult) { Function Get-HostUptime { $Uptime = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $servername $LastBootUpTime = $Uptime.ConvertToDateTime($Uptime.LastBootUpTime) $Time = (Get-Date) - $LastBootUpTime Return '{0:00} Days, {1:00} Hours, {2:00} Minutes, {3:00} Seconds' -f $Time.Days, $Time.Hours, $Time.Minutes, $Time.Seconds } $DiskInfo= Get-WMIObject -ComputerName $servername Win32_LogicalDisk | Where-Object{$_.DriveType -eq 3} | Where-Object{ ($_.freespace/$_.Size)*100 -lt $thresholdspace} ` | Select-Object SystemName, DriveType, VolumeName, Name, @{n='Size (GB)';e={"{0:n2}" -f ($_.size/1gb)}}, @{n='FreeSpace (GB)';e={"{0:n2}" -f ($_.freespace/1gb)}}, @{n='PercentFree';e={"{0:n2}" -f ($_.freespace/$_.size*100)}} | ConvertTo-HTML -fragment $OS = (Get-WmiObject Win32_OperatingSystem -computername $servername).caption $SystemInfo = Get-WmiObject -Class Win32_OperatingSystem -computername $servername | Select-Object Name, TotalVisibleMemorySize, FreePhysicalMemory $TotalRAM = $SystemInfo.TotalVisibleMemorySize/1MB $FreeRAM = $SystemInfo.FreePhysicalMemory/1MB $UsedRAM = $TotalRAM - $FreeRAM $RAMPercentFree = ($FreeRAM / $TotalRAM) * 100 $TotalRAM = [Math]::Round($TotalRAM, 2) $FreeRAM = [Math]::Round($FreeRAM, 2) $UsedRAM = [Math]::Round($UsedRAM, 2) $RAMPercentFree = [Math]::Round($RAMPercentFree, 2) $TopProcesses = Get-Process -ComputerName $servername | Sort WS -Descending | Select ProcessName, Id, WS -First $ProccessNumToFetch | ConvertTo-Html -Fragment $LastPatchedInfo=Get-HotFix -cn $servername $Nu=$LastPatchedInfo | FT InstalledOn-HideTableHeaders | Out-String if ([string]::IsNullOrWhiteSpace($Nu)) { $LastPatchedDate="There is some issue in reading patched date on $servername" $LatestPatchedDate = $LastPatchedInfo | Select HotFixID,InstalledBy | ConvertTo-Html -Fragment } else { $LastPatchedDate = $LastPatchedInfo | Sort-Object InstalledOn -Descending | Select InstalledOn -First 1 $i=$LastPatchedDate.InstalledOn.Date.ToString("M/dd/yyyy")+"*" $LatestPatchedDate=$LastPatchedInfo | Where-Object {$_.InstalledOn -match "$i"} | Select HotFixID,InstalledBy | ConvertTo-Html -Fragment $LastPatchedDate= $LastPatchedDate | ConvertTo-Html -Fragment -as list -Property '*' | ? { $_ -replace '*', '' } } $Services = Get-WmiObject -Class Win32_Service -ComputerName $servername | Where {($_.StartMode -eq "Auto") -and ($_.State -eq "Stopped")} foreach ($Service in $Services) { $row = New-Object -Type PSObject -Property @{ Name = $Service.Name Status = $Service.State StartMode = $Service.StartMode } $ServicesReport += $row } $ServicesReport = $ServicesReport | ConvertTo-Html -Fragment $SystemEvents = Get-EventLog -ComputerName $servername -LogName System -EntryType Error,Warning -Newest $EventNum foreach ($event in $SystemEvents) { $row = New-Object -Type PSObject -Property @{ TimeGenerated = $event.TimeGenerated EntryType = $event.EntryType Source = $event.Source Message = $event.Message } $SystemEventsReport += $row } $SystemEventsReport = $SystemEventsReport | ConvertTo-Html -Fragment $ApplicationEvents = Get-EventLog -ComputerName $servername -LogName Application -EntryType Error,Warning -Newest $EventNum foreach ($event in $ApplicationEvents) { $row = New-Object -Type PSObject -Property @{ TimeGenerated = $event.TimeGenerated EntryType = $event.EntryType Source = $event.Source Message = $event.Message } $ApplicationEventsReport += $row } $ApplicationEventsReport = $ApplicationEventsReport | ConvertTo-Html -Fragment Create-PieChart -FileName ((Get-Location).Path + "\chart-$servername") $FreeRAM, $UsedRAM $ListOfAttachments += "chart-$servername.png" $SystemUptime = Get-HostUptime -ComputerName $servername $currentTime=(get-date -format yyyyMMdd) $CurrentSystemHTML = @"

Report as on $currentTime

System Info

System Uptime $SystemUptime
OS $OS
LastServerPatched $LastPatchedDate
ServicePackApplied $LatestPatchedDate
Total RAM (GB) $TotalRAM
Free RAM (GB) $FreeRAM
Percent free RAM $RAMPercentFree
$servername Chart

Disk Info

Drive(s) listed below have less than $thresholdspace % free space. Drives above this threshold will not be listed.

$DiskInfo

System Processes - Top $ProccessNumToFetch Highest Memory Usage

The following $ProccessNumToFetch processes are those consuming the highest amount of Working Set (WS) Memory (bytes) on $servername

$TopProcesses

System Services - Automatic Startup but not Running

The following services are those which are set to Automatic startup type, yet are currently not running on $servername

$ServicesReport

Events Report - The last $EventNum System/Application Log Events that were Warnings or Errors

The following is a list of the last $EventNum System log events that had an Event Type of either Warning or Error on $servername

$SystemEventsReport

The following is a list of the last $EventNum Application log events that had an Event Type of either Warning or Error on $servername

$ApplicationEventsReport
"@ $HTMLMiddle += $CurrentSystemHTML $HTMLmessage = $HTMLHeader + $HTMLMiddle + $HTMLEnd write-host Starting Server $servername $dataSource=$servername $TableHeader = "OS Health Check Report" $path = "C:\Temp\" $name = $dataSource -replace "\\","_" $OutputFile_new = $path + $name + '.html' $DatabaseServer=$servername.Substring(4,1) if($DatabaseServer -eq "D") { $dataSource=$servername+",11001" $database = "master" $TableHeader = "SQL Server Health Check Report" $path = "C:\Temp\" $name = $dataSource -replace "\\","_" $OutputFile_new = $path + $name + '.html' $connectionDetails = "Provider=sqloledb; " + "Data Source=$dataSource; " + "Initial Catalog=$database; " + "Integrated Security=SSPI;" $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command1 = New-Object System.Data.OleDb.OleDbCommand $sql_server_info,$connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command1 $dataSet1 = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet1) $connection.Close() $frag1 = $dataSet1.Tables | Select-Object -Expand Rows |select -Property SQLNetworkName,MachineName,SQLServerName,IsClustered,SQLService_Current_Node,Edition,Servicepack,InstanceName,ProductVersion,Serverversion| ConvertTo-HTML -AS Table -Fragment -PreContent '

SQL Server Info

'|Out-String $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command2 = New-Object System.Data.OleDb.OleDbCommand $sqlserverAgent,$connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command2 $dataSet2 = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet2) $connection.Close() $frag2 = $dataSet2.Tables | Select-Object -Expand Rows| Select -Property ServiceName,Status|ConvertTo-HTML -AS Table -Fragment -PreContent '

SQL Server Agent Status

'|Out-String $colorTagTable.Keys | foreach { $frag2 = $frag2 -replace ">$_<",($colorTagTable.$_) } $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command3 = New-Object System.Data.OleDb.OleDbCommand $SQLServerDatabaseState,$connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command3 $dataSet3 = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet3) $connection.Close() $frag3 = $dataSet3.Tables | Select-Object -Expand Rows |Select -Property DBName,DBStatus | ConvertTo-HTML -AS Table -Fragment -PreContent '

SQLServer Databases State

'|Out-String $colorTagTable.Keys | foreach { $frag3 = $frag3 -replace ">$_<",($colorTagTable.$_) } $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command4 = New-Object System.Data.OleDb.OleDbCommand $SQLJob,$connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command4 $dataSet4 = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet4) $connection.Close() $frag4 = $dataSet4.Tables | Select-Object -Expand Rows |select -Property SQLJobName | ConvertTo-HTML -AS Table -Fragment -PreContent '

SQLServer SQL Job failed in last 24 Hours

'|Out-String $colorTagTable.Keys | foreach { $frag4 = $frag4 -replace ">$_<",($colorTagTable.$_) } $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command5 = New-Object System.Data.OleDb.OleDbCommand $SQLServerDatabaseBackup,$connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command5 $dataSet5 = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet5) $connection.Close() $frag5 = $dataSet5.Tables | Select-Object -Expand Rows |select -property DBName,DBStatus,DBComments | ConvertTo-HTML -AS Table -Fragment -PreContent '

SQLServer Database Backup status in Last 24 Hours

'|Out-String $colorTagTable.Keys | foreach { $frag5 = $frag5 -replace ">$_<",($colorTagTable.$_) } $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command6 = New-Object System.Data.OleDb.OleDbCommand $SQLServerErrorlog,$connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command6 $dataSet6 = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet6) $connection.Close() $frag6 = $dataSet6.Tables | Select-Object -Expand Rows|select -Property Date,processinfo,Comments | ConvertTo-HTML -AS Table -Fragment -PreContent '

SQLServer ErroLog Information

'|Out-String $colorTagTable.Keys | foreach { $frag6 = $frag6 -replace ">$_<",($colorTagTable.$_) } $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command7 = New-Object System.Data.OleDb.OleDbCommand $SQLServerCPUInformation,$connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command7 $dataSet7 = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet7) $connection.Close() $frag7 = $dataSet7.Tables | Select-Object -Expand Rows|select -Property Logical_CPU_Count,Hyperthread_Ratio,Physical_CPU_Count,Physical_Memory_in_MB| ConvertTo-HTML -AS Table -Fragment -PreContent '

CPU Information

'|Out-String $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command8 = New-Object System.Data.OleDb.OleDbCommand $SQLServerMemoryAllocated,$connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command8 $dataSet8 = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet8) $connection.Close() $frag8 = $dataSet8.Tables | Select-Object -Expand Rows|select -Property Counter,MemoryLimitSet_inMB | ConvertTo-HTML -AS Table -Fragment -PreContent '

Memory Allocated to SQL Server

'|Out-String $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command9 = New-Object System.Data.OleDb.OleDbCommand $SqlServerMemortConsumingobjects,$connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command9 $dataSet9 = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet9) $connection.Close() $frag9 = $dataSet9.Tables | Select-Object -Expand Rows | select -Property Object,Space_used_inMB| ConvertTo-HTML -AS Table -Fragment -PreContent '

Top 10 Memory Consuming SQL Objects

'|Out-String $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command10 = New-Object System.Data.OleDb.OleDbCommand $sqlservermemorypressuredetection,$connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command10 $dataSet10 = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet10) $connection.Close() $frag10 = $dataSet10.Tables | Select-Object -Expand Rows |select -Property Comments | ConvertTo-HTML -AS Table -Fragment -PreContent '

Server Have Memory Pressure or Not

'|Out-String $colorTagTable.Keys | foreach { $frag10 = $frag10 -replace ">$_<",($colorTagTable.$_) } $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command11 = New-Object System.Data.OleDb.OleDbCommand $LongRunningQueries,$connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command11 $dataSet11 = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet11) $connection.Close() $frag11 = $dataSet11.Tables | Select-Object -Expand Rows|select -Property DBName,ObjectName,Avg_MultiCore_CPU_time_sec, Total_MultiCore_CPU_time_sec,Average_Seconds,Total_Seconds,Count,Time,Query| ConvertTo-HTML -AS Table -Fragment -PreContent '

Top 10 Long Running Query

'|Out-String $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command12 = New-Object System.Data.OleDb.OleDbCommand $CPUConsumingQuery,$connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command12 $dataSet12 = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet12) $connection.Close() $frag12 = $dataSet12.Tables | Select-Object -Expand Rows |select -Property DBName,ObjectName,Avg_MultiCore_CPU_time_sec, Total_MultiCore_CPU_time_sec,Average_Seconds,Total_Seconds,Average_IO,Total_IO,Count,Time,Query | ConvertTo-HTML -AS Table -Fragment -PreContent '

Top 10 CPU Consuming Query

'|Out-String $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command13 = New-Object System.Data.OleDb.OleDbCommand $IOConsumingQuery,$connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command13 $dataSet13 = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet13) $connection.Close() $frag13 = $dataSet13.Tables | Select-Object -Expand Rows |Select -Property DBName,ObjectName,Average_Seconds, Total_Seconds, Average_IO, Total_IO, Count, Time, Query | ConvertTo-HTML -AS Table -Fragment -PreContent '

Top 10 IO Consuming Query

'|Out-String $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command14 = New-Object System.Data.OleDb.OleDbCommand $CPUPressure,$connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command14 $dataSet14 = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet14) $connection.Close() $frag14 = $dataSet14.Tables | Select-Object -Expand Rows | select -Property Pct_Signal_CPU_Waits,Comment, Pct_Resource_Waits | ConvertTo-HTML -AS Table -Fragment -PreContent '

CPU Pressure Detection

'|Out-String $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command15 = New-Object System.Data.OleDb.OleDbCommand $WaitTimePercentage,$connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command15 $dataSet15 = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet15) $connection.Close() $frag15 = $dataSet15.Tables | Select-Object -Expand Rows |select -Property WaitEvent,Time_inSec,PctUsed | ConvertTo-HTML -AS Table -Fragment -PreContent '

Wait Type % Allocation on Server

'|Out-String write-host $frag15 ConvertTo-HTML -head $a -PostContent $HTMLmessage,$frag1,$frag2,$frag3,$frag4,$frag5,$frag6,$frag7,$frag8,$frag9,$frag10,$frag14,$frag15,$frag11,$frag12,$frag13 -PreContent "

OS and SQL Server Health Check Report

" | Out-File $OutputFile_new } Else {ConvertTo-HTML -head $a -PostContent $HTMLmessage -PreContent "

$servername Server OS Health Check Report

" | Out-File $OutputFile_new } $anonUsername = "anonymous" $anonPassword = ConvertTo-SecureString -String "anonymous" -AsPlainText -Force $anonCredentials = New-Object System.Management.Automation.PSCredential($anonUsername,$anonPassword) $ListOfAttachments += "$OutputFile_new" $body= Get-Content $OutputFile_new $subject = "Health Check Report for Server: " + $servername write-host "Sending email" Add-PSSnapin Microsoft.Exchange.Management.Powershell.Admin -erroraction silentlyContinue foreach ($user in $users) { $att = new-object Net.Mail.Attachment("C:\Temp\chart-$servername.png") $att.ContentId = "att" $msg = new-object Net.Mail.MailMessage $smtp = new-object Net.Mail.SmtpClient($smtpServer) $msg.From = $fromemail $msg.To.add($user) $msg.Subject = "$subject" + (get-date -format yyyy/MM/dd) $msg.Body = $body $msg.IsBodyHTML = $true $msg.Attachments.Add($att) $smtp.Send($msg) } $att.Dispose() $Stoptime = Get-Date Write-host $Stoptime } Else {Write-host "$servername Server is not up"} }