$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.
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
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
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
The following is a list of the last $EventNum Application log events that had an Event Type of either Warning or Error on $servername
"@
$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"}
}