First Solo Flight

Due to the weather not cooperating it was nice to finally find an opening to squeak in some flying.  Though their was bad weather all around us Petter and I decided to take a chance and headed to the airfield on May 26th for 6:30PM.  The evening could have went either way for us but turned out awesome!

I got up for 2 or 3 solid flights on the buddy box, taking off and landing by myself.  On the last flight of the day I did my first solo flight!  This is the first time I actually held my Spectrum DX6 transmitter in my hands while flying.  Using this transmitter over the old one I was using (as Peter held the primary) gave a different feel while flying due to the joysticks being stiffer and the ergonomics of the transmitter itself.   I was on an ‘aeromodellers’ high and my heart felt like it was going to beat out through my chest!  Following tradition my maiden solo voyage was a short one.  I took off, flew down to the end of the run way and maneuvered right into the box pattern, turning left and flying out away from us parallel to the runway.  I then throttled back, getting ready for my approach, banked left once I got to the far left of the field, leveled out and then shortly there after banked left again pointing me at the runway, cut the throttle and coasted in for a successful landing!

We then called it a night as my first solo mission was a success and we were going home with the plane in tack!  We then packed up, and it turned out to be a blessing in disguise as the sun went behind the clouds (didn’t want to be flying in shadows just yet!), the skeeters instantly became wicked and the rain started to fall.  Didn’t matter to me as I was on cloud nine!  I think Peter was too as he gets a great thrill out of seeing his students progress and ended with saying, “Now you’re on your own as there’s nothing more I can do for you while you’re flying”.   Though I know he’ll still help when he can, and plan to be on the buddy box again when I’m out their and the wind has changed and need to land from the other direction.  We then followed it up with going out for a treat.

I’ve been really looking forward to getting out again; however, the weather still hasn’t been cooperating as it’s been raining to much making the field to wet or their have been threatening dark clouds in the sky, which typically end up contributing to the sogginess of the airfield!  If it hasn’t been precipitation it’s been strong unpredictable winds.  Based on the forecast Thursday is looking to be my best chance of getting out before I go on holidays.  In the mean time I’m blogging to take the edge off. 😉

Takeoffs, Flying & Landings. Oh Yeah!

On Wednesday, May 18th I got in my first flights of the season! I went out the night before; however, my transmitter with its new battery pack (that’s actually made for the Spektrum radio I have) when I got there was not charged!  I thought I had charged it, plugged it into the wall overnight, but my outlets are weird, so I’m guessing the outlet was turned off.  Lesson learned though, always check the radio the night before going flying!  I then tried again the next night and boy did it pay off!  I got in three solid flights.

The first flight started with Peter taking off the plane and then allowing me to fly around however I saw fit.  I did a few figure eights and transitioned into boxes to practice lining up for landings.  I then descended lower and lower on the approach whizzing down the middle of the runway.  I eventually took more and more off the throttle until I was pretty much landing, but since wasn’t time yet I’d apply full throttle to ascend up for another come around until no mention of doing another come around came up from Peter and I landed!

The second flight started with me taxing the plane out into the runway, followed by me standing behind the plane lining it up for take off (based on the windsock).  I then took off the aircraft and awkwardly (as that’s me, Mr. Awkward! Well, sometimes, lol) backed up as Peter guided me with a nudge here and their back behind little fence for pilots in the air as I didn’t want to take my eyes off the plane.  I then flew around until it came time to land again and then practised lining up my approach and eventually landed my Sig Kadet LT-40 back on solid ground.

The third flight got even more exhilarating.  Their were times I could feel my heart beating hard in my chest!  This time I taxied the plane out to the runway, stayed behind the fence, and took off the aircraft from behind the fence.  This provides a whole other perspective of the plane while taking off (side-view essentially).  I then flew around for a bit and transitioned into touch and goes.  I would come in as a landing, slowed right down, touch the plane down on the ground and coast along the ground a tiny bit as I’d then give full throttle to take off yet again to come around for yet another landing.  I did this a handful of times before finally touching down for the night.

All in all I had an excellent night of flying and learned more while I was at it.  You basically gotta learn something new each time out, whether its little or big, as that is part of the fun and what allows you to progress!  Some key things for this night out are:

  • Don’t bank to steeply in turns, especially when it is windy as it was this night, or you risk the wind catching your plane and flipping you over, due to the amount of surface area being exposed
  • At our airfield don’t go to far off the end of the runway for your approach or else you risk the wind (up draft?) coming up the hill and rocking your plane.  Thank-you Peter for the save (rather than then seeing if I would have been able to).
  • Kinda learned this one before, but if you are not comfortable with your approach for landing, give ‘er power and come around again (this is why you want to land before you run out of fuel) because if something already started to go wrong it’s likely only going to get worse if you try to correct/force the landing.

SharePoint 2013 Display Templates

In SharePoint 2013 display templates are used with the content by search web part (CSWP) to format and style search results generated by the CSWP search query.  Display templates control which managed properties are shown in the search results and how they appear in the web part.  Each display template consists of two parts, the HTML version of the template that you can edit and a .js file SharePoint auto-generates for its use (never to be edited by you) when an HTML file is uploaded.

There are two types of display templates:

  • Control Template: is rendered only once in the web part and contains HTML to structure the overall layout of how you want to present the search results. I.e. HTML for a heading, beginning and end of a list
  • Item Template: is rendered one time for each item in the result set, i.e. for a results set containing five items, the template creates its HTML section five times

OHS Document Item Display Template

To see the code please view OHSDocItem_TwoLines, a text file containing the code.  To use the template would need to change the file extension from txt to html.

Code Breakdown
The key here is to take a copy of one of SharePoint’s already created item templates that is close to what you are trying to create.  In my case that was the Item_2Lines template.  From their you need to make the necessary edits, which broken down for me are the following, with description underneath:

<title>

Edit the contents between the title tag to be what you want end users to see when selecting your display template, see Fig. 1.

<mso:ManagedPropertyMapping msdt:dt="string">'Link URL'{Link URL}:'Path','Line 1'{Line 1}:'Title','LastModifiedTime'{Line 2}:'LastModifiedTime', 'LastModifiedBy'{Line 3}:'LastModifiedBy','FileExtension','SecondaryFileExtension'</mso:ManagedPropertyMapping>

This one is key as to what information is going to be accessible for displaying items.  Each one needs to be a managed property, specified in the ‘Search Schema’ for the correct search service application (SSA).

1
<mso:ContentTypeId msdt:dt="string">0x010100BEBD1B217F6D144191B58533C4577EAB1E</mso:ContentTypeId>

Within here is the content type ID for the content type your search query is returning.  This can be found by going to your content type hub and clicking on the appropriate content type.  You can then grab the ID from the URL displayed in the address bar.

<div id="OHSDocItem_TwoLines">

Initially will contain the file name portion of the one you copied, make it match yours.

var modified = $getItemValue(ctx, "LastModifiedTime");

Is an example of how to retrieve the contents of the managed property mapped via the first snipped of code above.  This says to get the value stored in the item ‘LastModifiedTime’ using the current context, ctx (a variable SharePoint creates, handles, and makes available to you).

Usage
In my scenario I am using the CSWP to surface up OHS documents pertaining to a particular office, i.e. Atikokan, on a page within their office site.  Our OHS documents are all of the content type “OHS Document”.  Prior to utilizing the display template first the query returns back relevant results, in this case the query is ‘Return back items of the content type OHS Document where the tags metadata field contains the value Atikokan’, or in query form:

ContentTypeId:0x010100BEBD1B217F6D144191B58533C4577EAB1E* Tags:Atikokan

Fig. 1 shows the customization of the CSWP, where the query is done in an additional window by clicking ‘Change Query’.  In this case we are using the ‘List with Paging’ control (a.k.a. the control template) which allows the user to scroll through results, 15 per page, by using the arrows.  The item template is the one I created (see Making Your Display Template Accessible below).   The property mappings are pulled directly from the display template as specified within the <mso:ManagedPropertyMapping msdt:dt=”string”> tag.

Content Search Web Part Config
Fig. 1: Content Search Web Part Config

 

The result of the display template is shown in Fig. 2, where the managed property mappings correlate as follows:

  • ‘Path’: Contains the URL to the item being displayed
  • ‘Title’: Is the title from the document, which may be the same as the the file name.  In our environment we’ve had to correct a few of these so that they are meaningful
  • ‘LastModifiedTime’: Contains the date the file was last modified and makes up part of the 2nd line of the display template
  • ‘LastModifiedBy’: Contains the person who last modified the document and makes up the second part of the 2nd line.  This managed property I created includes a mapping to the crawled properties LastModifiedBy, ows_Modified_x0020_By, and ows_ModifiedBy.

As you can see in Fig. 2 this does not always contain a value, so there appears to still be a better crawled property (or perhaps ordering of crawled properties within my managed property) to be utilized.  To handle this I have if conditions, seen as ‘comment sections’ in the code below which determines which code needs to be added for display.  If the modified date is empty then nothing will get displayed and if modified date is not empty and the last modified person is not empty then ‘by’ is inserted along with the person who last modified the document.  This prevents ‘by’ appearing without specifying an individual.

<!--#_
if(!modified.isEmpty)
{
_#-->
                <div class="cbs-modified ms-noWrap" title="_#= $htmlEncode(modified.defaultValueRenderer(modified)) =#_" id="_#= modifiedId =#_">Last modified on _#= modified =#_
<!--#_
}
_#-->
<!--#_
if(!modified.isEmpty && !lastModifiedBy.isEmpty)
{
_#-->
				by _#= lastModifiedBy =#_
<!--#_
}
_#-->
<!--#_
if(!modified.isEmpty)
{
_#-->
                </div>
<!--#_
}
_#-->
OHS Item Display Template used in a Content Search Web Part
Fig. 2: OHS Item Display Template used in a Content Search Web Part

Making Your Display Template Accessible
At first I tried to copy an existing template and discovered that copying an existing HTML file to my local machine and then editing it, followed by copying it back did not work for me.  What I had to do instead of copying it back through Windows Explorer I needed to navigate to ~sitecollection/_catalogs/masterpage and then drill down into ‘Display Templates -> Content Web Parts’.  Then to add my newly created display template from the ribbon I navigated to ‘FILES -> New Document -> Item Display Template’, left clicking on ‘Item Display Template’ as that is the type I have created.

I then chose my display template HTML file *Item_*.html (Fill the * to get actual template name as this is my naming convention), added appropriate version comments, then clicked ‘OK’.  I left the default properties on the document, as it appeared to pull everything correctly from the file.  After reading it over I clicked ‘Save’.  Refreshing the page by clicking ‘Content Web Parts’ and now see two additional files:

*Item_*.html (That I uploaded)
*Item_*.js (That was generated as expected)

Once this process is done you should be able to edit the properties of the CSWP and see the newly added display template under the drop down list ‘Item’ for display templates.  The name will be as you described it using the HTML title attribute in the HTML file.

For an introduction to display templates with screenshots, that got me started, you can see an article by Matthew McDermott entitled Introduction to SharePoint 2013 Display Templates.

Database Design v3 – Quilt

Tracker Database v3 - Quilting
Tracker Database v3 – Quilting

I’m back!  This project has been put on the back burner for a while, but now that it is fall and winter is soon to follow I can’t think of a better time to ramp up development again, especially since a new requirement for my tracker application has arisen!  In chatting with my girlfriend, who is an avid quilter, would like a place to track the quilts she has completed.  Currently this is done by taking a picture of the quilt and storing just the image on her laptop.  She would like to keep track of other information as well, which lead to the addition of a Quilt table (first eight rows) and a Quilt Image Path table (remaining rows) including the following columns:

QuiltIDint(11)Primary Key of Quilt Table, Auto Increment (Also FK of QuiltImagePath table)
UserIDint(11)Foreign Key from User table
Lengthdouble(5,2)Will use inches as unit
Widthdouble(5,2)Will use inches as unit
Patternvarchar(255)The name of the pattern used in creating the quilt
Recipientvarchar(200)Who the quilt was made for
CompletedOndateWhen quilt was finished
Costdecimal(6,2)Total cost of quilt, includes materials, etc.
ImagePathvarchar(255)Path, with file name, to the quilt relative to project
ImagePathIDint(11)Primary Key of Quilt Image Path table, Auto Increment

I thought this would be a nice addition, as it also adds in another technical element I haven’t had to deal with in any of the other items I am tracking.  This is the addition of being able to upload, store, reference, and display an image related to the content.  Also have to implement the functionality to delete a record along with corresponding images.  I believe, without attempting an implementation yet, I am going to accomplish this by:

  • Storing the images in a folder on disk (this folder will be contained within my VS project so I know how to properly reference the folder)
  • Rename the file on upload after record is created to include primary key in file name for if need to cross reference for any reason, also ensures unique names are being stored
  • Storing the relative path (from project perspective) with file name in the database (FilePath column)
  • Implement deleting of a record to also include deleting of the file, on missing files skip and continue with deletion of record

My goal for the winter is to have an initial implementation for all the features I currently have slated for my tracker application.

Sig Kadet LT40 Maiden Voyage

Me with my virgin LT40

Since my last post there have been a few developments.  Going to start with my first flight followed by a continuation of the customizations done by Peter and John to my plane and why as more came out of that first flight due to things we discovered.

First Flight

It was great and a little nerve racking to finally get out to the airfield and see if my plane would fly.  The first time time out to attempt a maiden voyage the weather didn’t cooperate as it was too windy, but that was OK as we started with the basics of ground work and I taxied around the runway.  Peter was impressed by how well I was able to keep the plane straight as I rolled down the runway.  I technically, if you’ll humour me, had my first take off and landing as I increased the speed so much that the plane actually took off the ground about 4 feet, followed by me cutting the throttle and successfully landing the aircraft back on the wheels.  At which point I continued to taxi around, but that initial feeling was awesome!  I guess I can say that I took it for its first flight 😉

Taxing LT 40 Video

It’s maiden voyage for a flight longer than a handful of seconds occurred the next time out and was done by Peter who did it beautifully.  He was impressed, as were those watching, at how effortlessly the LT40 left the ground and took to the air.  Once he was up we could tell the engine wasn’t running all that smooth and the plane was pulling to the right so John adjusted the trim while Peter flew.  They also tweaked the engine to get it performing better.  We then attempted the buddy box so that I could get in some flying time; however, we kept loosing connection and it was being very temperamental as the evening progressed.  I did manage to fly it for a couple circuits.

Due to the temperament of the buddy box Peter and I played with it at his place and tested Peter’s Eurika moment of batteries, which turned out to be the case!  Once the main transmitters batteries were drained down past a certain level (seemed to be about 5.5V) the buddy box ceased to function.

Customizations

During our initial testing of the aircraft we noticed while taxing around that we were chopping lots of grass which lead to additional customizations on the plane, namely adding a wedge between the main landing gear and the plane to raise it up higher off the ground and unravelling one of the coils on the nose wheel to lengthen it as well so the aircraft remained level.  Additionally we did some testing of the buddy box and discovered the reasoning for loosing connection was that once the main transmitters battery dropped below 5.6V the buddy box would no longer function as the transmitter could not maintain the connection.  The transmitter is a battery hog and I should have a LiPo battery pack on the way once an order with Hobby King is placed that John is going to kindly wire up for me so I can charge it and it’ll fit nicely in my transmitter (he has the same transmitter and battery combo).

Conclusion

I am well on my way to becoming an R/C pilot and am itching to get some more flying hours under my belt.  I, along with Peter and John, don’t think it will be long before I am taking off and landing if I can just get in the time.  Tried getting out early last week but it was to windy to fly and Wednesday night I got out again where I performed my first landing, which went really well.  After a break I performed my first takeoff.  To do this we stood out in the middle of the runway behind the plane so I could see how the plane reacts as it is going down the runway.  I applied a little up elevator and let it fly level to pick up airspeed once it was off the ground before rising up to a reasonable height.  After flying around I came in for a landing, which wasn’t so pretty,  as the sun was setting and I went against my better judgement and continued to float the plane in nice and slow, ending up in the tall grass off the far side of the landing strip.  Thankfully no damage was done, and according to John and landing the plane remains in one piece is a good landing!

I am excited to get out again; however, I’ve missed the last couple of opportunities due to being sick but hopefully will get out a time or two, even with going away on holidays shortly, before the season is out.

Here are some pics of my Sig Kadet LT 40 at the field so you can see some of the ins and outs as well as me posing with it holding my transmitter (Spectrum DX6) before its first trip to the runway.

Flying, Equipment setup & First Plane!

I have been out to the airfield a couple times since my last post.  Nowhere near as much as I would have liked to but you can’t control the weather!

I am gaining more experience and have gotten well beyond the circle pattern I have been flying in both directions.  I moved on to figure eights and have been doing them in both directions.  It’s now where I start when I first get control of the aircraft for the day!  I have been doing boxes the last few times out, which consists of flying in a box formation where you go down the center of the runway do a quick turn, fly out, do another quick turn, fly to the other end of the field, another quick turn, and then you do another quick turn as if you are coming in for a landing and you want to be lined up with the center of the runway and fly right down the middle.  Essentially this is practicing for landing the aircraft.  As I get better with the key turn (coming into the runway) the box will get lower and lower until I am finally landing the plane.

Quick Turn

What I mean by a quick turn is that you bank the aircraft to the inside of your turn, as you pull up on the stick a little giving some up elevator to keep the nose level/up, and then immediately straighten the aircraft so it is level.

This is required so that you are lined up properly with the runway and are level.  If for any reason something doesn’t feel right you abort the landing.  This is why you should always land with fuel in the tank and avoid those dead stick landings!  When you dead stick you just pray you get it right on the first try and go with it as there is no second attempt.

Equipment

I have finally bit the bullet and purchased the gear I require, which consists of the following:

  • Sig Kadet LT 40 ARF
  • 2 O.S. Glow Plugs
  • Glow driver
  • Spektrum DX6 Transmitter w/ AR610 receiver + 1 Free receiver (Gotta love this promotion as then I already have one for my next plane!)
  • Accu-Cycle charger
  • Charger leads
  • Fuel Pump

Thank-you to Peter who had the battery and switch for the plane as well as the servos, better landing gear (both wheels and assembly) along with a stronger push rod for the throttle, more on that in the next post about assembly!

Next Flight

Looks like my next flight is going to be with my very own plane!  More on that to come with the next post.

First flight at the Kenora airfield

Sig Kadet LT 40

Wednesday I finally got out to the Kenora airfield to try my hands with a gas powered trainer.  I got up for a couple flights.  I was on the buddy box while Peter held the master control as I flew counter-clockwise around the perimeter of the flying field.   I did really well for my first time out as not once did Peter have to take control of the aircraft due to a misstep on my part.  I was able to keep the plane at a constant altitude.  One key thing to remember is that as your turning you also need to pull back on the stick a bit too in order to give the aircraft lift as while banking in turns the plane will want to nose down and descend, which you do not want.

I haven’t taken off the aircraft yet; but in talking with Peter one thing I need to remember is that after lift off need to level out the plane a bit (don’t just try to climb and turn right away) to gain some airspeed before climbing and banking to ensure I have enough airspeed to continue the climb instead of stalling and loosing lift sending the aircraft back to earth (what you don’t want, lol).

Flying was a lot of fun and I am excited to get out again.

Holding Sig Kadet LT40
Holding Sig Kadet LT40 that I am learning to fly on.

Kenora Aeromodelers Club Introduction

When I was home the weekend before last I decided it was time to get in touch with my local R/C (Remote Control) flying club since I’m now finally in a position that I can take up a hobby I have been wanting to try and get into for years.  Since the weather didn’t cooperate this past Friday I met Peter (he responded to my email to the club) at King George school.

Our meeting started with introductions to the various members that were present followed by going over some flying/club rules, like taking turns and how to rotate turns while in doors as well as out on the field.  I also went over the radio and how best to hold it, which is such that your hand controlling the stick for ‘steering’ (rudder only in this case) such that you are controlling it with two fingers, your thumb and pointer finger, and can reach the top controls with your other fingers.  Should have taken a picture but I didn’t think to, lol.  For radios the club likes to use Spektrum and Airtronics.

ParkZone Night Vapor
ParkZone Night Vapor

I got to fly a ParkZone Night Vapor around the gym.  I started by taking over once the plane was in the air, flying counter-clockwise circles around the gym taking up as much area as I could.  I eventually got braver and threw in a figure eight or two.   I did have a crash or two where I ended up in a wall, but overall the instructor was impressed by how quickly I picked it up.  I was taking the aircraft off and eventually flying clockwise around the gym to mix it up, which after becoming so comfortable with the other way threw me off a little at first.  The key points I need to remember are:

  • Keep plane in sight (OK fairly obvious)
  • Do not fly over top of my head (or any ones for that mater)
  • Kill the power if you crash (it’s inevitable I suppose and protects as much of the plane as you can)
  • Avoid helicopters as they’ll shred the plane

I was there for a few hours and really enjoyed my first time out with the club here in Kenora.  I am looking forward to getting to the airfield (outdoors) and flying a gas powered trainer once I’m back from vacation in NL.

Rename Files with PowerShell

While on holidays I started scanning in a bunch of pictures for a family project and since this was being done in batches each batch of scanned images would be named childhoodXXXXX.jpg where XXXXX is the number of the current image in sequence.  This would be the same for each batch.  Since wanted a means of knowing which images were part of which batch (and thus the corresponding package they came from) I wrote a little script to rename files in a folder with the following requirements:

  • Prepend batch number to beginning of file name
  • Prepend to files that did not currently start with a digit (since we know all names start with childhood)
  • Script is being run from the directory containing the files
  • I didn’t include a recursive folder structure so all files are in the same directory

The script is as follows:

$num = "3 - "
 
dir | 
%{ 
    $name = $_.Name;
    $c = $name.SubString(0,1);
 
    if(-not [char]::IsNumber($c))
    {
        Rename-Item $_ ($num + $name)
    }; 
}

The script works by getting a list of all files in the current directory, dir, and piping it to a loop which iterates over every file in the folder.  The % is a shorthand/alias for the cmdlet Foreach-Object.  For every file the first character of the name is retrieved and then is tested to see if it is a number, if it is not a number then the Rename-Item cmdlet is used to return the current object/file represented by $_ in the loop to the concatenation of $num and $name, i.e.

childhood0001.jpg would become 3 – childhood0001.jpg

You can make your prefix anything as it is just a string by setting the $num variable.  This means you could automate this further by dynamically changing the prefix within a separate loop or passing it as a parameter to your script.

This post came out of me wanting to play with PowerShell over the holidays and as it worked out this will be my last post of 2014.

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 *