Tag Archives: SQL connection

Ping Results to Database

I updated my script to write the results to a database to allow for easier manipulation of the data through queries, instead of having to traverse a file, linearly.  The database design is very simple, consisting of only two tables, and can be easily expanded to contain more information about the device.   The bold fields in the tables are required fields.  There is a 1-to-many relationship between the device and the ping results, since each result can only be for one device, but each device can have many results.

Ping Results Database
Ping Results Database

The full script can be seen at the bottom of the post.  The script starts a job running the ping script block 4 times, sleeping for 14 seconds in between.  It is not an exact science here, but the idea is that the script can be scheduled using Windows Task Scheduler to run every minute and will ping all the machine on first starting and about every 15 seconds to make 4 times in a minute.

The script block creates a new SQL Client Connection object and opens a connection to the database, using the following code:

$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=IT-SERVICES\SQLEXPRESS;database=PingResults;User ID=username;Password=pass;"
$sqlConnection.Open()

Note that the username and password have been changed.

A command object is then created, where the only thing we need to set is the command text (what we want to execute on the SQL server), which in our is a SQL statement to retrieve the device ID and name for only devices that have been marked as active.  We need both as we are pinging the devices by its name and when inserting we need to be able to link the result to the correct device, which as noted above in the database design is reference by the foreign key (FK) DeviceId in the PingResult table.  Once the command text is set we execute the command by calling ExecuteReader().

#Create a command object
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = "Select DeviceId, Name from [PingResults].[dbo].[Device] where IsActive=1"
 
#Execute the Command
$sqlReader = $sqlCommand.ExecuteReader()

The returned results are then looped through, pinging each machine in turn and formatting the results into the proper format for a SQL insert statement. Since we cannot insert null into a tinyint column we set the response time to live and response time to -1 if the returned result is null or empty. -1 is chosen since it has no practical meaning in this context (i.e. you can’t have a negative response time).

$sqlInsertQuery += "(" + $deviceId + ",'" + $RunDate +"'," + $status +"," + $timeout + ","+$ttl+","+ $rttl +",'"+$ri+"',"+$RT+"),"

The $sqlInsertQuery is initialized to store the start of the insert SQL statement and then each row to be inserted is formatted and appended to the end of the query, utilizing the line above.  Once all results have been concatenated to the query the loop exits and the trailing comma (,) is trimmed.  The database connection is then closed.  A new connection is opened, setting the command text to the built SQL insert statement, executed, and then closed.  This means for each run of the script block 2 queries are ran against the database.  The first is to retrieve the list of active machines and the second is to do an insert of all the results.

What’s Next?

The next phase of this project is to read the results that have been accumulating in the database to generate the appropriate table and graphs.  Each row of the table is going to consist of the machine name (linking to it’s day, week, month graph webpage), the percentage of down time over the last 120 points (30 minutes) and the average latency over the same interval.   The last recorded latency will be displayed in a column with the font colour indicating whether the device is up (green) or down (red).

The full script:

#Win32_PingStatus class
#http://library.wmifun.net/cimv2/win32_pingstatus.html
#    11001 Buffer Too Small
#    11002 Destination Net Unreachable
#    11003 Destination Host Unreachable
#    11004 Destination Protocol Unreachable
#    11005 Destination Port Unreachable
#    11006 No Resources
#    11007 Bad Option
#    11008 Hardware Error
#    11009 Packet Too Big
#    11010 Request Timed Out
#    11011 Bad Request
#    11012 Bad Route
#    11013 TimeToLive Expired Transit
#    11014 TimeToLive Expired Reassembly
#    11015 Parameter Problem
#    11016 Source Quench
#    11017 Option Too Big
#    11018 Bad Destination
#    11032 Negotiating IPSEC
#    11050 General Failure 
 
$pingScriptBlock = {
 
	$sqlInsertQuery = "INSERT INTO [PingResults].[dbo].[PingResult]
			   ([DeviceId]
			   ,[RunDate]
			   ,[Status]
			   ,[Timeout]
			   ,[TimeToLive]
			   ,[ResponseTTL]
			   ,[ReplyI]
			   ,[ResponseTime])
		 VALUES
			   "
 
	# Connect and run a command using SQL Native Client, Returns a recordset
	# Create and open a database connection
	$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=IT-SERVICES\SQLEXPRESS;database=PingResults;User ID=username;Password=pass;"
	$sqlConnection.Open()
 
	#Create a command object
	$sqlCommand = $sqlConnection.CreateCommand()
	$sqlCommand.CommandText = "Select DeviceId, Name from [PingResults].[dbo].[Device] where IsActive=1"
 
	#Execute the Command
	$sqlReader = $sqlCommand.ExecuteReader()
 
	#Parse the records
	while ($sqlReader.Read())
	{
		$MachineName = ($sqlReader["Name"].ToString())
		$deviceId = ($sqlReader["DeviceId"].ToString())
		$RunDate = Get-Date
 
		$PingStatus = Gwmi Win32_PingStatus -Filter "Address = '$MachineName'"
		Select-Object StatusCode
		$status = ($PingStatus.StatusCode)
		#Select-Object Timeout
		$timeout = ($PingStatus.Timeout)
		$ttl = ($PingStatus.TimeToLive)
		$rttl = ($PingStatus.ResponseTimeToLive)
		$ri = ($PingStatus.ReplyInconsistency)
		$RT = ($PingStatus.ResponseTime)
 
		#Won't let me insert empty/NULL for the value so going to make these -1 (which has no practical significance since rttl & RT cannot be -'ve)
		if([string]::IsNullOrEmpty($rttl))
		{
			$rttl = -1
		}
		if([string]::IsNullOrEmpty($RT))
		{
			$RT = -1
		}
 
		$sqlInsertQuery += "(" + $deviceId + ",'" + $RunDate +"'," + $status +"," + $timeout + ","+$ttl+","+ $rttl +",'"+$ri+"',"+$RT+"),"
	}
 
	$sqlInsertQuery = $sqlInsertQuery.Trim(',')
 
	# Close the database connection
	$sqlConnection.Close()
 
	# Create and open a database connection
	$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=IT-SERVICES\SQLEXPRESS;database=PingResults;User ID=username;Password=pass;"
	$sqlConnection.Open()
 
	#Create a command object
	$sqlCommand = $sqlConnection.CreateCommand()
	$sqlCommand.CommandText = $sqlInsertQuery
 
	#Execute the Command
	$sqlReader = $sqlCommand.ExecuteReader()
 
	# Close the database connection
	$sqlConnection.Close()
}
 
# 
Start-Job $pingScriptBlock
Start-Sleep -s 14
Start-Job $pingScriptBlock
Start-Sleep -s 14
Start-Job $pingScriptBlock
Start-Sleep -s 14
Start-Job $pingScriptBlock
 
Write-Host "Waiting for jobs to complete"
While(Get-Job -State "Running") { Start-Sleep 2 }
 
# Display output from all jobs
Get-Job | Receive-Job
 
# Cleanup
Remove-Job *