Morning report of services using PowerShell

Why use this utility

Making sure the services that are needed to run are actually running. The key here is knowing what happens while you are out of the office relaxing.

How it works

I test for all services. Sadly I don’t log past states of services, just the current start. Someday, this is the thing I would change on this one. I launch the PowerShell script from the windows scheduler because I don’t like one server directly talk to another server. Then I created a file listing out all servers I want to test. The script starts by check and logging the status of each service while storing it in an email to be sent.

Here is the sample server name file:

v1 c1
v1 c2
v2 pd
v2
W1
W2
W3

For format here is server <space> instance

Here is the breakdown of the code:

All this code is PowerShell. The first part of the code is to read the config file:

$ServerName = Get-Content "H:\__08 Tools\powershell\databaseservers.txt"

foreach ($SRV in $ServerName)
{
$Server,$Instance = $SRV.split(' ')

The next part is to test the services, This code test Oracle and SQL

$Results = Get-service -computername $Server -name "Task Scheduler" | Where-Object {$_.status -eq "Running"} |select status
if ($Results.Status -eq "Running")
{$Resultora.TaskSched = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultora.TaskSched = "N"}
else
{$Resultora.TaskSched = "-"}
$Resultora2.TaskSched = $Resultora.TaskSched

Each service has a block like this one above. The states I am looking for is “Y”, “N” and otherwise resulting in “-”

The email block was gotten from dbatools – the community’s sql powershell module If you have not used this, please give them a try I have learned a lot from them, this is a must checkout library.

Full code “SQLServerStatus.PS1”

function Send-HTMLEmail {
#Requires -Version 2.0
[CmdletBinding()]
Param
([Parameter(Mandatory=$True,
Position = 0,
ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true,
HelpMessage="Please enter the Inputobject")]
$InputObject,
[Parameter(Mandatory=$True,
Position = 1,
ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true,
HelpMessage="Please enter the Subject")]
[String]$Subject,
[Parameter(Mandatory=$False,
Position = 2,
HelpMessage="Please enter the To address")]
[String[]]$To = "DBA@XX.GOV",
[String]$From = "NoReply@XX.GOV",
[String]$CSS,
[String]$SmtpServer ="REPLACE WITH YOUR IP ADDRESS FOR MAIL SERVER"
)#End Param

if (!$CSS)
{
$CSS = @"
Please download the code from the link rather than copy it from here since the code does not show up here.
"@
}#End if

$HTMLDetails = @{
Title = $Subject
Head = $CSS
}

$Splat = @{
To =$To
Body ="$($InputObject | ConvertTo-Html @HTMLDetails)"
Subject =$Subject
SmtpServer =$SmtpServer
From =$From
BodyAsHtml =$True
}
Send-MailMessage @Splat

}#Send-HTMLEmail

 

clear
#Get-service -computername vssq32 -displayname "*SQL*"

###Get-service -computername VSOB20 -displayname "SQL*"

#Display all SQL Server Services which are in stopped state.
###$stat = ""
###$stat = Get-service -computername svot01 -name "Task*" | Where-Object {$_.status -eq "Running"} |select status
###$stat

#Stop above service.
#Stop-Service -Name Sqlserveragent

$Arrayora = @()
$Arrayora2= @()
$Arraysql = @()
$Results
$ServiceInst = ""
$ServerName = Get-Content "H:\__08 Tools\powershell\databaseservers.txt"

foreach ($SRV in $ServerName)
{
$Server,$Instance = $SRV.split(' ')
$Resultora = ""
$Resultora2 = ""

if ($Server -like "SVOT*") #Oracle Servers
{

$Resultora = "" | Select server,ping,TaskSched, OraCons,OraMTS,OraListener,OraOtmain,OraOttrain, OraOttest
$Resultora2= "" | Select server,ping,TaskSched, OraCons,OraMTS,OraListener,OraOtmain,OraOttrain, OraOttest

switch ($server)
{
"SVOT02"
{
$Resultora.server = $server+" - ott1"
$Resultora2.server = $server+" - ott2"
if (test-Connection -ComputerName $Server -Count 2 -Quiet )
{
$Resultora.ping = "Y"
$Resultora2.ping = "Y"
}
else
{
$Resiltora.ping = "N"
}

$Results = Get-service -computername $Server -name "Task Scheduler" | Where-Object {$_.status -eq "Running"} |select status
if ($Results.Status -eq "Running")
{$Resultora.TaskSched = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultora.TaskSched = "N"}
else
{$Resultora.TaskSched = "-"}
$Resultora2.TaskSched = $Resultora.TaskSched

$Results = Get-service -computername $Server -name "OracleDBConsoleotmain" | Where-Object {$_.status -eq "Running"} |select status
if ($Results.Status -eq "Running")
{$Resultora.OraCons = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultora.OraCons = "N"}
else
{$Resultora.OraCons = "-"}

$Results = Get-service -computername $Server -name "OracleDBConsoleotrain" | Where-Object {$_.status -eq "Running"} |select status
if ($Results.Status -eq "Running")
{$Resultora2.OraCons = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultora2.OraCons = "N"}
else
{$Resultora2.OraCons = "-"}

$Results = Get-service -computername $Server -name "OracleMTSRecoveryService" | Where-Object {$_.status -eq "Running"} |select status
if ($Results.Status -eq "Running")
{$Resultora.OraMTS = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultora.OraMTS = "N"}
else
{$Resultora.OraMTS = "-"}
$Resultora2.OraMTS = $Resultora.OraMTS

$Results = Get-service -computername $Server -name "OracleOraDb11g_home1TNSListener" | Where-Object {$_.status -eq "Running"} |select status
if ($Results.Status -eq "Running")
{$Resultora.OraListener = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultora.OraListener = "N"}
else
{$Resultora.OraListener = "-"}
$Resultora2.OraListener = $Resultora.OraListener

$Results = Get-service -computername $Server -name "OracleServiceotmain" | Where-Object {$_.status -eq "Running"} |select status
if ($Results.Status -eq "Running")
{$Resultora.OraOttest = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultora.OraOttest = "N"}
else
{$Resultora.OraOttest = "-"}

$Results = Get-service -computername $Server -name "OracleServiceottrain" | Where-Object {$_.status -eq "Running"} |select status
if ($Results.Status -eq "Running")
{$Resultora2.OraOttrain = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultora2.OraOttrain = "N"}
else
{$Resultora2.OraOttrain = "-"}

break
}
"SV1"
{
$Resultora.server = $server+" - otmain"
$Resultora.ping = "Y"
if (test-Connection -ComputerName $Server -Count 2 -Quiet )
{
$Resultora.ping = "Y"
}
else
{
$Resiltora.ping = "N"
}

$Results = Get-service -computername $Server -name "Task Scheduler" | Where-Object {$_.status -eq "Running"} |select status
if ($Results.Status -eq "Running")
{$Resultora.TaskSched = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultora.TaskSched = "N"}
else
{$Resultora.TaskSched = "-"}

$Results = Get-service -computername $Server -name "OracleDBConsoleotmain" | Where-Object {$_.status -eq "Running"} |select status
if ($Results.Status -eq "Running")
{$Resultora.OraCons = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultora.OraCons = "N"}
else
{$Result.OraCons = "-"}

$Results = Get-service -computername $Server -name "OracleMTSRecoveryService" | Where-Object {$_.status -eq "Running"} |select status
if ($Results.Status -eq "Running")
{$Resultora.OraMTS = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultora.OraMTS = "N"}
else
{$Resultora.OraMTS = "-"}

$Results = Get-service -computername $Server -name "OracleOraDb11g_home1TNSListener" | Where-Object {$_.status -eq "Running"} |select status
if ($Results.Status -eq "Running")
{$Resultora.OraListener = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultora.OraListener = "N"}
else
{$Resultora.OraListener = "-"}

$Results = Get-service -computername $Server -name "OracleServiceotmain" | Where-Object {$_.status -eq "Running"} |select status
if ($Results.Status -eq "Running")
{$Resultora.OraOtmain = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultora.OraOtmain = "N"}
else
{$Resultora.OraOtmain = "-"}
}
}
}
else
{
$Resultsql = "" | Select server,ping,TaskSched, SQLServer, SQLAgent, SQLFullTxt, SQLBrowser, SQLVSS, SQLInegration, SQLAnalysis, SQLReport, SQLTelemetry, SQLSSASTelemetry
#, SQLSSISTelemetry
$Resultsql.server = $SRV
if (test-Connection -ComputerName $Server -Count 2 -Quiet )
{
$Resultsql.ping = "Y"
}
else
{
$Resiltsql.ping = "N"
}

$Results = Get-service -computername $Server -name "Task Scheduler" | Where-Object {$_.status -eq "Running"} |select status
if ($Results.Status -eq "Running")
{$Resultsql.TaskSched = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultsql.TaskSched = "N"}
else
{$Resultsql.TaskSched = "-"}

#sql Server
if (![string]::IsNullOrEmpty($Instance))
{$ServiceInst = -join("MSSQL", '$', $Instance)
}
else
{$ServiceInst = "MSSQLSERVER"}

$Results = Get-service -computername $Server -name $ServiceInst |select status
#-displayname 'SQL Server (*' |select status
if ($Results.Status -eq "Running")
{$Resultsql.SQLServer = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultsql.SQLServer = "N"}
else
{$Resultsql.SQLServer = "-"}

#sql agent
if (![string]::IsNullOrEmpty($Instance))
{$ServiceInst = -join("SQLAgent", '$', $Instance)
}
else
{$ServiceInst = "MSSQLSERVER"}

$Results = Get-service -computername $Server -name $ServiceInst |select status
#-displayname 'SQL Server Agent (*' |select status
if ($Results.Status -eq "Running")
{$Resultsql.SQLAgent = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultsql.SQLAgent = "N"}
else
{$Resultsql.SQLAgent = "-"}

#sql Full Text
$Results = Get-service -computername $Server -displayname 'SQL Full-text*' |select status
if ($Results.Status -eq "Running")
{$Resultsql.SQLFullTxt = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultsql.SQLFullTxt = "N"}
else
{$Resultsql.SQLFullTxt = "-"}

#sql Browser
$Results = Get-service -computername $Server -displayname 'SQL Server Browser*' |select status
if ($Results.Status -eq "Running")
{$Resultsql.SQLBrowser = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultsql.SQLBrowser = "N"}
else
{$Resultsql.SQLBrowser = "-"}

#sql VSS Writer
$Results = Get-service -computername $Server -displayname 'SQL Server VSS*' |select status
if ($Results.Status -eq "Running")
{$Resultsql.SQLVSS = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultsql.SQLVSS = "N"}
else
{$Resultsql.SQLVSS = "-"}

#sql Integration
$Results = Get-service -computername $Server -displayname 'SQL Server Integration Services 1*' |select status
if ($Results.Status -eq "Running")
{$Resultsql.SQLInegration = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Result.SQLInegration = "N"}
else
{$Resultsql.SQLInegration = "-"}

#sql Analysis
$Results = Get-service -computername $Server -displayname 'SQL Server Analysis Services (*' |select status
if ($Results.Status -eq "Running")
{$Resultsql.SQLAnalysis = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultsql.SQLAnalysis = "N"}
else
{$Resultsql.SQLAnalysis = "-"}

#sql Report
$Results = Get-service -computername $Server -displayname 'SQL Server Reporting Services*' |select status
if ($Results.Status -eq "Running")
{$Resultsql.SQLReport = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultsql.SQLReport = "N"}
else
{$Resultsql.SQLReport = "-"}

#SQL Telemetry
if (![string]::IsNullOrEmpty($Instance))
{$ServiceInst = -join("SQLTELEMETRY", '$', $Instance)
}
else
{$ServiceInst = "SQLTELEMETRY"}
$Results = Get-service -computername $Server -name $ServiceInst |select status
if ($Results.Status -eq "Running")
{$Resultsql.SQLTelemetry = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultsql.SQLTelemetry = "N"}
else
{$Resultsql.SQLTelemetry = "-"}

#SQL SSASTelemetry
$Results = Get-service -computername $Server -displayname 'SQL Server Analysis Services CEIP*' |select status
if ($Results.Status -eq "Running")
{$Resultsql.SQLSSASTelemetry = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultsql.SQLSSASTelemetry = "N"}
else
{$Resultsql.SQLSSASTelemetry = "-"}

#SQL SSISTelemetry
<# if (![string]::IsNullOrEmpty($Instance))
{$ServiceInst = -join("SQLTELEMETRY", '$', $Instance)
}
else
{$ServiceInst = "SQLTELEMETRY"}

$Results = Get-service -computername $Server -name $ServiceInst
#-displayname 'SQL Server Integration Services CEIP*' |select status
if ($Results.Status -eq "Running")
{$Resultsql.SQLSSISTelemetry = "Y" }
elseif ($Results.Status -eq "Stopped")
{$Resultsql.SQLSSISTelemetry = "N"}
else
{$Resultsql.SQLSSISTelemetry = "-"}
#>
#sql DTC

}

Write-Host $Server
write-host $Arrayora
write-host $Arraysql

switch ($server)
{
svot01
{$Arrayora += $Resultora
break}
svot02
{$Arrayora += $Resultora
$Arrayora += $Resultora2
break}
default
{$Arraysql += $Resultsql
break}
}

}

# Column: *
# Condition: Any property with value equals to $False
# Query: N/A
# Value Fore Color: Red
# Value Back Color: N/A
# Row Fore Color: N/A
# Row Back Color: N/A

# Flag Columns: 'SN', 'Server'
# Flag Columns Fore Color: Cyan
# Flag Columns Back Color: N/A
# ----------------------------------------------------------------------------
$Arraysql | Write-PSObject -MatchMethod Exact -Column * -Value "N" -ValueForeColor Red -HostWindowWidth 350;
Send-HTMLEmail -InputObject ($Arrayora | Write-PSObject -MatchMethod Exact -Column * -Value "N" -ValueForeColor Red -HostWindowWidth 350) -Subject "Server Services"

$Arrayora | Format-Table *
$Arraysql | Format-Table *

Send-HTMLEmail -InputObject ($Arraysql | select 'server','ping', 'tasksched', 'SQLServer','SQLAgent', 'SQLFullTxt', 'SQLBrowser', 'SQLVSS', 'SQLInegration', 'SQLAnalysis','SQLReport', 'SQLSATelemtry') -Subject "SQL Server Services Running"