Rotating Header Image

Monitoring With Powershell (pings, services, and websites)

So it’s no secret that I’ve been having an affair on TSQL. I’ve been seeing PowerShell behind her back and I gotta tell ya even after the initial excitement of a new language I’m still loving it. Let’s talk monitoring. I started with a requirement of:

  • Pinging a list of production level servers
  • Checking specific services on those servers
  • Checking website availability for a list of websites
  • All information and history needs to be tracked in a database for SLA analysis.

So where am I going to get my list of servers from?

# REMOVE FILE FOR NEW EXECUTION RESULTS
del F:\MonitoringDB\AllDomainServers.txt 
 
# SEARCH CRITERIA COMPUTERS THAT HAVE WINDOWS*SERVER AS AN OS
$SearchCategory = "computer"
$SearchOS = "Windows*Server*"
 
$Domain = New-Object System.DirectoryServices.DirectoryEntry
 
$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $Domain
$objSearcher.Filter = ("OperatingSystem=$SearchOS")
 
$ServerName = "name"
 
foreach ($i in $ServerName)
{
	$objSearcher.PropertiesToLoad.Add($i)
}
 
$Results = $objSearcher.FindAll()
 
foreach ($ResultRow in $Results)
{
	$Computer = $ResultRow.Properties;
	$Computer.name |Out-File F:\MonitoringDB\AllDomainServers.txt -Append
}

Now let’s create a database called MonitoringDB on a SQL Server 2008 Instance and run the following script to create the tables needed:

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,
 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
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,
 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] [int] NOT 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

OK now that we have our list of servers and a database to store them in, import your list into the ServerList table. If you’re having troubles with this step an easy way to do this is just to:

  • Right click on the MonitoringDB database, Tasks, Import Data.
  • Choose a flatfile source and browse to your AllDomainServers.txt file
  • Import it into the ServerList table making sure to map the only column in the txt file to the ServerName column

OK so let’s pick a couple of development SQL Servers instances and update the active indicator (ActiveInd) so that those servers are the only ones that are going to be used in this post. Your script should look something like:

UPDATE	dbo.ServerList
SET	ActiveInd = 0
WHERE	ServerName NOT IN ('Server1', 'Server2', 'Server3')

OK so let’s first populate some services that we want to check. Hmmmmmm how are we going to know the service names? Don’t worry I wont leave ya hanging… Here is yet another PowerShell script that you can use to get the list of services and their current status:

$WMI = Get-WmiObject -Class win32_service -computer "Server1"
 
	foreach($service in $WMI)
	{
		foreach($srv in $serviceArray)
		{
			$service.name + "`t" + $service.state
 
		}
	}

So Cherrypick a few services that you’d like to check from each of the servers that you left the ActiveInd to 1. I’m a DBA so if I had to pick two I’ll use MSSQLSERVER and SQLSERVERAGENT.

So let’s get the ServerIDs of our testing servers by using the following script:

SELECT	ServerID, ServerName
FROM	ServerList
WHERE	ActiveInd = 1

OK for my example my ServerIDs are 70, 79, and 363.

Now we’re going to open the ServiecCheck Table and populate 2 rows per test server… something like this:

ServerID: 70
ServiceName: MSSQLSERVER
ServiceType: SERVICE

ServerID: 70
ServiceName: SQLSERVERAGENT
ServiceType: SERVICE

ServerID: 79
ServiceName: MSSQL$DEVE
ServiceType: SERVICE

ServerID: 79
ServiceName: SQLAGENT$DEVE
ServiceType: SERVICE

ServerID: 363
ServiceName: MSSQLSERVER
ServiceType: SERVICE

ServerID: 363
ServiceName: SQLSERVERAGENT
ServiceType: SERVICE

You’ll notice that my ServerID 79 is a named instance so the SQL Server service and the SQL Server Agent service are named a bit differently. You’ll also notice that I’ve populated the ServiceType filed with “Service” this is to distinguish between services and websites. You’ll see what I do with this field later.

Let’s throw in a website availability checks as well.

ServerID: 0
ServiceName: http://BenchmarkITConsulting.com
ServiceType: WEBSITE
Notes: *Colin Stasiuk*

OK so a little different setup then the service check. There is no “server” to check on a website availability check so I set the ServerID to 0. The service name is the http:// address of the site you’re looking to check. The service type is “WEBSITE” and the Notes column for a website is used to hold the search criteria for what the check looks for on the page to validate it’s availability.

Now we’re going to create a couple views to filter out the services and servers that we want to check by using the following script:

CREATE VIEW [dbo].[vwServerListActive] AS
SELECT	ServerID, ServerName
FROM	dbo.ServerList
WHERE	ActiveInd = 1
GO
CREATE VIEW [dbo].[vwActiveServiceChecks] AS
SELECT	A.ServiceCheckID, A.ServerID, B.ServerName, A.ServiceName, A.ActiveInd, A.CreateDateTime, A.LastModDateTime
FROM	dbo.ServiceCheck A INNER JOIN
		dbo.vwServerListActive B ON A.ServerID = B.ServerID
WHERE	A.ActiveInd = 1 AND
		A.ServiceType = 'SERVICE'
GO
CREATE VIEW [dbo].[vwActiveServiceChecks_Websites] AS
SELECT	A.ServiceCheckID, A.ServiceName, A.Notes, A.ActiveInd, A.CreateDateTime, A.LastModDateTime
FROM	dbo.ServiceCheck A
WHERE	A.ActiveInd = 1 AND
		A.ServiceType = 'WEBSITE'

OK finally we’re going to get to the “bacon” of this blog post…. the more I think I should of done a multi-part post but if you’ve stuck with me this long we’ve just got one more script to go…

So here we go… This is the script that you want to schedule to run as often as you feel necessary (I have mine running every 5 minutes)… first I’ll show the script and then break it down a bit.

$Server = "MySQL2008Server"
$Database = "MonitoringDB"
 
$emailFrom = "blah@blah.com"
$emailTo = "DBA@blah.com"
$subject = "Service Check Failure"
$smtpServer = "SMTPServer.blah.com"
 
$FailureCount = 0
 
###################		WINDOWS SERVICE CHECKS	(start)		###################
 
$con = "server=$Server;database=$Database;Integrated Security=sspi"
$cmd = "SELECT DISTINCT ServerName FROM dbo.vwActiveServiceChecks"
 
  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
 
  $dt = new-object System.Data.DataTable
 
  $da.fill($dt) | out-null
 
  foreach ($srv in $dt)
  {
	$ServerName = $srv.ServerName
 
	$ping = new-object System.Net.NetworkInformation.Ping
	$Reply = $ping.send($ServerName)
 
	if ($Reply.status –eq “Success”)
	{
		$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
 
		foreach ($svc in $dt2)
		{
 
			$ServiceCheckID = $svc.ServiceCheckID
			$ServiceName = $svc.ServiceName
 
			$colOS = get-wmiobject win32_service -filter "name='$ServiceName'" -computerName $ServerName 
 
			if ($colos)
			{
				foreach($objComp in $colOS)
				{
					$State = $objComp.State
 
					if ($objComp.State -eq "Running")
					{
						$sql = "INSERT INTO dbo.ServiceCheckHistoryAudit(ServiceCheckID, ServiceState) SELECT '$ServiceCheckID', '1'"
					}
					else
					{
						$sql = "INSERT INTO dbo.ServiceCheckHistoryAudit(ServiceCheckID, ServiceState) SELECT '$ServiceCheckID', '0'"
					        $body = $body + "`r`r" + "Service Check Failure: " + $ServerName + " (" + $ServiceName + ")"
						$FailureCount = 1
					}
					invoke-sqlcmd -serverinstance $Server -database $Database -query $sql
				}
			}
			else
			{
				$FailureCount = 1
				$Body = $Body + "`r`r" + "Bad Service Name: " + $ServerName + " (" + $ServiceName + ")"	
 
				$sql = "INSERT INTO dbo.ServiceCheckHistoryAudit(ServiceCheckID, ServiceState) SELECT '$ServiceCheckID', '0'"
				invoke-sqlcmd -serverinstance $Server -database $Database -query $sql		
 
			}
		}
	}
	else
	{
		$FailureCount = 1
		$Body = $Body + "`r`r" + "Failed Ping: " + $ServerName 
 
		$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
 
		foreach ($svc2 in $dt3)
		{
			$ServiceCheckID = $svc2.ServiceCheckID
			$ServiceName = $svc2.ServiceName
			$sql = "INSERT INTO dbo.ServiceCheckHistoryAudit(ServiceCheckID, ServiceState) SELECT '$ServiceCheckID', '0'"
			invoke-sqlcmd -serverinstance $Server -database $Database -query $sql	
 
			$FailureCount = 1
			$body = $body + "`r`r" + "Service Check Failure: " + $ServerName + " (" + $ServiceName + ")"
 
		}
 
	}
 
	$Reply = ""
 
  }
 
###################		WINDOWS SERVICE CHECKS	(End)		###################
 
###################		WEBSITE CHECKS	(start)			###################
 
$conWeb = "server=$Server;database=$Database;Integrated Security=sspi"
$cmdWeb = "SELECT ServiceCheckID, ServiceName, Notes FROM dbo.vwActiveServiceChecks_Websites"
 
$daWeb = new-object System.Data.SqlClient.SqlDataAdapter ($cmdWeb, $conWeb)
$dtWeb = new-object System.Data.DataTable
$daWeb.fill($dtWeb) | out-null
 
foreach ($Website in $dtWeb)
{
	$ServiceCheckID = $WebSite.ServiceCheckID
	$ServiceName = $Website.ServiceName
	$Notes = $Website.Notes
 
	trap
	{
		continue
	}
 
	$webclient= new-object System.Net.WebClient
	$webclient.Credentials = [System.Net.CredentialCache]::DefaultCredentials
	if($webclient.Proxy -ne $null)
	{
        	$webclient.Proxy.Credentials =
                [System.Net.CredentialCache]::DefaultNetworkCredentials
	}
 
	$Output = $webclient.DownloadString($ServiceName)
 
		IF ($Output -like $Notes)
		{
			$sql = "INSERT INTO dbo.ServiceCheckHistoryAudit(ServiceCheckID, ServiceState) SELECT '$ServiceCheckID', '1'"
			invoke-sqlcmd -serverinstance $Server -database $Database -query $sql
		}
		ELSE
		{
			$FailureCount = 1
			$Body = $Body + "`r`r" + "Bad Website or Search String: " + $ServiceName + " (" + $Notes + ")"
 
			$sql = "INSERT INTO dbo.ServiceCheckHistoryAudit(ServiceCheckID, ServiceState) SELECT '$ServiceCheckID', '0'"
			invoke-sqlcmd -serverinstance $Server -database $Database -query $sql
		}
 
	$Output = ""
 
}
 
###################		WEBSITE CHECKS	(End)			###################
 
###################		MAIL	(start)			###################
 
if ($FailureCount -eq "1")
{
	$smtp = new-object Net.Mail.SmtpClient($smtpServer)
	$smtp.Send($emailFrom,$emailTo,$subject,$body)
 
}
 
###################		MAIL	(End)			###################

OK let’s step through this a bit to see what we’re doing…

*** There are some variables at the top that will need to be populated with information from your environment ***

  • First we connect to the database and get the list of all servers that have services that need to be checked
  • For each of those severs we first ping to see if we can even connect to the server
  • If we can’t ping then for all the services for that server are marked as failed and the body of our failure email is appended to
  • If we can ping then we pull the services to check for that server
  • For each service in that list we check the status… if it’s running we audit accordingly if it’s not then we audit accordingly and the body of our failure email is appended to
  • Next… For each website we look at the source and compare it to our search criteria in the notes column
  • If it can find the search criteria we audit accordingly… if it can’t find the search string or can’t connect at all then we audit accordingly and the body of our failure email is appended to
  • Finally after everything is done the failure email (if there are any failures) get sent out.

A quick SELECT from the ServiceCheckHistoryAudit table will give you a look as to what passed and what failed by the ServiceState column.

I setup my job to run every 5 minutes and I’m currently looking at over 200 services and websites and my average duration of my job is 1 minute and 20 seconds (not bad in my opinion)…. anyways this is very much a v1 of this script/process and I’m hoping that people can not only find a good use for it but also look to take the script and add to it, optimize it, etc.  I’m still a rookie with PowerShell so any and all help and suggestions would be greatly appreciated

EDIT:  I also have a stored proc that runs monthly to “clean up” the data on days when a service has a clean record for the full 24 hour period… but I’ve already taken up enough of your time so we’ll save that for another post another day LOL

Enjoy!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

16 Comments

  1. Buck Woody says:

    Collin – great job. A couple of comments – take them only for what they are worth.

    I like the idea of a “driver” file. You might want to use an XML file instead, which gives you the ability to have a hiearchy of objects (such as certain servers, certain Instances on that server, and even certain databases, tables and so on on that Instance) to work with. You can keep it “flat” at first, which allows just a standard list of servers, or you can expand it later to do more granular work.

    If you make a function out of the various repetitive tasks this script is doing then you could “pipe” or send the results of an action there – everything from notifications to logging and so on. the advantage there is that the code is smaller and easier to debug.

    I did a series on a PowerShell “framework” script that combines these actions into functions, so you could for instance do some work, log it in the event logs, pop up a notification, send to e-mail, web and so on all on one line. You can see one version of it here: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=259

  2. Lee Holmes says:

    Nice job. I like the idea of keeping it all in a shared DB so that you can get a report on this stuff at any time.

    A few ideas:
    – Some of the things you are scripting are built into PowerShell directly. For example, Test-Connection rather than ping, Send-MailMessage, and the [ADSI] and [ADSISearcher] type shortcuts instead of using the .NET Framework directly.
    – I’d imagine that a bunch of your elapsed time is due to processing this all sequentially. Give PowerShell Jobs a look:

    http://powershell.labs.oreilly.com/ch28.html#improve_performance_large_wmi_operatios
    http://powershell.labs.oreilly.com/ch01.html#invoke-long-running-command

    Lee Holmes [MSFT]
    Windows PowerShell Development

  3. Great Comments!!

    Just as I suspected (as a PowerShell Rookie) that there would be some significant improvements that can be made.

    Thanks Buck and Lee… I’m going to have to do some reading/playing/learning before I can apply your suggestions to the script but I look forward to posting a followup blog when I have something “workable”.

    thanks again!

  4. […] otherwise contentious DBA’s to wander astray. Colin Stasiuk [Blog/Twitter] admitted as much in a recent blog post : “…it’s no secret that I’ve been having an affair on TSQL. I’ve been seeing PowerShell […]

  5. Mark Adams says:

    On your server list script I am missing many servers. would this script find VM servers?

  6. Mark – Just some quick questions off the bat to hopefully help you out here:

    1) Are these “missing” servers on the same domain?

    2) The $SearchOS variable is by default looking for : “Windows*Server*” could these servers be filtered out potentially? If you remove the search criteria do these missing servers show up?

    Thanks

    ~ Colin

  7. Mark Adams says:

    Thanks for the quick response

    Yes, the servers in question are in different domains. As someone who does not know powershell how can I add the different domains to search for servers?

  8. Sam Greene says:

    Thanks for the script. I’m hoping to add to this with some specifics for failover clusters. I’ll be sure to send my code back to you if it works out.

  9. Fantastic… looking forward to your tweaks and improvements!! 🙂

    Thanks for reading!

  10. Matt Gordon says:

    Thanks for info, its been a great help. I have modded it only slightly as I wished to do ‘ping only’ checks on a few IP nodes. I also publish the logged data into SharePoint enabling the use of PKI’s. This means I can have an automated traffic light system for things like internet access, email connectors, WAN links, etc… which the end users love. Also a similar system can be viewed by techs, but showing more detail (i.e. the results of all checks rather than those end user may be interested in). Wouldn’t mind having a look at your clean up script as I’m struggling to get mine working.

  11. Mike Browning says:

    Sorry to bring an old post back to life but I wanted to say that this script is exactly what I’ve been looking for to do some basic monitoring for me. I do have two quick questions though:

    1. Did you ever post the clean-up script? I couldn’t find it here.
    2. Is there a way to add drive space checking based on a threshold to the script?

    Thanks again!

  12. In response to my last post, I’ve added drive space monitoring and a few other little additions to your script and posted a copy of it on my site here:

    http://www.g33kdaddy.com/post/11670337032/using-powershell-to-monitor-servers-part-1

    I’d love to hear any feedback. Thanks again for the great script!

  13. Tom says:

    hi ,
    first of off Nice script, it helped me allot
    But i’m wondering if someone can help me to ad a backup check into the script.
    I’m using windows back , and i want to check my servers if the backup was succesfull or not.

    I can check the eventlog as windows backup creates an entry if the backup is succesfull or not.

    Hope someone can help me

  14. Tom,

    Adding in a check that looks for a success/fail entry in the event log wouldn’t be that hard given the “Get-EventLog” command. Hopefully that points you in the right direction. If you need more guidance let me know.

  15. Lets start with the new Notes app in iOS 9. Notes is getting a huge overhaul in iOS 9–which is good, because the app has hardly been updated since the first iPhone. Now in Notes in iOS 9 you’ll be able to easily create a checklist right in a note

  16. Google may be putting those extra pixel to good use when you strap the Pixel XL into a Daydream View headset and start giving VR a spin, but they don’t change the day-to-day experience much.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.