Devin W.C. Ryan

Ping Results with CSV

This is a project that came out of a wish list from a coworker to monitor,  at a glance, if something is up or down as well as have a bit of a history to analyze for any slow or dropped pings, hence the graph.  The first phase (script) ping.ps1 for performing the ping tests is from an external source that my coworker found and then modified by the two of us to get what we want, the second phase of the process is GeneratePingCharts.ps1 written by me to generate the graphs from the output of the first script using RGraph which is an HTML 5 charts library.

GeneratePingCharts.ps1 output sampleping.ps1 output sample
Ping Results GraphsPing Results HTM

GeneratePingCharts.ps1

I am writing about this one first since I wrote it from scratch. The script calculates the ping response time averages for month, week, & day utilizing the data collected and saved in a CSV file and outputs the graphs and averages to an HTML file. For flexibility I allow the folder containing the CSV files, where the HTML should be outputted, and the location of the RGraph library files to be passed as parameters to the script.

The script starts by outputting the CSS utilized and then gathers the list of files containing the CSV extension from the folder path passed in.  For each file found a new job is started, with a maximum of 10 jobs running at a time, to process the CSV file.  The script waits for all 10 jobs to finish  and then removes the jobs prior to starting 10 more jobs.  This process continues until all files have been handled.

The line which starts the job by running the script block and passing the parameters is:

Start-Job $ProcessCSVScriptBlock -ArgumentList $file.FullName,$outputPath,$rGraphJS

The script block then initiates the processing of the CSV file by calling the Process-CSV function.  The CSV file is imported into a variable using the Import-CSV PowerShell cmdlet followed by each row being processed.  The sum of all the response times and number of response times collected is tracked to calculate the averages.  The response times are stored in a string with the format [a,b,c,….,z] where a-z is the response time value and will contain as many as there are stored in the CSV for the given time frame.  The only exception to this is month, which stores the average response time over $mInc points; otherwise, the graph would not draw do to the large quantity of points.  All this data is then stored into a custom object which is passed to the Generate-Chart function.

The Generate-Chart function stores the HTML for the graph page in a variable inserting the appropriate information stored in the $chartOut parameter in with the HTML string so that the:

  • Machine name and run time are displayed at the top
  • Averages are displayed in a table
  • Day, week, and month graphs contain the appropriate data points.

Worth mentioning is that the Get-My-Date function is used to convert the date stored in an unsupported format (MM_DD_YYYY) to a supported format (YYYY-MM-DD) so that within the Process-CSV function the date can be compared against today’s date allowing the script to know which graphs the ping result belong too.

<#
	.SYNOPSIS
		Calculates the ping response time averages for month, week, & day utilizing the data collected and saved in a CSV file by the script ping.ps1
		Creates a graph based on RGraph functionality and outputs all results to an HTML file.
	.PARAMETER csvFolderPath
		Where the CSVs are located and must all be in the same directory
	.PARAMETER outputPath
		Where the generated graphs should be saved
	.PARAMETER rGraphJS
		Where the JavaScript files are for RGraph used to generate the charts, relative to outputPath
	.EXAMPLE
		.\GeneratePingCharts.ps1 -csvFolderPath "C:\scripts\RESULTS" -outputPath "C:\Program Files (x86)\Lansweeper\Website\PingGraphs" -rGraphJS "../js"
#>

[CmdletBinding()]
 Param (
	[Parameter(Mandatory=$True)]
	[string] $csvFolderPath,
	[Parameter(Mandatory=$True)]
	[string] $outputPath,
	[Parameter(Mandatory=$True)]
	[string] $rGraphJS
)

$ProcessCSVScriptBlock = {
	param($fName, $op, $jsPath)
	
	<#
		Converts the date format used in the file to what Get-Date needs so we can do our comparisons
	#>
	function Get-My-Date ($date)
	{
		$parts = $date.Split('_')
		
		# year-month-day
		$df = $parts[2] + "-" + $parts[0] + "-" + $parts[1]
		
		return Get-Date $df
	}

	# Need to test
	function Generate-Chart
	{
		Param($chartOut,$op,$jsPath)
		
		$today = Get-Date
		$html = '<html>
				<head>
					<link rel="stylesheet" href="style.css" type="text/css" media="screen" />
					
					<script src="'+ $jsPath +'/RGraph.common.core.js" ></script>
					<script src="'+ $jsPath +'/RGraph.common.dynamic.js" ></script>
					<script src="'+ $jsPath +'/RGraph.common.tooltips.js" ></script>
					<script src="'+ $jsPath +'/RGraph.line.js" ></script>
					<script src="'+ $jsPath +'/jquery.min.js" ></script> 
					<!--[if lt IE 9]><script src="'+ $jsPath +'/excanvas.js"></script><![endif]-->
					
					<title>' + $chartOut.MachineName + '</title>
				</head>
				<body>

					<h3>' + $chartOut.MachineName + '</h3>
					<h5>Generated on ' + $today + '</h5>
					<table border="1">
						<tr>
							<th colspan="3">Response Time Averages (ms)</th>
						</tr>
						<tr>
							<th>Month</th>
							<th>Week</th>
							<th>Day</th>
						</tr>
						<tr>
							<td align="left">' + $chartOut.mAvg + '</td>
							<td align="center">' + $chartOut.wAvg + '</td>
							<td align="right">' + $chartOut.dAvg + '</td>
						</tr>
					</table>
					<hr>
					<h5>The Past Day</h5>
					<canvas id="Day" width="2000" height="300">[No canvas support]</canvas>
					<br/>
					<h5>The Past Week</h5>
					<canvas id="Week" width="2000" height="500">[No canvas support]</canvas>
					<h5>The Past Month</h5>
					<canvas id="Month" width="2000" height="500">[No canvas support]</canvas>
					
					<script>
						$(document).ready(function ()
						{
							var line = new RGraph.Line({
								id: ''Week'',
								data: ' + $chartOut.wData + ',
								options: {
									tooltips: ' + $chartOut.wDataToolTips + '
								}
							}).draw()
						})
					</script>
					<script>
						$(document).ready(function ()
						{
							var line = new RGraph.Line({
								id: ''Day'',
								data: ' + $chartOut.dData + ',
								options: {
									tooltips: ' + $chartOut.dDataToolTips + '
								}								
							}).draw()
						})
					</script>
					<script>
						$(document).ready(function ()
						{
							var line = new RGraph.Line({
								id: ''Month'',
								data: ' + $chartOut.mData + ',
								options: {
									tooltips: ' + $chartOut.mDataToolTips + '
								}	
							}).draw()
						})
					</script>
				</body>
				</html>'
		$html | Out-File -FilePath $($op + "\" +$chartOut.MachineName + ".html")
	}
	
	<#
		Reads a csv file in the appropriate format and calculates the last month, week, day response times (RT) as well as every date and response time.
		
		$file is the name of the file to process
	#>
	function Process-CSV
	{
		Param($file,$op,$jsPath)
		
		# Arrays for our chart data
		$mChartData = "["
		$wChartData = "["
		$dChartData = "["
		$mDataToolTips = "["
		$wDataToolTips = "["
		$dDataToolTips = "["
		
		$today = Get-Date
		$monthAgo = $today.AddMonths(-1)
		$weekAgo = $today.AddDays(-7)
		$mSum = 0
		$mPoints = 0
		$wSum = 0
		$wPoints = 0
		$dSum = 0
		$dPoints = 0
		$mIncSum = 0
		$mInc = 3 # How many points in month data to average to 1 point
		
		$csv = Import-CSV -Header MachineName,UpDown,RunDate,PingTime,Status,Timeout,TTL,RTTL,ReI,RT $file
		
		foreach($row in $csv)
		{
			# Array variable containing month, day, year
			$rDate = Get-My-Date $row.RunDate
			
			# Add down pings as empty, not included in sum/points for avg's
			if($row.UpDown -eq "down")
			{ #Need to figure out how to move the x-axis to allow negatives
				$mChartData += ","
				$wChartData += ","
				$dChartData += ","
				$mDataToolTips += "'" + $row.RunDate + ": " + $row.RT + "',"
				$wDataToolTips += "'" + $row.RunDate + ": " + $row.RT + "',"
				$dDataToolTips += "'" + $row.RunDate + ": " + $row.RT + "',"
			}
			else # Use the values in the file
			{
				# Handle month ago data
				if($monthAgo -lt $rDate)
				{
					$mSum += [int]$row.RT
					$mIncSum += [int]$row.RT
					$mPoints += 1
					
					if($mPoints % $mInc -eq 0)
					{
						$avg = ([double]$mIncSum / $mInc)
						$mChartData += [String]$avg + ","
						$mDataToolTips += "'" + $row.RunDate + ": " + [String]$avg + "',"
						$mIncSum = 0
					}
				}
				
				# Handle week ago data
				if($weekAgo -lt $rDate)
				{
					$wSum += [int]$row.RT
					$wPoints += 1
					
					$wChartData += $row.RT + ","
					$wDataToolTips += "'" + $row.RunDate + ": " + $row.RT + "',"
				}
				
				# Handle day ago data
				if(($today - $rDate).TotalHours -lt 24)
				{
					$dSum += [int]$row.RT
					$dPoints += 1
					
					$dChartData += $row.RT + ","
					$dDataToolTips += "'" + $row.RunDate + ": " + $row.RT + "',"
				}
			}
		}
		
		$mChartData = $mChartData.Trim(',') + "]"
		$wChartData = $wChartData.Trim(',') + "]"
		$dChartData = $dChartData.Trim(',') + "]"
		$mDataToolTips = $mDataToolTips.Trim(',') + "]"
		$wDataToolTips = $wDataToolTips.Trim(',') + "]"
		$dDataToolTips = $dDataToolTips.Trim(',') + "]"
		
		# Commented out wChartData & dChartData as mChartData contains all points and then can just use appropriate sections when generating the charts
		$chartData = New-Object System.Object
		$chartData | Add-Member -Type NoteProperty -Name mAvg -Value ([double]$mSum / $mPoints)
		$chartData | Add-Member -Type NoteProperty -Name wAvg -Value ([double]$wSum / $wPoints)
		$chartData | Add-Member -Type NoteProperty -Name dAvg -Value ([double]$dSum / $dPoints)
		$chartData | Add-Member -Type NoteProperty -Name mData -Value $mChartData
		$chartData | Add-Member -Type NoteProperty -Name wData -Value $wChartData
		$chartData | Add-Member -Type NoteProperty -Name dData -Value $dChartData
		$chartData | Add-Member -Type NoteProperty -Name mDataToolTips -Value $mDataToolTips
		$chartData | Add-Member -Type NoteProperty -Name wDataToolTips -Value $wDataToolTips
		$chartData | Add-Member -Type NoteProperty -Name dDataToolTips -Value $dDataToolTips
		$chartData | Add-Member -Type NoteProperty -Name MachineName -Value $csv[0].MachineName
		
		Generate-Chart $chartData $op $jsPath
	}

	# Do what you need to do
	Process-CSV $fName $op $jsPath
	
	# Just wait for a bit...
	Start-Sleep 5
}

# Entry point for script
#CSS file only needs to be done once so do it here
$css = 'body {
	font-family: Arial;
}

pre.code {
	padding: 5px;
	background-color: #eee;
	border: 2px dashed gray
}'
$css | Out-File -FilePath $($outputPath + "\style.css")

$csvFiles = Get-ChildItem $csvFolderPath -Filter *.csv
$count = 0 
Foreach($file in $csvFiles)
{
	$count += 1
	Start-Job $ProcessCSVScriptBlock -ArgumentList $file.FullName,$outputPath,$rGraphJS
	
	if($count % 10 -eq 0)
	{
		# Wait for 10 jobs to complete
		While(Get-Job -State "Running") { Start-Sleep 2 }

		# Display output from all jobs
		Get-Job | Receive-Job
		
		# Cleanup
		Remove-Job *
	}
}

Write-Host "Out of For waiting for last jobs"
# Wait for last jobs to complete
While(Get-Job -State "Running") { Start-Sleep 2 }

# Display output from all jobs
Get-Job | Receive-Job

# Cleanup
Remove-Job *

ping.ps1

The script reads a list of machine names listed one per line in a text file (named servers.txt below) and then loops through every machine and rights the results of the ping to a CSV file as well as the up results to a file with the appropriate HTML to append to the down HTML so that all the downs are displayed at the top.  The script also writes the machine name, state (up/down), run date, ping time, status code, timeout, time to live (ttl), response time to live (RTTL), reply inconsistency (ri), and response time (RT) to a CSV file for historical purposes as well as to feed the second script, detailed after the ping.ps1 code.  This script is presently being run as a scheduled task every 1 minute.

# Red = #FF0000
# Green = #00FF00
# Blue = #0000FF
# Cyan (blue and green) = #00FFFF
# Magenta (red and blue) = #FF00FF
# Yellow (red and green) = #FFFF00

#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 
#		is in use on other pages:	background-color  #DCDCDC

# <--------------- Start script ------------------------------------------------->
clear
#ipconfig /flushdns
$pingResults =("C:\Program Files (x86)\Lansweeper\Website\PingResults.HTM") # <-- you need to change this
$up =("C:\scripts\up.txt") # <-- you need to change this
$RunDate = (get-date).tostring("MM_dd_yyyy")
$PingTime = (Get-Date -format 'hh:mm')
$PingMachines = Gc "C:\scripts\servers.txt"
$n=(6)
#Write the preamble of the report
#clear-content -Path $pingResults
clear-content -Path $up
$htmlContent += "<head><meta http-equiv='refresh' content='15' ><p>"
$htmlContent += "<title> Ping Results </title>"
$htmlContent += "</head><body bgcolor='#DCDCDC'>"
$htmlContent += "<h3><p align='center'>Report Generated " + $RunDate + "
@ " + $PingTime + "</p></h3>"

$htmlContent += "<table border='1' align='center' style='width:50%'>"

ForEach($MachineName In $PingMachines)
{$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)
If ($PingStatus.StatusCode -eq 0)
{
	Add-Content -Path $up ("<tr><pre><h6><td>"  + $MachineName +"</td><td><FONT color=#00FF00>`tUP</FONT></td><td><a href='./PingGraphs/" + $MachineName + ".html' target='_blank'>Graph</a></td></h1></pre></tr>")
}
Else
{
    $htmlContent += "<tr><pre><h6><td>" + $MachineName + "</td><td><FONT color =#FF0000>`tDOWN</FONT></td><td><a href='./PingGraphs/" + $MachineName + ".html' target='_blank'>Graph</a></td></h6></pre></tr>"
}
# send to csv file everything...
If ($PingStatus.StatusCode -eq 0)
{Add-Content "c:\scripts\RESULTS\$MachineName.csv" ($MachineName + ",up,"+ $RunDate +","+ $PingTime+","+ $status +","+$timeout+","+$ttl+","+ $RTTL +","+$ri+","+$RT)}
Else
{Add-Content "c:\scripts\RESULTS\$MachineName.csv" ($MachineName + ",down," + $RunDate +","+ $PingTime+","+ $status +","+$timeout+","+$ttl+","+ $RTTL +","+$ri+","+$RT)}
# $MachineName to $csv to put all results in one file
}
#put all Up results at end of file
$data = (get-content $up)
$htmlContent += $data + "</table>"
#Need to close the syntax of the HTML properly
$htmlContent += "</body></html>"
clear-content -Path $pingResults
$htmlContent | Out-File $pingResults

What’s Next?

The next phase of this project is to get away from the CSV files, as they are going to get extremely bloated.  We want to run the ping.ps1 script about every 15 seconds, which will generate 4 times the amount of data.  So what I want to do is break out the ping.ps1 script into two separate scripts.  The first will be used to perform the actual pings, writing the results to a database, and then the second will read the results from the database to generate the HTML table of up/down results; which will also before formatted differently and contain some additional information.  The GeneratePingCharts.ps1 script will then be modified to obtain the results from the database instead of the CSV file.

3 thoughts on “Ping Results with CSV”

    1. Hi manfred,

      Thank-you for your comment. Could you please clarify what you mean by ‘migrate this via html’?

      The script I provided generates the HTML file and outputs it to the location you require via what you set on the following line:
      $pingResults =("C:\Program Files (x86)\Lansweeper\Website\PingResults.HTM") # <-- you need to change this

      In our case it is located on the same machine where the script is ran. Since this was deemed sufficient & a side project at work I haven't completed the storing of the content into the database instead of a CSV file. The HTML generation would still be the same; except the source for the data would be different.

      I'm not sure if this answers your question. Please follow up if you would like further information. I would have shared the HTML file; however, it contains content I do not wish to leave the organisation.

      Thanks,
      Devin

      1. hi Devin,

        Thanks for replying, what i mean is that, were you able to save the data in database instead of csv, and HTML will read all the data from that database ?

        Thanks,
        Edward

Leave a Comment

Your email address will not be published. Required fields are marked *