In my previous post Running Powershell for SQL Use* in Parallel - POshRBAR no more. (sqlrod.com) , I discussed the use of powershell jobs, threaded or otherwise to collect SQL information. That example, although simple and particularly contrived, showed a more optimised way to run powershell against a group of servers. In the case of larger estates, this can be a really useful option to have.
The chances are that you have other systems monitoring your SQL servers already, so this task isn't required at all. However, sometimes a quick 'knock on the door of SQL' to confirm a response isn't a bad thing as a sanity check.
So, building on that, we're going to use the same technique to essentially perform a sweep of our estate (with a few bells and whistles added) to give us a colour coded quick view of service status like below, with a little bit of additional info.
Naturally, we're going to make use of DBATools to make our lives easier as well. Obviously, the techniques/code here could be shredded to check other systems & services as well, whatever floats your boat.
Code
I'll break the code it down into smaller sections, explaining each as I go. Don't worry, most of the size of this post are the script extracts themselves, not the explainations! If you want the script in its entirity, you can just grab it from github here.
Setting prereqs and collecting our target array of servers.
Install-Module -Name ThreadJob
Install-Module -Name DBATools
Import-module ThreadJob
Import-module DBATools
$TaskName='SQLConnectSweep'
$CMSServer='MYCMSServer'
$CMSGroup='PROD'
$Noformat=$false
$Failuresonly=$false
$throttle = [int]$env:NUMBER_OF_PROCESSORS+1
$threaded = $true
$Output=$null
clear
$serverlist=@()
# Get target servers to check from CMS
If ($CMSGroup)
{
$Serverlist=(Get-DbaRegServer -SqlInstance $CMSServer -Group $CMSGroup | select Servername).Servername
}
# Or If you want just to include an array of servers
# $serverlist='LAPTOP2','LAPTOP2\NAMED1'
# Or pull from an inventory table
# $serverlist=Invoke-Sqlcmd -ServerInstance MyinventoryServer -Database MyinventoryDB -Query "Select servername from MyinventoryTable"
$Totalcount=($Serverlist).count
If ($Totalcount -eq 0){
Write-Host 'No servers to check' -ForegroundColor Yellow
Break
}Here we are just:
Installing (only required once) and Importing the modules that we need (DBATools and ThreadJob)
Giving the collection a variable name ($TaskName) and collecting the number of CPUs+1 for throttling purposes ($Throttle). You can tune this for your system as you desire, the above just gives a (safe) start point.
Set up the variables we need , our CMSServer name ($CMSserver) and CMS Server Group that we want to target our collections against.
Call the Get-DbaRegServer function with our targets to populate the $Serverlist variable with the array of Servernames from our CMS. (I love this function!). I've included a couple of other methods if you so desire, or don't have a CMS*.
Store the number of servers in our array in ($TotalCount) for later use.
If there are no servers to check, exit early.
*Go and confgure one.
The Scriptblock Workload - where we do the heavy lifting.
As we are going to be using Jobs, (threaded or otherwise), we will be passing in the work that we want to do in the form of a script block. Here, we assign $full_instance as the parameter name of which the target server names are passed into later on.
# Script Block of what we actually want to run.
$GetSQLInfo = {
param(
$full_instance
)
try{
# split server and instance
if ($full_instance -like '*\*')
{
$server = $full_instance.substring(0,$full_instance.IndexOf('\'))
$instance = $full_instance.Replace("$server\",'')
$instancename = "MSSQL`$$instance"
}
else
{
$server = $full_instance
$instance = 'MSSQLSERVER'
$instancename= 'MSSQLSERVER'
}
# Extra check for fast-fail
if (Test-Connection -ComputerName $server -Quiet -Count 1) {
$Ispingable=$true
# Check if SQL service started
If( (get-service -ComputerName $server | where Name -eq $instancename).Status -ne 'Stopped' )
{
$IsSQLServiceUp=$true
# Get some data from SQL
$sqlstmt="DECLARE @auth varchar(8)
IF EXISTS (SELECT * FROM sys.dm_exec_connections WHERE auth_scheme='KERBEROS')
SET @auth ='KERBEROS'
ELSE
SET @auth ='NTLM'
SELECT SERVERPROPERTY('ServerName') AS SQLinstance,SERVERPROPERTY('Edition') AS Edition,SERVERPROPERTY('ProductVersion') AS VersionNumber,SERVERPROPERTY('IsHadrEnabled') AS Hadr, @Auth AS AuthScheme,
(select status_desc from sys.dm_server_services where servicename LIKE 'SQL Server Agent%') AS SQLAgentStatus"
try {
$sqldata=Invoke-Dbaquery -SQLInstance $full_instance -Database 'master' -Query $sqlstmt -ErrorAction SilentlyContinue -WarningAction SilentlyContinue
If ($sqldata){
$IsSqlresponding=$true
# Express detect, blank agent data
If ($sqldata.Edition -like '*Express*'){
$sqldata.SQLAgentStatus = $null
}
}
}
catch {
$IsSqlresponding=$false
}
}
Else {
$IsSQLServiceUp=$false
}
# Build status output from vars
$Detail=[PSCustomObject]@{
SQLInstance = $full_instance
IsPingable = $Ispingable
IsSQLServiceUp = $IsSQLServiceUp
IsSqlresponding = $IsSqlresponding}
# Add SQL info if we have any
If ($sqldata) {
$Detail | Add-Member -MemberType NoteProperty "Edition" -Value $sqldata.Edition
$Detail | Add-Member -MemberType NoteProperty "VersionNumber" -Value $sqldata.VersionNumber
$Detail | Add-Member -MemberType NoteProperty "Hadr" -Value $sqldata.Hadr
$Detail | Add-Member -MemberType NoteProperty "Authscheme" -Value $sqldata.AuthScheme
$Detail | Add-Member -MemberType NoteProperty "SQLAgentStatus" -Value $sqldata.SQLAgentStatus
}
}
Else {
$Detail=[PSCustomObject]@{
SQLInstance = $full_instance
IsPingable = $false
}
}
$Detail # Return info
}
# Theres an error somewhere, return this fact.
catch{
$Detail=[PSCustomObject]@{
SQLInstance = "$full_instance - ERROR IN COLLECTION "}
}
}
# End of Script BlockTheres a few lines here*, so here's what we do with the param ($full_instance) passed in.
Check if the target server in ($full_instance) is a SQL named instance by the presence of a backslash (\), and populate the $Server , $instance and $instancename variables accordingly.
Test the server name with a basic ping to check for a fast fail mechanism.
If ok, check to see if the SQL service is not stopped.
If not, run a sql command against the instance using Invoke-dbaquery (it is more reliable with threads than invoke-sqlcmd). If succssful, then store the resultset from this command if successful, store this in the ($Detail) array. This proves that SQL is responding.
If the target is Express edition, blank out the SQLAgentStatus returned value as this is meant to be stopped.
Also, on each stage of the above, set a variable dictating success or failure of that stage.
Build the PS object to be returned ($Detail), populating each property with the variables set on success or failure of each stage above.
If we have SQL information returned also, then add this to the PS object to be returned.
Return the ($Detail) object.
*The truth of the matter is, I wrote a version of this, then decided it was hitting the SQL engine more times that it needed...swore lots at myself and then rewrote it.
Calling the job(s) across our target estate.
Now we want to actally trigger our job calls. Same as previously.
$i=0
ForEach($Server in $serverlist)
{
$i++
while( @(Get-Job -State Running).Count -ge $Throttle)
{
Write-Host "Max concurrency reached...Throttling..." -ForegroundColor Yellow
Start-Sleep -Milliseconds 2000
}
Write-Progress -Activity $TaskName -Status "Starting threads..." -CurrentOperation "$i of $TotalCount Started" -PercentComplete ($i/$TotalCount*100)
# Call a job for the task for the server
if (-not $threaded) {
$ID=(Start-Job -Name $TaskName-$server -ScriptBlock $GetSQLInfo -ArgumentList $server).ID
}
Else {
$ID=(Start-ThreadJob -Name $TaskName-$server -ScriptBlock $GetSQLInfo -ArgumentList $server -ThrottleLimit $Throttle).ID
}
Write-Verbose "Started Job $ID for Target: $Server"
}
clear
Write-Host "Threads started for all targets. Collating." -ForegroundColor Cyan
$completed=@(Get-Job -State Completed).count
while( @(Get-Job -State Running | Where Name -like "$TaskName*" ).Count -gt 0){
$completed=@(Get-Job -State Completed).count
Write-Progress -Activity $TaskName -Status "Completed threads..." -CurrentOperation "$completed of $TotalCount Completed" -PercentComplete ($completed/$TotalCount*100)
Start-Sleep -Milliseconds 2000
}
Write-Progress -Completed -Activity "Done."
# Return results
$Output=Get-Job | Where Name -like "$TaskName*" | Wait-Job | Receive-Job | Select *
$Jobs=Get-Job | Where Name -like "$TaskName*"
Loop through the list of target servers obtained from our CMS server, again throttling by number of Jobs in a Running state.
Also, this time, as we're being friendlier, outputting a progress bar to the screen for the number of Jobs completed vs the Total number of servers to hit (from the number of elements in the $Serverlist array. Hooray, a percentage bar!
Based on the ($threaded config variable at the top), start a threaded job or a normal job, saving the ID of this so we can output a "Servername to Job ID" match - useful to see which server, if any misbehaved. (Verbose display only)
Check how many jobs have completed.
While jobs are still running, write another progress bar of the Completion of jobs, along with a percentage. The beauty of this is that as these are background threads, some will have completed already. Nice.
Grab all results and pop them in the ($output) variable.
Grab the state of the jobs into the ($Jobs) to allow a summary display.
So now we have all of the results of our estate sweep, stored in the ($output) variable.
Displaying the Results of our Connectivity sweep
Which is the bit we are all after really...
How this is going to be used is completely up to you. You may want to just display the raw data, or pass it to another process.
Setting ($Noformat) as true at the top of the script will just bring back the raw data. All there, unmolested. Great.
If that's all that you ever need, then you can just grab the $output | Select line below, and you're done.
If you only want to see failures with naughty servers, make sure ($failuresonly) is true.
If ($Noformat){
If (-not $Failuresonly){
$output | Select SQLInstance, IsPingable, IsSQLServiceUp, IsSQLResponding, Edition, VersionNumber, Hadr, AuthScheme, SQLAgentStatus | Where {$null -ne ($_.SQLInstance)}
}
Else {
foreach ($line in $output){
If(($line.IsPingable -eq $false ) -or ($line.IsSQLServiceUp -eq $False) -or ($line.IsSQLResponding -eq $false) -or ($line.SQLAgentStatus -eq 'Stopped')) {
$line | Select SQLInstance, IsPingable, IsSQLServiceUp, IsSQLResponding, Edition, VersionNumber, Hadr, AuthScheme, SQLAgentStatus | Where {$null -ne ($_.SQLInstance)}
}
}
}
}Colour!
If you want to use this in an interactive session, formatted with colour, then make sure ($NoFormat) is false, and the code will display elements in Red or Green, dependent on findings, makes the entire output a 2 second glance to see if all is well or not. Again, if you only want failures, set ($failuresonly) to true.
All that is being done here outputing the output for each returned server in our output, setting colours on success or failure for each element.
Else
<#Lets present our findings in a pretty way.#>
{
foreach ($line in $output)
{
If ($line.IsPingable -eq $false)
{
Write-Host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable)" -ForegroundColor Red
}
If (($line.IsPingable -eq $True) -and ($line.IsSQLServiceUp -eq $False) )
{
Write-host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable), " -ForegroundColor Green -NoNewline;`
Write-host "SQL Service Up: $($line.IsSQLServiceUp) " -ForegroundColor Red
}
If (($line.IsSQLServiceUp -eq $True) -and ($line.IsSQLResponding -eq $false) )
{
Write-host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable), " -ForegroundColor Green -NoNewline;`
Write-host "SQL Service Up: $($line.IsSQLServiceUp), " -ForegroundColor Green -NoNewline; Write-host "SQL Responding: $($line.IsSQLResponding)" -ForegroundColor Red
}
# SQL Agent down
If (($line.IsSQLResponding -eq $True) -and ($line.SQLAgentStatus -eq 'Stopped'))
{
Write-host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable), " -ForegroundColor Green -NoNewline;`
Write-host "SQL Service Up: $($line.IsSQLServiceUp) " -ForegroundColor Green -NoNewline; Write-host " SQL Responding: $($line.IsSQLResponding), " -ForegroundColor Green -NoNewline;`
Write-Host " SQLAgent Status: $($line.SQLAgentStatus), " -ForegroundColor Red -NoNewline; Write-host " Edition: $($line.Edition), Version: $($line.VersionNumber), Hadr: $($line.hadr), Auth: $($line.AuthScheme)" -ForegroundColor Green
}
If (-not $Failuresonly){
# ALL OK
If (($line.Ispingable -eq $True) -and ($line.IsSQLServiceUp -eq $True) -and ($line.IsSQLResponding -eq $true) -and ('Running' -eq $line.SQLAgentStatus))
{
Write-host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable), " -ForegroundColor Green -NoNewline;`
Write-host "SQL Service Up: $($line.IsSQLServiceUp), " -ForegroundColor Green -NoNewline; Write-host "SQL Responding: $($line.IsSQLResponding), " -ForegroundColor Green -NoNewline;`
Write-Host "SQLAgent Status: $($line.SQLAgentStatus), " -ForegroundColor Green -NoNewline; Write-host "Edition: $($line.Edition), Version: $($line.VersionNumber), Hadr: $($line.hadr), Auth: $($line.AuthScheme)" -ForegroundColor Green
}
If (($line.ConnectSuccess -eq $True) -and ($line.IsPingable -eq $True) -and ($line.IsSQLResponding -eq $true) -and ($null -eq $line.SQLAgentStatus))
{
Write-host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable), " -ForegroundColor Green -NoNewline;`
Write-host "SQL Service Up: $($line.IsSQLServiceUp), " -ForegroundColor Green -NoNewline; Write-host "SQL Responding: $($line.IsSQLResponding), " -ForegroundColor Green -NoNewline;`
Write-Host "SQLAgent Status: N/A, " -ForegroundColor Green -NoNewline; Write-host "Edition: $($line.Edition), Version: $($line.VersionNumber), Hadr: $($line.hadr), Auth: $($line.AuthScheme)" -ForegroundColor Green
}
}
}Optional - Display a little summary
$Jobs=Get-Job
Write-Host "`nJOB SUMMARY: " -ForegroundColor DarkGreen
Write-Host "Target Count: $Totalcount"
Write-Host "Not Completed: "($Jobs | Where State -ne 'Completed').count
Write-Host "Completed: "($Jobs | Where State -eq 'Completed').Count
Write-Host "Success %: "(($Jobs | Where State -eq 'Completed').count/$TotalCount*100)If you want to, display the job totals and success rate. If you don't, you can just strip the code out.
Optional - Display any Failures
# Output anything not marked as completed
If (($Jobs | Where State -ne 'Completed').count -ne 0)
{
$Jobs | Where State -ne 'Completed' | Select Id, Name, State
}As the heading says really...
Now Tidy Up
# Now Tidy Up
Get-Job | Remove-Job -ForceAs your mother told you to always do...and eat your vegetables too.
And that's it, a method to check your server estate in parallel, and provide easy to view output. This can easily be added to a parameterised powershell script, and called by other staff as needed.
Of course, you can always strip out the meaty script block, and run that in serial with a ForEach loop, it's your choice and all good. But that may take a while on large estates, and learning new techniques is always useful.
As always, thanks for reading.
Oddjob Rod
Putting this all togther, you end up with the below (also available from github here):
# Install-Module -Name ThreadJob -Force
# import-module ThreadJob
$TaskName='SQLHeartbeat'
$CMSServer='MYCMSSERVER'
$CMSGroup='PROD'
$Noformat=$false
$Failuresonly=$false
$throttle = [int]$env:NUMBER_OF_PROCESSORS
$threaded = $true
$Output=$null
clear
$serverlist=@()
# Get target servers to check from CMS
If ($CMSGroup)
{
$Serverlist=(Get-DbaRegServer -SqlInstance $CMSServer -Group $CMSGroup | select Servername).Servername
}
# Or If you want just to include an array of servers you can:
#$serverlist='LAPTOP2','LAPTOP2\NAMED1'
# Or pull from an inventory table:
#$serverlist=Invoke-Sqlcmd -ServerInstance MyinventoryServer -Database MyinventoryDB -Query "Select servername from MyinventoryTable"
$Totalcount=($Serverlist).count
If ($Totalcount -eq 0){
Write-Host 'No servers to check' -ForegroundColor Yellow
Break
}
# Script Block of what we actually want to run.
$GetSQLInfo = {
param(
$full_instance
)
try{
# split server and instance
if ($full_instance -like '*\*')
{
$server = $full_instance.substring(0,$full_instance.IndexOf('\'))
$instance = $full_instance.Replace("$server\",'')
$instancename = "MSSQL`$$instance"
}
else
{
$server = $full_instance
$instance = 'MSSQLSERVER'
$instancename= 'MSSQLSERVER'
}
# Extra check for fast-fail
if (Test-Connection -ComputerName $server -Quiet -Count 1) {
$Ispingable=$true
# Check if SQL service started
If( (get-service -ComputerName $server | where Name -eq $instancename).Status -ne 'Stopped' )
{
$IsSQLServiceUp=$true
# Get some data from SQL
$sqlstmt="DECLARE @auth varchar(8)
IF EXISTS (SELECT * FROM sys.dm_exec_connections WHERE auth_scheme='KERBEROS')
SET @auth ='KERBEROS'
ELSE
SET @auth ='NTLM'
SELECT SERVERPROPERTY('ServerName') AS SQLinstance,SERVERPROPERTY('Edition') AS Edition,SERVERPROPERTY('ProductVersion') AS VersionNumber,SERVERPROPERTY('IsHadrEnabled') AS Hadr, @Auth AS AuthScheme,
(select status_desc from sys.dm_server_services where servicename LIKE 'SQL Server Agent%') AS SQLAgentStatus"
try {
$sqldata=Invoke-Dbaquery -SQLInstance $full_instance -Database 'master' -Query $sqlstmt -ErrorAction SilentlyContinue -WarningAction SilentlyContinue
If ($sqldata){
$IsSqlresponding=$true
# Express detect, blank agent data
If ($sqldata.Edition -like '*Express*'){
$sqldata.SQLAgentStatus = $null
}
}
}
catch {
$IsSqlresponding=$false
}
}
Else {
$IsSQLServiceUp=$false
}
# Build status output from vars
$Detail=[PSCustomObject]@{
SQLInstance = $full_instance
IsPingable = $Ispingable
IsSQLServiceUp = $IsSQLServiceUp
IsSqlresponding = $IsSqlresponding}
# Add SQL info if we have any
If ($sqldata) {
$Detail | Add-Member -MemberType NoteProperty "Edition" -Value $sqldata.Edition
$Detail | Add-Member -MemberType NoteProperty "VersionNumber" -Value $sqldata.VersionNumber
$Detail | Add-Member -MemberType NoteProperty "Hadr" -Value $sqldata.Hadr
$Detail | Add-Member -MemberType NoteProperty "Authscheme" -Value $sqldata.AuthScheme
$Detail | Add-Member -MemberType NoteProperty "SQLAgentStatus" -Value $sqldata.SQLAgentStatus
}
}
Else {
$Detail=[PSCustomObject]@{
SQLInstance = $full_instance
IsPingable = $false
}
}
$Detail # Return info
}
# Theres an error somewhere, return this fact.
catch{
$Detail=[PSCustomObject]@{
SQLInstance = "$full_instance - ERROR IN COLLECTION "}
}
}
# End of Script Block
$i=0
ForEach($Server in $serverlist)
{
$i++
while( @(Get-Job -State Running).Count -ge $Throttle)
{
Write-Host "Max concurrency reached...Throttling..." -ForegroundColor Yellow
Start-Sleep -Milliseconds 2000
}
Write-Progress -Activity $TaskName -Status "Starting threads..." -CurrentOperation "$i of $TotalCount Started" -PercentComplete ($i/$TotalCount*100)
# Call a job for the task for the server Threaded or normal depending on param
if (-not $threaded) {
$ID=(Start-Job -Name $TaskName-$server -ScriptBlock $GetSQLInfo -ArgumentList $server).ID
}
Else {
$ID=(Start-ThreadJob -Name $TaskName-$server -ScriptBlock $GetSQLInfo -ArgumentList $server -ThrottleLimit $Throttle).ID
}
Write-Verbose "Started Job $ID for Target: $Server"
}
clear
Write-Host "Threads started for all targets. Collating." -ForegroundColor Cyan
$completed=@(Get-Job -State Completed).count
while( @(Get-Job -State Running | Where Name -like "$TaskName*" ).Count -gt 0){
$completed=@(Get-Job -State Completed).count
Write-Progress -Activity $TaskName -Status "Completed threads..." -CurrentOperation "$completed of $TotalCount Completed" -PercentComplete ($completed/$TotalCount*100)
Start-Sleep -Milliseconds 500
}
Write-Progress -Completed -Activity "Done."
# Return results
$Output=Get-Job | Where Name -like "$TaskName*" | Wait-Job | Receive-Job | Select *
$Jobs=Get-Job | Where Name -like "$TaskName*"
If ($Noformat){
If (-not $Failuresonly){
$output | Select SQLInstance, IsPingable, IsSQLServiceUp, IsSQLResponding, Edition, VersionNumber, Hadr, AuthScheme, SQLAgentStatus | Where {$null -ne ($_.SQLInstance)}
}
Else {
foreach ($line in $output){
If(($line.IsPingable -eq $false ) -or ($line.IsSQLServiceUp -eq $False) -or ($line.IsSQLResponding -eq $false) -or ($line.SQLAgentStatus -eq 'Stopped')) {
$line | Select SQLInstance, IsPingable, IsSQLServiceUp, IsSQLResponding, Edition, VersionNumber, Hadr, AuthScheme, SQLAgentStatus | Where {$null -ne ($_.SQLInstance)}
}
}
}
}
Else
# Lets present our findings in a pretty way
{
foreach ($line in $output)
{
If ($line.IsPingable -eq $false)
{
Write-Host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable)" -ForegroundColor Red
}
If (($line.IsPingable -eq $True) -and ($line.IsSQLServiceUp -eq $False) )
{
Write-host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable), " -ForegroundColor Green -NoNewline;`
Write-host "SQL Service Up: $($line.IsSQLServiceUp) " -ForegroundColor Red
}
If (($line.IsSQLServiceUp -eq $True) -and ($line.IsSQLResponding -eq $false) )
{
Write-host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable), " -ForegroundColor Green -NoNewline;`
Write-host "SQL Service Up: $($line.IsSQLServiceUp), " -ForegroundColor Green -NoNewline; Write-host "SQL Responding: $($line.IsSQLResponding)" -ForegroundColor Red
}
# SQL Agent down
If (($line.IsSQLResponding -eq $True) -and ($line.SQLAgentStatus -eq 'Stopped'))
{
Write-host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable), " -ForegroundColor Green -NoNewline;`
Write-host "SQL Service Up: $($line.IsSQLServiceUp) " -ForegroundColor Green -NoNewline; Write-host " SQL Responding: $($line.IsSQLResponding), " -ForegroundColor Green -NoNewline;`
Write-Host " SQLAgent Status: $($line.SQLAgentStatus), " -ForegroundColor Red -NoNewline; Write-host " Edition: $($line.Edition), Version: $($line.VersionNumber), Hadr: $($line.hadr), Auth: $($line.AuthScheme)" -ForegroundColor Green
}
If (-not $Failuresonly){
# ALL OK
If (($line.Ispingable -eq $True) -and ($line.IsSQLServiceUp -eq $True) -and ($line.IsSQLResponding -eq $true) -and ('Running' -eq $line.SQLAgentStatus))
{
Write-host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable), " -ForegroundColor Green -NoNewline;`
Write-host "SQL Service Up: $($line.IsSQLServiceUp), " -ForegroundColor Green -NoNewline; Write-host "SQL Responding: $($line.IsSQLResponding), " -ForegroundColor Green -NoNewline;`
Write-Host "SQLAgent Status: $($line.SQLAgentStatus), " -ForegroundColor Green -NoNewline; Write-host "Edition: $($line.Edition), Version: $($line.VersionNumber), Hadr: $($line.hadr), Auth: $($line.AuthScheme)" -ForegroundColor Green
}
If (($line.ConnectSuccess -eq $True) -and ($line.IsPingable -eq $True) -and ($line.IsSQLResponding -eq $true) -and ($null -eq $line.SQLAgentStatus))
{
Write-host "Server: $($line.SQLInstance) " -ForegroundColor Cyan -NoNewline; Write-Host "Is Pingable: $($line.IsPingable), " -ForegroundColor Green -NoNewline;`
Write-host "SQL Service Up: $($line.IsSQLServiceUp), " -ForegroundColor Green -NoNewline; Write-host "SQL Responding: $($line.IsSQLResponding), " -ForegroundColor Green -NoNewline;`
Write-Host "SQLAgent Status: N/A, " -ForegroundColor Green -NoNewline; Write-host "Edition: $($line.Edition), Version: $($line.VersionNumber), Hadr: $($line.hadr), Auth: $($line.AuthScheme)" -ForegroundColor Green
}
}
}
Write-Host "`nJOB SUMMARY: " -ForegroundColor DarkGreen
Write-Host "Target Count: $Totalcount"
Write-Host "Not Completed: "($Jobs | Where State -ne 'Completed').count
Write-Host "Completed: "($Jobs | Where State -eq 'Completed').Count
Write-Host "Success %: "(($Jobs | Where State -eq 'Completed').count/$TotalCount*100)
<#Output anything not marked as completed.#>
If (($Jobs | Where State -eq 'Completed').count -ne 0)
{
$Jobs | Where State -ne 'Completed' | Select Id, Name, State
}
}
<#Now Tidy Up#>
Get-Job | Remove-Job -Force