Always ON Healthcheck Report

-----------GetAlwaysOnStatus.sql
if (SERVERPROPERTY('ishadrenabled') = 1)
Begin
select convert(Nvarchar(500),SERVERPROPERTY('servername')) as Servername, n.group_name AlwaysONGroup,n.replica_server_name,n.node_name NodeName,rs.role_desc Role,
db_name(drs.database_id) as 'DBName',drs.synchronization_state_desc State,drs.synchronization_health_desc Health
,getdate() as Rundate
from master.sys.dm_hadr_availability_replica_cluster_nodes n
join master.sys.dm_hadr_availability_replica_cluster_states cs
on n.replica_server_name = cs.replica_server_name
join master.sys.dm_hadr_availability_replica_states rs 
on rs.replica_id = cs.replica_id
join master.sys.dm_hadr_database_replica_states drs
on rs.replica_id=drs.replica_id
join master.sys.availability_group_listeners AS AGL ON AGL.group_id = rs.group_id
where n.replica_server_name = @@SERVERNAME
and rs.replica_id=drs.replica_id
End 
-------------TruncateTable
Truncate table dbadmin.dbo.DbadminDashboard_alwayson_status
--------------GetServerList
--Query to check All prod servers
--select  SRV.server_name from [ServerName].[msdb].[dbo].
--[sysmanagement_shared_registered_servers_internal] SRV join 
--[ServerName].[msdb].[dbo].[sysmanagement_shared_server_groups_internal] GRP
--on SRV.server_group_id=GRP.server_group_id
--where  substring(SRV.server_name,4,4) = 'PROD'
--and substring(SRV.server_name,1,3) in 
--( )
--Union 
--select  SRV.server_name from [ServerName].[msdb].[dbo].
--[sysmanagement_shared_registered_servers_internal] SRV join 
--[ServerName].[msdb].[dbo].[sysmanagement_shared_server_groups_internal] GRP
--on SRV.server_group_id=GRP.server_group_id
--and GRP.name  = 'XXX'
--order by SRV.server_name asc
--Query to check All Non-prod servers
select  SRV.server_name from [ServerName].[msdb].[dbo].
[sysmanagement_shared_registered_servers_internal] SRV join 
[ServerName].[msdb].[dbo].[sysmanagement_shared_server_groups_internal] GRP
on SRV.server_group_id=GRP.server_group_id
where  substring(SRV.server_name,4,4) = 'PROD'

--Union 
--select  SRV.server_name from [ServerName].[msdb].[dbo].
--[sysmanagement_shared_registered_servers_internal] SRV join 
--[ServerName].[msdb].[dbo].[sysmanagement_shared_server_groups_internal] GRP
--on SRV.server_group_id=GRP.server_group_id

--Query to check All servers
--select  SRV.server_name from [ServerName].[msdb].[dbo].
--[sysmanagement_shared_registered_servers_internal] SRV join 
--[ServerName].[msdb].[dbo].[sysmanagement_shared_server_groups_internal] GRP
--on SRV.server_group_id=GRP.server_group_id
--where  substring(SRV.server_name,1,3) in 
--()
--order by SRV.server_name asc

-------------MainPS.ps1
$cmsserver ='ServerName'
$cmsdb = 'dbadmin'
$defaultdb='Master'
$uname ='XXXXXX'
$pword = 'XXXXXXX'
# change this parameter if running for other things
$BaseDir='C:SQLServerReportMonitoringAlwaysOn' 
$Outpath = $BaseDir + "Output"
$LogFile = $BaseDir + 'LogFile.txt'
$FailedServers = $BaseDir + 'FailedServers.txt'
#$SuccessServers = $BaseDir + 'SuccessServers.txt'
"CMS Server -- " + $cmsserver | out-file $LogFile  -Width 180
"Output Directory --" + $Outpath | out-file $LogFile  -Width 180  -Append
"Base Directory --" + $BaseDir | out-file $LogFile  -Width 180 -Append
"Logfile --" + $LogFile | out-file $LogFile  -Width 180 -Append
"This file captures servers which are not reachable or have some issues.Please check them." + "`r`n" + "For detailed status,query table -- dbadmin.dbo.DbadminDashboard_alwayson_status " + "`r`n" | out-file $FailedServers  -Width 180


$SuccessServers = $BaseDir + 'SuccessServers.txt'
"This file captures servers which are reached successfully." + "`r`n" + "For detailed status,query table -- dbadmin.dbo.DbadminDashboard_SqlServices_Status " + "`r`n" | out-file $SuccessServers -Width 180

"SQL Server Processing - Started " | out-file $LogFile  -Width 180  -Append
Get-ChildItem -Path $Outpath *.csv | out-file $LogFile  -Width 180 -Append

"removing old files starts" | out-file $LogFile  -Width 180 -Append
Get-ChildItem -Path $Outpath *.csv | foreach { Remove-Item $_.FullName}
"removing old files done" | out-file $LogFile  -Width 180 -Append

$CurDate  =  (Get-Date -Format MM) + (get-Date -format dd) +  (get-Date -format yyyy) 
$CurDatetime  =  (Get-Date -Format MM) + (get-Date -format dd) +  (get-Date -format yyyy)  + (get-Date -format hh) + (get-Date -format mm)
$ServerNameFile= $BaseDir + "GetServerList.sql"
$GetQueryFile =$BaseDir + "GetAlwaysOnStatus.sql"
$ServerNameQuery = get-content "$ServerNameFile"
'ServerQuery  -- ' + $ServerNameQuery + "`r`n" | out-file $LogFile  -Width 180  -Append
$serverlist = Invoke-Sqlcmd -ServerInstance $cmsserver -Database $cmsdb -Inputfile $ServerNameFile -QueryTimeout 300 
$FailedServers = $BaseDir + 'FailedServers.txt'
ForEach ($instance in $serverlist)
{
$instance=$instance.Item(0) 
$servername = $instance.split("")  |Select-Object -Index 0  
"`r`n" + 'Checking Server --' + $servername   | out-file $LogFile  -Width 180  -Append
If (Test-Connection  -ComputerName $servername -quiet -BufferSize 32 -Count 1 )  
{
#move to fetch the data if ping succeeds
$servername + '            --Ping Succeeded'  | out-file $LogFile  -Width 180  -Append
try
{ 
#log start of process
# $instance= $instance + ",1433"
#"$Instance fetch starts at " + $CurDatetime    | out-file $LogFile  -Width 180  -Append 
$results=Invoke-Sqlcmd -ServerInstance $instance -Database $defaultdb  -Username $uname -Password $pword  -InputFile $GetQueryFile -QueryTimeout 300 
"Saving Success Backup Results in CSV format..."  +  $instance 
$CsvFilePath = $Outpath + "AlwaysONStatus_Report.csv"
$results | export-csv  $CsvFilePath   -NoTypeInformation -Append
#log end of process
"$Instance            --DataExtract Succeeded at " + $CurDatetime     | out-file $LogFile  -Width 180  -Append
"$Instance            --Succeeded "| out-file $SuccessServers -Width 180  -Append
} 

{
"$Instance            --DataExtract Failed at " + $CurDatetime     | out-file $FailedServers  -Width 180  -Append 
}
} 
#Catch the error if the ping test fails
else 
{
$servername + '            --Ping Failed '  | out-file $FailedServers  -Width 180  -Append
}
}

(Get-Content $csvFilePath) | Foreach-Object {$_ -replace "`"", ""} | Set-Content $CsvFilePath
"SQL Server Processing - COMPLETED"  +"`r`n"  | out-file $LogFile  -Width 180 -Append

----------------Mail.ps1
"Generating mail "
$BaseDir='D:PSScriptAlwaysOn'
$CsvFilePath='D:PSScriptAlwaysOnOutput*.csv'
$smtp='XXXXXXXXXXXXXXX'
$ToAddress='XXXXXXXXXXX'
$fromAddress='XXXXXXXXXXX'
$EmailSubject = "Dashboard_alwayson_final_status Reports" + " - " +'-' + $CurDate
$Body = "`r`n"  + "`r`n" + "Dashboard_alwayson_final_status Reports --" 
$files = get-childitem $CsvFilePath | where name -like "*.csv"
$attachments = $files.fullname
Send-MailMessage -to $ToAddress -From $fromAddress -SmtpServer $smtp -Subject $EmailSubject -BodyAsHtml $body -Attachments $attachments
"Mail sent"
"Dashboard_alwayson_final_status  Reports "
----------------CsvToTable.ps1
'table DbadminDashboard_alwayson_final_status truncation start'
#load data in table 
$BaseDir='C:SQLServerReportMonitoringAlwaysOn' 
$CsvFilePath=$BaseDir + 'Output*.csv'
$cmsserver ='CMSServerName'
$cmsdb = 'dbadmin'
$table = 'dbo.DbadminDashboard_alwayson_status'
$TruncateFile= $BaseDir + "TruncateTable.sql"
$results=Invoke-Sqlcmd -ServerInstance $cmsserver -Database $cmsdb -InputFile $TruncateFile -QueryTimeout 300
'table DbadminDashboard_alwayson_final_status truncation end'
'table DbadminDashboard_alwayson_final_status load start'
'table DbadminDashboard_alwayson_final_status load start'
$CSVImport = Import-CSV $CsvFilePath
ForEach ($CSVLine in $CSVImport)
{
$CSVServername = $CSVLine.Servername
$CSVgroup_name = $CSVLine.AlwaysONGroup
$CSVreplica_server_name = $CSVLine.replica_server_name
$CSVnode_name = $CSVLine.NodeName
$CSVrole_desc = $CSVLine.Role
$CSVDBName = $CSVLine.DBName
$CSVsynchronization_state_desc = $CSVLine.state
$CSVsynchronization_health_desc = $CSVLine.health

$SQLInsert = "USE $cmsdb
INSERT INTO $table (Servername,group_name,replica_server_name,node_name,role_desc,DBName,synchronization_state_desc,synchronization_health_desc) VALUES('$CSVServername','$CSVgroup_name', '$CSVreplica_server_name', '$CSVnode_name', '$CSVrole_desc', '$CSVDBName', '$CSVsynchronization_state_desc','$CSVsynchronization_health_desc')"
Invoke-SQLCmd -ServerInstance $cmsserver -Database $cmsdb -Query $SQLInsert 
}

'table DbadminDashboard_alwayson_final_status load end'