G33k Daddy

Using Powershell to monitor servers: Part 2

As a follow-up to my previous post HERE, we also needed a script to go hit all the servers listed in the monitoring database flagged as active and pull back a list of all the Windows Updates, hotfixes, and security updates that had been installed as well as when they were installed. I created the Powershell script below to do pull that data from each server and dump the data into a SQL table in the monitoring DB for reporting as needed.

Here’s the SQL script to create the additional table you need in your monitoring DB from the last post:

USE [MonitoringDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ServerUpdateHistory](
	[Server] [nvarchar](50) NULL,
	[Update Name] [nvarchar](150) NULL,
	[Installed On] [nvarchar](20) NULL,
	[Run Date] [nvarchar](20) NULL,
	[KB Name] [nvarchar](50) NULL
) ON [PRIMARY]

GO

And here’s the Powershell code used to populate that table using the list of servers you have your ServerList table:

# location of the config database for the script
$Server = "configserverhere"
$Database = "configdbhere"
# any configuration adjustments must be done in SQL using tables ServerList (list of servers and "to be checked" flag) and 
# ServiceCheck (list of services, websites, and disks to be checked based on server ID from ServerList table)

# set variables - DO NOT TOUCH
$Date = Get-Date
$RunDate = $Date.ToShortDateString()

###################		SQL CONNECT FUNCTION	(start)		###################
# this removes the need to install the SQL extensions for PowerShell

function Invoke-Sqlcmd2
{
    param(
    [string]$ServerInstance,
    [string]$Database,
    [string]$Query,
    [Int32]$QueryTimeout=30
    )

    $conn=new-object System.Data.SqlClient.SQLConnection
    $conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $ServerInstance,$Database
    $conn.Open()
    $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
    $cmd.CommandTimeout=$QueryTimeout
    $ds=New-Object system.Data.DataSet
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
    [void]$da.fill($ds)
    $conn.Close()
    $ds.Tables[0]

}

###################		SQL CONNECT FUNCTION	(end)		###################

# clear DB table for next run
$sql = "DELETE FROM $database.[dbo].[ServerUpdateHistory]"
Invoke-Sqlcmd2 -serverinstance $Server -database $Database -query $sql


$con = "server=$Server;database=$Database;Integrated Security=sspi"
$cmd = "SELECT DISTINCT ServerName FROM dbo.ServerList WHERE ActiveInd = 1"
  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
  $dt = new-object System.Data.DataTable
  $da.fill($dt) | out-null  # get a list of all the server names and put them in a table
 
  foreach ($srv in $dt)  # check every server in the table
  {
    $ServerName = $srv.ServerName  
    $ServerName
    $updates= Get-Hotfix -ComputerName $ServerName | where-object {$_.description -ne ''} | select description,hotfixid,installedon  # pull the list of updates installed from the server
        foreach ($u in $updates)
            {
                $name = $u.description
                if($name.Substring($name.Length-1,1) -ne ")")  # check to see if the KB number is already attached to the name
                    {
                        $name = $name + " (" + $u.hotfixid + ")"  # if not, attach KB number to name
                    }
                else
                    {
                        $name = $u.description  # if not null and already have the KB number then set variable
                    }
                $date = $u.installedon
                if(!$date)  # check to see if install date is null
                    {
                        $date = "Not listed"  # if null then set as "not listed"
                    }
                else
                    {
                        $date = $date.ToShortDateString()  #  if not null then set variable
                    } 
				$kbname = $u.hotfixid
                $sql = "INSERT INTO dbo.ServerUpdateHistory(Server, [Update Name], [Installed On], [Run Date], [KB Name]) SELECT '$ServerName', '$name', '$date', '$RunDate', '$kbname'"  # add data to table in monitoring DB
                Invoke-Sqlcmd2 -serverinstance $Server -database $Database -query $sql

                
            }
   }

Enjoy and let me know if you have an ideas to enhance it or any questions.

EDIT #1: Updated post to include SQL script for creation of the table needed to store the scripts results - 11/8/11

EDIT #2: Update script to use a quicker method for dropping updates with a NULL name value; added a new column for just the KB number to make searching easier - 11/18/11