Powershell Script:: Check Possible Security update or Server reboot Failure:: SCCM


Powershell script to check last reboot status, Remote desktop connection and/or ping response status

The script will retrieve the list of servers that not rebooted in last 15 days (assuming the last windows update happened a month or 2 back), check the ping status, to verify the server still online, and check RDP connection (to verify server stuck at reboot)

I usually run this during the Windows update maintenance window (last one hour), to retrieve exact list of servers that really not updated or stuck at reboot

#———————- Script Start ———————————–

$SQLServer = “SCCM_SQLServers” #use Server\Instance for named SQL instances!
$SQLDBName = “DB_name” #normally it is same as site name!
$lastreboot = “15” # enumnrate Servers list number of date since last reboot
$SqlQuery = “SELECT TOP (100) PERCENT dbo.v_R_System.Name0, dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0, DATEDIFF(Day,
dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0, GETDATE()) AS [ActiveDays], dbo.v_GS_OPERATING_SYSTEM.Caption0
FROM dbo.v_GS_OPERATING_SYSTEM INNER JOIN
dbo.v_R_System ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System.ResourceID
WHERE (DATEDIFF(Day, dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0, GETDATE()) > $lastreboot) AND (dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE ‘%Windows%’)
ORDER BY [ActiveDays]

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server = $SQLServer; Database = $SQLDBName; Integrated Security = True”

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

$datas = $DataSet.Tables[0]
$datas

$Body =

<p style=’font-family:Century Gothic’>Hi Team,</p>

<p><span style=’font-size:10.0pt;font-family:Century Gothic’>Please check below server, these servers not rebooted after patching, review and confirm server updated and reboot.</span></p>

<table border=’1′>
<tr>
<th>Server Name</th>
<th>Operating System</th>
<th>Last Boot time</th>
<th>Not rebooted for (days)</th>
<th>Ping Status</th>
<th>RDP Status</th>
</tr>

foreach ($data in $datas)
{
$computer = $data.Name0
$Lastboot = $data.LastBootUpTime0
$OS = $data.Caption0
$activeDays = $data.ActiveDays

$ping = Get-WmiObject Win32_PingStatus -filter “Address=’$computer'”
if($ping.StatusCode -ne 0)
{
start-sleep 1

$ping1 = Get-WmiObject Win32_PingStatus -filter “Address=’$computer'”
if($ping1.StatusCode -ne 0){

#foreach ($pings in $ping1) {$IP = $pings.ProtocolAddress}
$PingStatus= “Not Responding PING”
}

}

else {$PingStatus= “Responding to PING”}
$socket = New-Object Net.Sockets.TcpClient($Computer, 3389)
if ($socket.Connected) {$rdp = “RDP Connected”}
else {$rdp = “RDP Not Connected”}
$socket.Close()
if ($PingStatus -like “*Not Responding*”-and $rdp -like “*Not Connected*”){
$Body +=

<tr>
<td>$computer</td>
<td>$OS</td>
<td>$Lastboot</td>
<td>$activeDays</td>
<td><span style=’color:red’><b>$PingStatus</b></span></td>
<td><span style=’color:red’><b>$rdp</b></span></td>
</tr>

}
elseif ($rdp -like “*Not Connected*”){
$Body +=

<tr>
<td>$computer</td>
<td>$OS</td>
<td>$Lastboot</td>
<td>$activeDays</td>
<td>$PingStatus</td>
<td><span style=’color:red’><b>$rdp</b></span></td>
</tr>

}
elseif ($PingStatus -like “*Not Responding*”){
$Body +=

<tr>
<td>$computer</td>
<td>$OS</td>
<td>$Lastboot</td>
<td>$activeDays</td>
<td><span style=’color:red’><b>$PingStatus</b></span></td>
<td>$rdp</td>
</tr>

}

else {
$Body +=

<tr>
<td>$computer</td>
<td>$OS</td>
<td>$Lastboot</td>
<td><span style=’color:red’><b>$activeDays</b></span></td>
<td>$PingStatus</td>
<td>$rdp</td>
</tr>

}

}

$Body += ”
</table>
<p><span style=’font-size:10.0pt;font-family:Century Gothic’>Thank you, </span></p>
<p><span style=’font-size:10.0pt;font-family:Century Gothic’>Windows Operations Team </span></p>”

$recipients =”admin@domain.com”
$smtpserver = “email.domain.com”
send-mailmessage -from “admin@domain.com” -to $recipients -subject “Possible update or Server reboot Failure” -body $Body -smtpServer $smtpserver -BodyAsHtml -Priority high

#———————- Script End———————————–

Note: Script might be immature

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s