PolyMon

One of the features for PolyMon 2.x will be Availability Calendars for Operators. Essentially this will allow the creation of custom calendars for each operator where their Available/Non-Available times will be specified.

Alerting will then alert and escalate, based on these and other settings. So, if someone is say off for a week, their calendar can easily be updated to reflect that they are not available during these times. If alerts are triggered an unavailable operator would then be taken out of the alert/escalation mix.

The ON/OFF calendars will be set up the following way:

• Master Record: Define a Calendar master record which contains:
• Calendar ID
• Calendar Name
• Calendar Time Zone
• On/Off Precedence - this is used in overrides when conflicting ON/OFF settings are found. This will determine whether ON or OFF should take precedence.
  
• Base Calendar Times: Specify On times for any day of the week (possibly multiple times per day of week, possibly even overlapping - for example, M-1am-6am, 4pm-6pm, T-All day, etc). Any time that is not ON is considered OFF.
  
• Overrides: The base calendar defined above can then be overridden as in one of two ways (with any conflicting ON/OFF settings resolved using the On/Off precedence setting of the calendar):
• Recurrence Override: Defines a recurrence scheme based on a Start Date + Every N Days. Can specify whether the override is ON/OFF.Can be multiple and overlapping.
  
• Specific Override: Defines an ON/OFF setting for specific dates and times. Can be multiple and overlapping.
  

A resulting ON/OFF status is then determined by applying overrides to the base times (resolving any conflicts using the On/Off precedence setting).

All date time values specified in calendar settings are in the local time zone specified in the calendar master record. However, all times will be handled internally by PolyMon in UTC.

The sql script to generate the tables and associated functions are available here. Note that you will also need the DOW and Time Zone schemas/functions as well to use these.

One issue I have run across in the past is how the Day of Week numeric representation is calculated by SQL Server. The problem stems from the fact that different countries have different interpretations of what consitutes the first day of the week. For example, Monday is considered the first day of the week for some while others consider Sunday the first day of the week.

To handle this, SQL Server has a global variable @@DateFirstSetting that can be used to specify the first day of the week.

Of course, when creating an application that uses day of week logic and can be used internationally, this can lead to some problems.

I have standardized PolyMon 2.x on using Monday as the first day of the week. However, people could still be using different @@DateFirstSetting values, so I needed a way to avoid using SQL's internal day of week function.

To this end I created a small table (DayOfWeek) that stores the day of the week along with their internal id and other bits of information such as Long, Medium and Short text representations that will be used in the PolyMon front-end. Then, a function (dt_StdDOW) is provided that will calculate the day of the week for any specified date based on that table, irrespective of the @@DateFirstSetting value.

A script to generate the table and function, as well as populate data into the table is available here.

One thing I really wanted to have in the 2.x version was proper time zone handling.
In part this is due to having operator calendars where fully customizable calendars can be set up for each individual operator indicating their On/Off times (i.e. when can an operator be notified) which will also tie into escalation procedures associated with monitor alerts.

I initially thought this would be easy given SQL 2008's new datetimeoffset data type. However, things are never as easy as you think...

This article explains some of the issues and solutions quite well:
blogs.msdn.com/sqlprogrammability/archive/2008/03/18/using-time-zone-data-in-sql-server-2008.aspx

I have used this approach for PolyMon and the following tables and user defined functions are available:

• TZMapping: Table that contains a list of UTC zone offsets (no DST)
• TZZones: Table that contains the DST offsets for each time zone per date range (since DST rules change over time).
• tx_UTCOffset: Function that calculates the UTC offset for a date time + zone (the offset can be used as follows: UTC + Offset --> Local)
• tx_LocalToUTC: Converts a Local date time to UTC (given time zone)
  
• tc_UTCToLocal: Converts UTC to Local time (given time zone)
  

You can download a SQL script that creates the tables and functions as well as populates the time zone data here.

I ran across this podcast of a talk by Jensen Huang, co-founder and CEO of NVIDIA given at Stanford courtesy of the Stanford Technology Venture Program.

It is an excellent talk well worth listening to.

In fact, the entire series of talks available there are excellent - no matter whether you are yourself in a startup or working for an established business - absolutely fantastic content.

I have just started using SQL Server 2008. A really nice feature is the IntellliSense in SQL Server Management Studio (SSMS).

But, I hit a minor snag that took me a little while to figure out.

If you create a stored procedure or user-defined function, and in the same SSMS session write some T-SQL script that references it, IntelliSense doesn't pick up the newly create proc/function.

Reason appears to be that IntelliSense uses a caching mechanism. So, unless you want to keep closing SSMS and re-opening it just to get IntelliSense to recognize your latest proc/function, the simplest is to use the Edit|IntelliSense|Refresh Local Cache menu option (or Ctrl+Shit+R).

This refreshes the cache and IntelliSense now recognizes your new proc/function.

PolyMon integrates PowerShell as a scripting language to give users the ability to create custom monitors as well as script post-monitoring event actions that can be run based on the results of the last monitoring data received.

This integration is actually quite easy to do and I am surprised that using PowerShell as a scripting engine for applications has not become more common.

I will attempt to explain here how this integration can be achieved in .NET applications.

Very simplistically you follow these steps:

• Create an instance of PowerShell.
• Pass any objects you want to that PowerShell instance.
• Assign a script (text) to the PowerShell Instance. That script can use any of the objects you passed in to it, including method calls, properties, etc.
• Run the script.
• After script has completed any changes made to the objects you passed in before running your script are now available.

Basically, this allows PowerShell scripts to interact with any of the objects your application uses (that you care to make available to it).

The first step is to add a reference to PowerShell in your Visual Studio project. Now the PowerShell assembly is located in the GAC so you cannot just use the Add Reference option in your VS project.

[A good discussion on the GAC in general is located here: www.codeproject.com/KB/dotnet/demystifygac.aspx
Another good resource for referencing GAC assemblies from your VS project can be found here: support.microsoft.com/default.aspx?scid=kb;en-us;306149]

Now, you can certainly follow the advice given in the KB article referenced above, but I have simply been editing my VS project file by hand and entering the reference there directly.

To do so, first close your VS project. Then open your VS project file in your favorite text editor.

In the project file, find an ItemGroup section that contains references such as:

In that section you will need to add the following code (please remove line breaks):

Here I am using v1.0 of PowerShell. You will have to modify this entry based on where your instance of PowerShell is installed and based on the version you are using.

Save this file and re-open your project. You should now see a reference to System.Management.Automation in your project. This is the reference you will need in order to use PowerShell within your application.

The namespace we will use is the System.Management.Automation namespace and can be imported in your code file thus:

You create an instance of PowerShell by using the RunspaceFactory.CreateRunSpace method:

You can now make any object available to that instance of PowerShell by using the SessionStateProxy.SetVariable method using a Name/Value pair:

Note that the name you specify here is the name of the variable as it will be available within your PS script, in this case $myObject.

You now create a PS pipeline object with your script using the CreatePipeline method:

Finally, the PowerShell script is executed using the Invoke method:

Since this is a synchronous call, immediately after the script execution completes, control returns to your code and you can now access the objects you passed in to the PS script, including any properties you may have set within your PS script.

You can release the PS objects you created by closing your runspace and disposing it:

In practice you may want to cache your PS run space to avoid the overhead of creating/destroying it, but that will very much depend on your application needs.


Here is the above code put together:


You can also download this as a .NET project here which has the rest of the code for the custom object used in this example and a simple front-end.

Plans are under way for the next major version of PolyMon (2.x).

Based on a few years of community feedback the next version of PolyMon will be a major overhaul of the entire code base.

A lot of the planned features and architecture can be found in the PolyMon 2.x Roadmaps link box on this blog.

Some of the major enhancements will be in the tighter and more advanced PowerShell integration as well as a far more open architecture, enhanced scheduling and alerting, fully customizable dashboards, etc.

Thank you to all the PolyMon users and contributors that are driving this project into exciting new directions.

If you have any suggestions, feedback or would like to help, please let us know. Just post your comments on this blog, email me, or use the discussion board in the PolyMon project (hosted in CodePlex).

I have decided to re-start the (old) PolyMon blog on Blogger instead of Wordpress.

The old blog had died down anyway because I was not keeping it up and most of the conversations regarding PolyMon have been happening in the various PolyMon CodePlex projects (PolyMon, PolyMonRT and PolyMon Controls).

My decision to move to Blogger is based on just how many web "applications" I use through Google - iGoogle, GMail, Voice, Analytics, Reader, Picassa, Sites, etc.

This blog will not be just about PolyMon specifically, but I will occasionally also write up technical stuff I have figured out while working on the PolyMon projects.

Please note that PolyMon is based on .NET, SQL Server and PowerShell so the majority of technical posts are going to be centered around these technologies.