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
