Taken with instagram
Pop-upView Separately

Taken with instagram

Taking Deklan to his first NBA game! (Taken with instagram)
Pop-upView Separately

Taking Deklan to his first NBA game! (Taken with instagram)

The first batch of Browning Ale is about to be brewed! (Taken with instagram)
Pop-upView Separately

The first batch of Browning Ale is about to be brewed! (Taken with instagram)

Taken with Instagram at Highlands Ranch, CO
Pop-upView Separately

Taken with Instagram at Highlands Ranch, CO

Kickstarter - Pebble: E-Paper Watch for iPhone and Android

I’ve already got mine ordered. This is the watch of the future!

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

Using Powershell to monitor servers: Part 1

I was looking for a good Powershell script to do some server monitoring for us here at work and I ran across this post: http://benchmarkitconsulting.com/colin-stasiuk/2010/03/25/monitoring-with-powershell-pings-services-and-websites.

While that script did most of things we wanted to do it didn’t do everything we wanted such as checking for drive space issues whether they be global (checking the free space on every C: drive on every server) or specific (checking the free space on a particular network drive that must never drop below 20GB). That said, I did some additions to the original script to support these features as well as a few other things such as checking the nightly OLAP cube builds for Project Server 2010. Here’s the result:

# 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)

# location of Project Server 2010 DB for OLAP cube build data
$projectserver = "projectserverhere"
$projectDB = "projectserverdbhere"

# adjust email variables as needed
$emailFrom = "outgoing@email.com"
$emailTo = "to@email.com"
$subject = "subject goes here, i.e Server check failures"
$smtpServer = "mail.server.goes.here"

# adjust text (priority) variables as needed
$TextFrom = "outgoing@email.com"
$TextTo = "SMSaddress@email.com"
$Textsubject = ""  # intentionally left blank since this isn't needed in a text message
$smtpServer = "mail.server.goes.here"

# # of GB's that should be available on all server C: drives
$Cdrivethreshold = 1

# set variables - DO NOT TOUCH
$rundatetime = Get-Date # set date/time of current script run
$today = $rundatetime.ToShortDateString()  # get just today's date for later use
if ($today -ne [environment]::GetEnvironmentVariable('FirstRunDate','User'))
	{
		[environment]::SetEnvironmentVariable('RunCounter',0,'User') # set environment variable back to zero if this isn't the first run of the day
	}
$body = "The following failures have been reported at " + $RunDateTime.ToShortTimeString() + " on " + $RunDateTime.ToShortDateString() + ": "  # preload the body variable with the initial text
$Textbody = ""  # clear any previous text message bodies
$FailureCount = 0  # set our failure count to zero to start

###################		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)		###################


###################		PING, SERVICE, AND DISK SPACE CHECKS	(start)		###################
 
$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
 
	$Reply = Test-Connection -ComputerName $ServerName -Count 1 -Quiet # ping server to see if it's alive
     
	if ($Reply –eq “True”)  # if ping comes back ok then check disk space/services
        {
            $servername
            $disks = Get-WmiObject -ComputerName $ServerName -Class Win32_LogicalDisk -Filter "DriveType = 3";  # Get local drive info
           	foreach($disk in $disks)  # check every local drive in the table
               	{
                    [float]$freespace = $disk.FreeSpace;
                    $freeSpaceGB = [Math]::Round($freespace / 1073741824, 1);
                    if(($disk.DeviceID = "C:") -and ($freeSpaceGB -lt $Cdrivethreshold))  # if the server's C: drive is under the threshold add info to email
                        {
                            $Body = $Body + "`r`r" + "Free Disk Space for the C: drive on " + $ServerName + " is under " + $Cdrivethreshold + "GB.  This should be addressed immediately."  # add any problems found to the email report body
                            $FailureCount = 1  # set fault flag to trigger email
                            $sql = "INSERT INTO dbo.ServiceCheckHistoryAudit(ServiceCheckID, ServiceState) SELECT 'C: drive low - $servername', '0'"  # write failure data back to SQL for reporting purposes
                            Invoke-Sqlcmd2 -serverinstance $Server -database $Database -query $sql
						}
						
            $condisk = "server=$Server;database=$Database;Integrated Security=sspi" 		
            $cmddisk = "SELECT ServiceName, Notes FROM dbo.vwActiveDiskChecks WHERE ServerName = '$ServerName'"
            	$dadisk = new-object System.Data.SqlClient.SqlDataAdapter ($cmddisk, $condisk)
            	$dtdisk = new-object System.Data.DataTable
            	$dadisk.fill($dtdisk) | out-null  # get a list of all the servers who need disk space checks and put them in a table
         
                     foreach ($DiskCheck in $dtdisk)  # check every server in the table
                            {

                            $DiskToCheck = $DiskCheck.ServiceName
                        	$SpaceThresholdInGB = $DiskCheck.Notes

                            	$disks = Get-WmiObject -ComputerName $ServerName -Class Win32_LogicalDisk -Filter "DriveType = 3";  # Get local drive info
                                
                            	foreach($disk in $disks)  # check every local drive in the table
                                	{
                                		$deviceID = $disk.DeviceID;
                                		[float]$freespace = $disk.FreeSpace;
                                		$freeSpaceGB = [Math]::Round($freespace / 1073741824, 1);
                                        
                                        if(($freeSpaceGB -lt $SpaceThresholdInGB) -and ($deviceID -eq $DiskToCheck))  # only return drives under threshold
                                            {
                                                $sql = "INSERT INTO dbo.ServiceCheckHistoryAudit(ServiceCheckID, ServiceState) SELECT '$ServiceCheckID', '0'"  # write failure data back to SQL for reporting purposes
                                                Invoke-Sqlcmd2 -serverinstance $Server -database $Database -query $sql
                                                $Body = $Body + "`r`r" + "Free Disk Space for the " + $deviceID + " drive on " + $ServerName + " is under " + $SpaceThresholdInGB + "GB.  This should be addressed immediately."  # add any problems found to the email report body
                                                $FailureCount = 1  # set fault flag to trigger email
                                                IF ($Priority -eq 1)  # if disk is flagged as priority then immediately send a text alert
                                                {
                                                    	$Textbody = "Free Disk Space for the " + $deviceID + " drive on " + $ServerName + " is under " + $SpaceThresholdInGB + "GB."
                                                        $smtp = new-object Net.Mail.SmtpClient($smtpServer)
                                                    	$smtp.Send($TextFrom,$TextTo,$Textsubject,$Textbody)  # send failure text
                                                }
                                            }
                                     }
                            }
    		$cmd2 = "SELECT ServiceCheckID, ServiceName FROM dbo.vwActiveServiceChecks WHERE ServerName = '$ServerName'"
    		$da2 = new-object System.Data.SqlClient.SqlDataAdapter ($cmd2, $con)
    		$dt2 = new-object System.Data.DataTable
    		$da2.fill($dt2) | out-null  # get a list of all servers who need service checks and put them in a table
 
		foreach ($svc in $dt2)  # check every server in the table
		{
 
			$ServiceCheckID = $svc.ServiceCheckID
			$ServiceName = $svc.ServiceName
 
			$colOS = get-wmiobject win32_service -filter "name='$ServiceName'" -computerName $ServerName  # get a list of services installed on the server
 
			if ($colos)
			{
				foreach($objComp in $colOS)  # check every service to be checked against the list of services installed
				{
					$State = $objComp.State
 
					if ($objComp.State -eq "Running")
					{
						$sql = "INSERT INTO dbo.ServiceCheckHistoryAudit(ServiceCheckID, ServiceState) SELECT '$ServiceCheckID', '1'"  # write success data back to SQL for reporting purposes
					}
					else
					{
						$sql = "INSERT INTO dbo.ServiceCheckHistoryAudit(ServiceCheckID, ServiceState) SELECT '$ServiceCheckID', '0'"  # write failure data back to SQL for reporting purposes
					    $body = $body + "`r`r" + "The service " + $ServiceName + " was found on "+ $ServerName + " but it is not running.  Please restart the service."  # add any problems found to the email report body
						$FailureCount = 1  # set fault flag to trigger email
                        IF ($Priority -eq 1)  # if service is flagged as priority then immediately send a text alert
                        {
                           	$Textbody = "The service " + $ServiceName + " was found on "+ $ServerName + " but it is not running."
                            $smtp = new-object Net.Mail.SmtpClient($smtpServer)
                           	$smtp.Send($TextFrom,$TextTo,$Textsubject,$Textbody)  # send failure text
                        }
					}
					Invoke-Sqlcmd2 -serverinstance $Server -database $Database -query $sql
				}
			}
			else
			{
				$FailureCount = 1  # set fault flag to trigger email
				$Body = $Body + "`r`r" + "The service " + $ServiceName + " was not found on "+ $ServerName + ". Please check the service name in the config table."	 # if the service name can't be found then update email report body to notify user
 
				$sql = "INSERT INTO dbo.ServiceCheckHistoryAudit(ServiceCheckID, ServiceState) SELECT '$ServiceCheckID', '0'"  # write failure data back to SQL for reporting purposes
				Invoke-Sqlcmd2 -serverinstance $Server -database $Database -query $sql		
 
			}
		}       
	}
	else
	{
		$FailureCount = 1  # set fault flag to trigger email
		$Body = $Body + "`r`r" + "The ping of " + $ServerName + " failed." # add ping failures to the email report body and then add a failure for any services to be check on that server
   		$sql = "INSERT INTO dbo.ServiceCheckHistoryAudit(ServiceCheckID, ServiceState) SELECT 'Ping fail - $servername', '0'"  # write failure data back to SQL for reporting purposes
		Invoke-Sqlcmd2 -serverinstance $Server -database $Database -query $sql	
        
 
		$cmd3 = "SELECT ServiceCheckID, ServiceName FROM dbo.vwActiveServiceChecks WHERE ServerName = '$ServerName'"
		$da3 = new-object System.Data.SqlClient.SqlDataAdapter ($cmd3, $con)
		$dt3 = new-object System.Data.DataTable
		$da3.fill($dt3) | out-null  #  get a list of all servers who need service checks
 
		foreach ($svc2 in $dt3)  # check every server in the table
		{
			$ServiceCheckID = $svc2.ServiceCheckID
			$ServiceName = $svc2.ServiceName
			$sql = "INSERT INTO dbo.ServiceCheckHistoryAudit(ServiceCheckID, ServiceState) SELECT '$ServiceCheckID', '0'"  # write failure data back to SQL for reporting purposes
			Invoke-Sqlcmd2 -serverinstance $Server -database $Database -query $sql	
 
			$FailureCount = 1  # set fault flag to trigger email
			$body = $body + "`r`r" + "Service Check Failure: " + $ServerName + " (" + $ServiceName + ")"  # add any services meant to be checked on server that failed ping to the email report body
 
		}
 
	}
 
	$Reply = ""  # clear ping variable for next run
 
  }
 
###################		PING, SERVICE, AND DISK SPACE CHECKS	(End)		###################
 
###################		WEBSITE CHECKS	(start)			###################
 
$conWeb = "server=$Server;database=$Database;Integrated Security=sspi"
$cmdWeb = "SELECT ServiceCheckID, ServiceName, Notes, Priority FROM dbo.vwActiveServiceChecks_Websites"
 
$daWeb = new-object System.Data.SqlClient.SqlDataAdapter ($cmdWeb, $conWeb)
$dtWeb = new-object System.Data.DataTable
$daWeb.fill($dtWeb) | out-null  # get the list of websites that needs to be checked and put them in a table
 
foreach ($Website in $dtWeb)  # check each website in the table
{
	$ServiceCheckID = $WebSite.ServiceCheckID
	$ServiceName = $Website.ServiceName
	$Notes = $Website.Notes
    $Priority = $Website.Priority
 
	trap  # trap any errors and move on
	{
		continue
	}
 
	$webclient= new-object System.Net.WebClient
	$webclient.Credentials = [System.Net.CredentialCache]::DefaultCredentials  # pass user credentials based on login used to run the script
	if($webclient.Proxy -ne $null)
	{
        	$webclient.Proxy.Credentials =
                [System.Net.CredentialCache]::DefaultNetworkCredentials
	}
 
	$Output = $webclient.DownloadString($ServiceName)  # grabs a string of all the text on the webpage
 
		IF ($Output -like $Notes)  # checks to see if the string of website text contains the search string listed in DB
		{
			$sql = "INSERT INTO dbo.ServiceCheckHistoryAudit(ServiceCheckID, ServiceState) SELECT '$ServiceCheckID', '1'"  # write success data back to SQL for reporting purposes
			Invoke-Sqlcmd2 -serverinstance $Server -database $Database -query $sql
		}
		ELSE
		{
			$FailureCount = 1
			$Body = $Body + "`r`r" + "The following website or search string could not be resolved: " + $ServiceName + " (" + $Notes + ")"  # add website failure to email report body
            IF ($Priority -eq 1)  # if website is flagged as priority then immediately send a text alert
            {
                	$Textbody = "Website " + $ServiceName + " could not be resolved or the search string could not be found"
                    $smtp = new-object Net.Mail.SmtpClient($smtpServer)
                	$smtp.Send($TextFrom,$TextTo,$Textsubject,$Textbody)  # send failure text
            }
 
			$sql = "INSERT INTO dbo.ServiceCheckHistoryAudit(ServiceCheckID, ServiceState) SELECT '$ServiceCheckID', '0'"  # write failure data back to SQL for reporting purposes
			Invoke-Sqlcmd2 -serverinstance $Server -database $Database -query $sql
		}
 
	$Output = ""  # clear web text variable for next run
 
}
 
###################		WEBSITE CHECKS	(End)			###################

###################		PROJECT SERVER CUBE BUILD CHECKS	(Start)			###################

$runcount = [environment]::GetEnvironmentVariable('RunCounter','User') # get the current run count and store it for later use
if ($runcount -eq 0)
	{
		[environment]::SetEnvironmentVariable('RunCounter',1,'User') # set run count variable to 1 so we know to skip this section until the next day since this is only checked daily
		[environment]::SetEnvironmentVariable('FirstRunDate',$today,'User')  # set environment variable to check later for first run of day or not

		$concube = "server=$projectserver;database=$projectDB;Integrated Security=sspi"  # connect to Project Server DB
		$cmdcube = "SELECT WADMIN_CUBE_OLAP_SERVER, WADMIN_CUBE_NAME FROM ProjectServer_Published.dbo.MSP_WEB_ADMIN_CUBE WHERE WADMIN_IS_DATABASE_ENABLED = 1" # check project server DB to see which cubes are active
		$dacube = new-object System.Data.SqlClient.SqlDataAdapter ($cmdcube, $concube)
		$dtcube = new-object System.Data.DataTable
		$dacube.fill($dtcube) | out-null  # get a list of all the OLAP DB's currently active and put them in a table

		foreach ($cubeDB in $dtcube)  # check each OLAP DB in the table
		{
		$olapserver = $cubeDB.WADMIN_CUBE_OLAP_SERVER
		$dbname = $cubeDB.WADMIN_CUBE_NAME

		[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
		$server = New-Object Microsoft.AnalysisServices.Server
		$server.connect("$olapserver")
		$database=$server.databases
		$cubedb=$database["$dbname"]

		$Cubes=New-object Microsoft.AnalysisServices.Cube
		$Cubes=$cubedb.cubes
		$cubearray = $Cubes|select Name, lastprocessed  # get a list of all the cubes in the OLAP DB

		foreach ($date in $cubearray)  # check each cube in the list
		   {
		        $cubename = $date.Name
		        $lastdate = $date.lastprocessed
		        $lastdate = $lastdate.ToShortDateString()  # get just the date of the last cube build
		        $today = $RunDate.ToShortDateString()  # get just today's date
		                    
		        if ($lastdate -ne $today)  # check to see if the last cube build date is not equal to today's date, i.e. the build failed
		        {
		           $Body = $Body + "`r`r" + "The cube " + $cubename +  " on " + $dbname + " failed to build properly and needs to be reprocessed immediately."  # add any problems found to the email report body
		           $FailureCount = 1  # set fault flag to trigger email
		           $Textbody = "The cube " + $cubename + " on " + $dbname + " failed to build properly."
		           $smtp = new-object Net.Mail.SmtpClient($smtpServer)
		           $smtp.Send($TextFrom,$TextTo,$Textsubject,$Textbody)  # send failure text since this is a critical notification
		        }            
		   }
		}
	}
###################		PROJECT SERVER CUBE BUILD CHECKS	(End)			###################
 
###################		MAIL	(start)			###################
 
if ($FailureCount -eq "1")  # check to see if failure email is needed
{
	$smtp = new-object Net.Mail.SmtpClient($smtpServer)
	$smtp.Send($emailFrom,$emailTo,$subject,$body)  # send failure email
 
}
 
###################		MAIL	(End)			###################

Keep in mind that this is not just a “cut and paste” to get it working as you much not only set several variables to your environment but also need to create the proper SQL pieces. First you need to create a new database for us to use in the script. I named mine MonitoringDB for instance (as you can see in the script below in the USE command). Once you do that you’ll need to create some tables. Here’s the SQL script to do that for you to save some time: 

USE [MonitoringDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ServerList](
	[ServerID] [int] IDENTITY(1,1) NOT NULL,
	[ServerName] [nvarchar](255) NOT NULL,
	[Environment] [nvarchar](10) NULL,
	[CreateDateTime] [datetime] NOT NULL,
	[LastModDateTime] [datetime] NOT NULL,
	[ActiveInd] [bit] NOT NULL,
	[Documentum] [bit] NOT NULL,
 CONSTRAINT [PK_ServerList] PRIMARY KEY CLUSTERED 
(
	[ServerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ServerList] ADD  CONSTRAINT [DF_ServerList_CreateDateTime]  DEFAULT (getdate()) FOR [CreateDateTime]
GO

ALTER TABLE [dbo].[ServerList] ADD  CONSTRAINT [DF_ServerList_LastModDateTime]  DEFAULT (getdate()) FOR [LastModDateTime]
GO

ALTER TABLE [dbo].[ServerList] ADD  CONSTRAINT [DF_ServerList_ActiveInd]  DEFAULT ((1)) FOR [ActiveInd]
GO

ALTER TABLE [dbo].[ServerList] ADD  CONSTRAINT [DF_ServerList_Documentum]  DEFAULT ((0)) FOR [Documentum]
GO

CREATE TABLE [dbo].[ServiceCheck](
	[ServiceCheckID] [int] IDENTITY(1,1) NOT NULL,
	[ServerID] [int] NOT NULL,
	[ServiceName] [nvarchar](255) NOT NULL,
	[ServiceType] [nvarchar](10) NULL,
	[Notes] [nvarchar](500) NULL,
	[ActiveInd] [bit] NOT NULL,
	[CreateDateTime] [datetime] NOT NULL,
	[LastModDateTime] [datetime] NOT NULL,
	[Priority] [bit] NULL,
 CONSTRAINT [PK_ServiceCheck_1] PRIMARY KEY CLUSTERED 
(
	[ServiceCheckID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ServiceCheck] ADD  CONSTRAINT [DF_ServiceCheck_ActiveInd]  DEFAULT ((1)) FOR [ActiveInd]
GO

ALTER TABLE [dbo].[ServiceCheck] ADD  CONSTRAINT [DF_ServiceCheck_CreateDateTime]  DEFAULT (getdate()) FOR [CreateDateTime]
GO

ALTER TABLE [dbo].[ServiceCheck] ADD  CONSTRAINT [DF_ServiceCheck_LastModDateTime]  DEFAULT (getdate()) FOR [LastModDateTime]
GO

CREATE TABLE [dbo].[ServiceCheckHistoryAudit](
	[ServiceCheckHistoryAuditID] [bigint] IDENTITY(1,1) NOT NULL,
	[ServiceCheckID] [nvarchar](50) NULL,
	[AuditDateTime] [datetime] NOT NULL,
	[ServiceState] [bit] NOT NULL,
 CONSTRAINT [PK_ServiceCheckHistoryAudit] PRIMARY KEY CLUSTERED 
(
	[ServiceCheckHistoryAuditID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ServiceCheckHistoryAudit] ADD  CONSTRAINT [DF_ServiceCheckHistoryAudit_AuditDateTime]  DEFAULT (getdate()) FOR [AuditDateTime]
GO

Now that you have the tables you’ll need to populate them with some information. I’d recommend checking out the original blog post I got the idea from HERE for both a script to scrape a list of domain servers and the proper way to populate the ServiceCheck table. The one thing you won’t find documented there is how to add an entry to check a specific drive on a specific server for a specific free space threshold since that’s something I’ve added personally. The way you configure that check in the ServiceCheck table is by adding in the the following:

  • ServerID = server number from the ServerList table that you want to check
  • ServiceName = the drive letter you want to check, i.e. D:
  • ServiceType = DISK
  • Notes = number of GB’s of free space you want at a minimum, i.e. 20

You’ll also see that I’ve added another field into the ServiceCheck table which is Priority. This field is a bit field so it’s a 1 or 0 (true or false) as to whether that service check is critical enough to warrant a text message alert in addition to the email alert it would normally send if the service check failed. For example, I use this to make sure that I get a text message anytime a business-critical website goes down just in case I’m not at my desk when the email alert comes, i.e. I’m at lunch and not monitoring my email per se.

If you find this useful let me know. Also, if you come up with a cool addition or see a better way to do something let me know as I’d love to expand more on this (and I’ll update this post with your changes and give you credit). And of course, should have questions about use or why I did something the way I did in the script please feel free to inquire.

Next up… a script to get a list of all the windows update/hotfixes on the same list of servers used in the script above, put that list into a DB, and run a report against to show me what servers are missing needed updates. Stay tuned!

EDIT #1: Script updated to fix a small error in code - 10/20/11

EDIT #2: Script updated to add some missing SQL updates to the audit table - 11/4/11

EDIT #3: Added SQL scripts to build the tables needed since I added to the original tables - 11/8/11

EDIT #4: Added some environmental variables to hold data across sessions to keep the OLAP cube build checks to once per day (since they only build once a day) so they aren’t adding overhead on every run - 11/10/11

[Flash 9 is required to listen to audio.]
  • 0 Plays
Download External Audio

Just thought I’d share one of my favorite new songs… enjoy!

Modern day Seinfeld?

I just finished watching all the seasons of “It’s Always Sunny In Philadelphia” and I have to say… it’s one of the funniest shows on TV today!  If you’re not watching this show then you should be, plain and simple.  It’s most definitely the Seinfeld of today for many reasons:

  • The show centers around a tight group of friends who are so selfish they’ll screw one another over to get what they want and not think twice about it.
  • The main group of friends is comprised of three guys and a girl.
  • The writers use current events and pop culture references throughout the dialog.
  • As a show it will age well and continue to remain funny well into the future.
  • It’s just downright funny as hell!

If you loved Seinfeld then you’ll love “It’s Always Sunny In Philadelphia”!  I cannot recommend it enough people.

A photo of our boy Deklan taken by his grandma (who he’s currently staying with so Sabrina and I can focus on our move to Denver at the end of October).  Is he cute as hell or what?
Pop-upView Separately

A photo of our boy Deklan taken by his grandma (who he’s currently staying with so Sabrina and I can focus on our move to Denver at the end of October).  Is he cute as hell or what?