In the previous two articles, we discovered how to build collections to scope software deployment using information scavenged from your hardware inventory scans. In order to reduce the number of collections required for each scoped deployment, we created the SQL function Custom_ConvertVersionToBigint. Today, I'll use this function with some extra data cleaning and an aggregate wrapper to provide a fairly robust collection template for software rollouts. For some light relief from all this SQL, we'll finish with a couple of cool tips: the first ensures your package deployments never overload your Notification Server, and the second allows you to nestle all your software deployment objects together in the console.

Introduction

In the first article, we looked at how the version number field in the software inventory tables allow us to target delivery of applications to computers which did not previously possess the application at that version. We expanded on this in the second article and created a SQL function to allow us to deliver software packages to machines which did not meet a minimum version requirement. The above two scenarios subtly differ in that in the first scenario, while machines are upgraded which possess software below the required version level, it will also downgrade applications should someone upgrade their application beyond that level.

To prevent downgrading your proactive user population, in the last article we created a SQL function called Custom_ConvertVersionToBigint to facilitate version comparisons for scoped deployments. This allowed us a straightforward mechanism to check your PC estate for software not meeting some minimum version requirement which wouldn't fall foul of the errors which crop up when raw version strings against compared against one another. In this article, we are going to make this function a little more robust by cleansing the input.

As I alluded to in the summary above, for those who manage to make it to the end of today's article, I'll show a couple of very neat tricks. The first shows you how to use the SQL command TOP to naturally stagger your scoped deployments. The effect is to trickle your software delivery packages out to your clients, massively reducing your server load. This tip is most useful if you have one NS box for everything in your environment.

The second tip shows you how to import and export console objects, thereby allowing you to place all your collections, policies and software delivery packages into the same location. This means every object required for your deployment is co-located. If you often find yourself frustrated by your software package objects being split across three tabs in the console, then this tip is certainly for you.

Advantages of Software Scoping

Scoping of Software deployments has several advantages,

1. Efficient use of network bandwidth
   Because you are delivering packages only to machines which require them you can save on an awful lot of unnecessary network traffic. Using various scoping techniques you can also stagger your deployments which can prevent not only network load, but load on your Notification server too.
2. Increased likelihood of successful package installation.
   In scoped deployments, it is common practice to run the package on a repeating schedule. For example the policy to install Package X would target the collection "Computers without Package X" and be configured to run daily. This is fabulous for automatically resolving those random install failures -machines which do not complete the install on the first run, will be asked to re-run the package daily until the it completes.

It is because scoped deployments are so damn useful that its worth this extra pain of grappling with SQL, and therefore I guess articles like these ;-)

Casting Version Strings as Integers

Let's now catch up with where we left off. Last time we created a SQL function to convert a software version string into a big integer. This was to allow us to write meaningful version comparisons in SQL,

If Custom_ConvertVersionToBigInt(version) < Custom_ConvertVersionToBigInt('8.1.2'))
 BEGIN
  ..
  ..
 END
 

This function was neccessary as comparative operations on strings using the less than and greater than operators may not perform as expected. This is because the comparison is alpha-numerical. A simple example illustrates this: The version string '3.12.89' is reflects an application which 5 minor releases ahead of '3.7.0', but a simple comparison at the string level would not agree. The strings are equal until the third characters are compared, and here the '1' in '12' would show as being smaller than '7'.

The function Custom_ConvertVersionToBigInt, listed in the previous article, gave us a better way of comparing version strings by converting them into integers. Every major desktop application works well with this function, but to cater for some of the smaller software houses some changes are required to make the code more robust. The issues are,

• Some applications will use letters to denote minor revisions to applications, i.e. We have a Motorola phone tool application which as a version string of '3.11a'
• Some applications can prefix the version string with the application name, i.e. Icon Extractor has version strings like 'Icon Extractor 4.2.1'. Or they might add a superfluous 'v' or 'version' to the string, i.e. 'MpgTag v2.3'.
• In some rare cases, applications may use commas to delimit the major, minor, revision and build fields.

To get around these issues, I found the simplest answers were to,

1. Making the first task in the string conversion function to replace any commas with full stops.
2. Remove any non-numeric characters from the version string before casting as a big integer.

The above isn't perfect, but it does allow a correct version comparison in the vast majority of software strings out there.

Replacing Commas with Full Stops in SQL

To replace those commas which might appear in the version string, we can use the SQL REPLACE function which takes as its first argument the input string, as the second argument the substring to search for, and as the last argument the substring to replace the searched substring with.

So, replacing our commas is easy,

/* Replace any commas with dots */
SET @VERSIONString = REPLACE(@VERSIONString,',','.')

Removing letters from strings

To cleanse our string of unwanted characters is a little bit trickier. A nice approach is to get parse the string, character by character, checking to see if its an integer. The number check is most succinctly done by establishing whether the character's ASCII value lies between 0 and 9,

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/*==================================================================================

NAME:        Custom_ExtractNumeric

DESCRIPTION:     This functions accepts a string and returns the string
           after removing any characters which are not in the range [0-9].
		   This cleansing is so that the result can be recast as an
		   integer without error. The is intended to be used in cleansing
		   version numbers which occasionally include alpha characters.

USER PARAMETERS:   @INPUT VARCHAR(30)


DATE     DEVELOPER     DESCRIPTION OF REVISION       VERSION
=========  ===============  =================================  ===========
19/02/2007  Ian Atkin     Initial release           1.00
*/


CREATE FUNCTION [dbo].[Custom_ExtractNumeric](@INPUT VARCHAR(30))
RETURNS VARCHAR(30)
AS
BEGIN
 DECLARE @OUTPUT VARCHAR(30)
 DECLARE @CHAR CHAR(1)
 DECLARE @POSITION INT
 DECLARE @END INT

 SET @Position = 0
 SET @OUTPUT= ''

 /* Calculate the length of the input string using the generic string
   function LEN
 */

 SET @END = LEN(@INPUT)


 /* Start from the beginning of the input string @INPUT and analyse each 
   character in turn. If the ASCII value of the character is between 48 
   and 57, then the character is a numeric (its between 0 and 9) and so 
   append this to the output string @OUTPUT.
 */
 WHILE @POSITION <= @END
 BEGIN
  SET @CHAR = substring(@INPUT,@POSITION,1)
  if ASCII(@CHAR) >=48 and ASCII(@CHAR) <= 57
    SET @OUTPUT = @OUTPUT + @CHAR
  SET @POSITION = @POSITION + 1
 END

RETURN @OUTPUT

END

The Final Code

Putting all this together, gives us the following revision to Custom_ConvertVersionToBigInt SQL code. Note in the following we use the ALTER command instead of CREATE, as this function already exists and we are just changing it.

use altiris;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*==================================================================================

DESCRIPTION:   
The following code accepts a string which is a assumed to contain a version
number, a set of numeric fields delimeted by a '.', a full stop. Bmultiplyingng
each field in turn by a factor of 10^4, we convert to a BIGINT, assuming
that the most significant version field is the left most, i.e.

Author:
Ian Atkin, Oxford University, 2007

Usage:
Argument, VERSIONString is assumed to be of the following form,
@VERSIONString = 'MajorVersion.MinorVerison.Revision.Build'

We assume also that the user cares only about maximum of the four most significant fields
*/

Alter FUNCTION [dbo].[Custom_ConvertVersionToBigint](@VersionString VARCHAR(30))
RETURNS BIGINT
AS

BEGIN
  DECLARE @DotPosition1 INT
  DECLARE @DotPosition2 INT
  DECLARE @DotPosition3 INT
  DECLARE @DotPosition4 INT
  DECLARE @Str1 VARCHAR(30)
  DECLARE @Str2 VARCHAR(30)
  DECLARE @Str3 VARCHAR(30)
  DECLARE @Str4 VARCHAR(30)
  DECLARE @VersionBigint as BIGINT


  /* Replace any commas with dots */
  SET @VERSIONString = REPLACE(@VERSIONString,',','.')


  /* To keep the code simple, lets be sure we always have a dot at the end.
   This allows us to take the numbers between the dots for all fields 
  */
  if RIGHT(@VERSIONString,1) not Like '.' SET @VERSIONString = @VERSIONString + '.'

  /* Collect the positions of all the dots which delimit the fields
   in the string
  */
  SET @DotPosition1 = CHARINDEX('.' , @VERSIONString)

  If @DotPosition1 > 0
  SET @DotPosition2 = CHARINDEX('.' , @VERSIONString, @DotPosition1 + 1)
    
  If @DotPosition2 > 0
  SET @DotPosition3 = CHARINDEX('.' , @VERSIONString, @DotPosition2 + 1)

  If @DotPosition3 > 0
  SET @DotPosition4 = CHARINDEX('.' , @VERSIONString, @DotPosition3 + 1)

  /*Now extract the first field, which is the text up to the
   first dot delimeter
   Multiply by 10^12 to ensure this field is the most significant.
  */
   IF @DotPosition1 > 0
   BEGIN
   SET @Str1 = SUBSTRING(@VERSIONString, 1, @DotPosition1 - 1)
   SET @VersionBigint = cast(dbo.Custom_ExtractNumeric(@Str1) as BIGINT) * 1000000000000
   END


  /*Now extract the second field from between the first and 
   second dot delimeters.

   Multiply by 10^8 to ensure this field is the second most significant.
  */
   IF @DotPosition2 > 0
   BEGIN
   SET @Str2 =SUBSTRING(@VERSIONString,@DotPosition1+1,@DotPosition2-@DotPosition1-1)
   SET @VersionBigint = @VersionBigint + cast(dbo.Custom_ExtractNumeric(@Str2) as BIGINT) * 100000000
   END


  /*Now extract the third field from between the second and 
   third dot delimeters.

   Multiply by 10^4 to ensure this field is the third most significant.
  */
   IF @DotPosition3 > 0
   BEGIN
   SET @Str3 = SUBSTRING(@VERSIONString,@DotPosition2+1,@DotPosition3-@DotPosition2-1)
   SET @VersionBigint = @VersionBigint + cast(dbo.Custom_ExtractNumeric(@Str3) as BIGINT) * 10000
   END

  /*Now extract the fourth field from between the first and 
   second dot delimeters.

   Do not multiply to ensure this field is the least significant.
  */
   IF @DotPosition4 > 0
   BEGIN
   SET @Str4 = SUBSTRING(@VERSIONString,@DotPosition3+1,@DotPosition4-@DotPosition3-1)
   SET @VersionBigint = @VersionBigint + cast(dbo.Custom_ExtractNumeric(@Str4) as BIGINT) * 1
   END
  
RETURN @VersionBigint 
END

Taking Into Account Upgrade Paths - Aggregate Functions

Now that we have a more robust piece of code, what happens when we look at our collection which finds all our computers with Adobe Reader installed at below version 8.1.2? Something odd as it happens. We could well find that we've got a lot more computers without the update than we expect (hang in there, we're nearly done....)

The reason is that the upgrade paths for some applications leaves the old version information in the Add/Remove portion of the registry. Adobe Reader is pretty famous for this. The graphic opposite shows a section taken the inventory tab in Resource Manager -here we can plainly see the multiple versions of Adobe Reader we apparently have installed.

To take into account legacy entries, what we normally do is look for the maximum version of said software that is installed.

To do this we use one of SQL aggregate functions, MAX. This will give the maximum numeric value from a column. So, looking at out previous query to list computer GUIDs where Adobe Reader at version less than 8.1.2 is found,

SELECT guid 
FROM vcomputer 
WHERE guid IN (SELECT [_resourceguid] 
    FROM inv_aex_os_add_remove_programs 
    WHERE (inv_aex_os_add_remove_programs.[name] LIKE 'Adobe Reader%' 
      AND inv_aex_os_add_remove_programs.[name] NOT LIKE '% CE' 
      AND inv_aex_os_add_remove_programs.[name] NOT LIKE '%Font%' 
      AND inv_aex_os_add_remove_programs.[name] NOT LIKE '%Language%') 
      AND dbo.Custom_convertversiontobigint(inv_aex_os_add_remove_programs.version) < dbo.Custom_convertversiontobigint('8.1.2')) 

We now wrap our max function around our query, grouping all the Computer Guids together in a bundle and retrieving only the maxmum value of version number found,

SELECT guid 
FROM  (SELECT  vc.guid                                   AS 'Guid', 
         MAX(dbo.Custom_convertversiontobigint(inv_aex_os_add_remove_programs.version)) AS maxversion 
    FROM   vcomputer vc 
         JOIN inv_aex_os_add_remove_programs 
          ON vc.guid = inv_aex_os_add_remove_programs._resourceguid 
    WHERE  (inv_aex_os_add_remove_programs.[name] LIKE 'Adobe Reader%' 
         AND inv_aex_os_add_remove_programs.[name] NOT LIKE '% CE' 
         AND inv_aex_os_add_remove_programs.[name] NOT LIKE '%Font%' 
         AND inv_aex_os_add_remove_programs.[name] NOT LIKE '%Language%') 
    GROUP BY vc.guid) xxx 
WHERE maxversion < dbo.Custom_convertversiontobigint('8.1.2')
 

The new table generated by this query xxx, is in essence the result of the filtered vComputer and inv_aex_os_add_remove_programs join. It will just contain one GUID for each computer and the value stored in the second column will be thmaximumum version of Adobe Reader found on that machine.

So, that's it. May the above collection SQL serve you well as a template for your software rollouts!

Staggering Deployment Using TOP

If you're running Notification Server in a single box without package servers deployed then this tip is definitely for you.

Taking the example of the Adobe Reader 8.1.2 upgrade as our case point, lets say the collection of computers which requires the upgrade has 2000 computers in it. Adobe Reader is now about a 90MB delivery, so the traffic is not going to be insubstantial. How can we restrict this? Well the agent has some bandwidth throttling options, and multicast can also help massively too. But there is another weapon in your arsenal -your SQL query which builds your collection.

As computers get upgraded, they no longer appear in the "Computers requiring Upgrade" collection. So, over a few days you expect this collection size to diminish rapidly. As the collection size diminishes, the overhead of delivering the packages gets smaller and smaller.

With SQL though, we can limit the number of computers in the collection to say 100. This way, no more than 100 computers will be downloading the package at any point. And as each client installs the package and upgrades it falls out of the collection allowing a computer needing the upgrade to take its place.

So, what is this wonderous command? Its TOP -and you've probably seen it a lot already if you've ever looked at any of the Altiris report SQL. Restricting this is also fabulous for trickling your rollouts at a speed which suits you.

You can even use TOP to delivery trickle rollouts in unscoped deployments too (but this does take some extra thought).

The TOP command goes straight after the first SELECT in the SQL code, so modifying our collection template SQL above for a trickle deployment of 100 computers at once only gives us,

SELECT TOP 100 guid 
FROM  (SELECT  vc.guid                                   AS 'Guid', 
         MAX(dbo.Custom_convertversiontobigint(inv_aex_os_add_remove_programs.version)) AS maxversion 
    FROM   vcomputer vc 
         JOIN inv_aex_os_add_remove_programs 
          ON vc.guid = inv_aex_os_add_remove_programs._resourceguid 
    WHERE  (inv_aex_os_add_remove_programs.[name] LIKE 'Adobe Reader%' 
         AND inv_aex_os_add_remove_programs.[name] NOT LIKE '% CE' 
         AND inv_aex_os_add_remove_programs.[name] NOT LIKE '%Font%' 
         AND inv_aex_os_add_remove_programs.[name] NOT LIKE '%Language%') 
    GROUP BY vc.guid) xxx 
WHERE maxversion < dbo.Custom_convertversiontobigint('8.1.2') 

That's it -sooo simple. And pretty powerful too.

Re-structuring the Console for Less Frustrating Deployments

When I first started using the Altiris Console, I was completely bewildered by the console layout. Being forced to scatter items between the Tasks, Resources, Configuration and Reports tabs was incredibly frustrating. The 6.5 console makes many improvements, but when preparing for your software deployments you still had to navigate continually through the Tasks, Resources and Reports tabs to view and edit the console objects relevent to your rollout.

It doesn't have to be this way though. To the left I show a cutout from my Tasks Tab in the console. In here I've created a simple Software Rollout hierarchy, and placed within suitably named subfolders all the tasks, collections, policies and reports relevent to each software deployment. To show how neat the result is, i've expanded the 'Adobe Reader 8.1.2' rollout folder in the graphic to illustrate how the SWD package, the driving policy, the target collection and reports can all be nestled together.

So, how is this done? Well, a simple move doesn't work, as you severely limited in how console objects can be moved around the console tabs. To get around this, you can use the console's export and import right-click options. To illustrate try the following move using the export/import functionality to move a SWD package to the Tasks tab. The procedure is as follows,

1. In the Resources Tab, select your Software Delivery Package and Right-Click. From the right-click context menu, select Export
   

   Click to view.

2. In the 'File Save' dialog, save your package XML to a safe location on your desktop
3. In the Tasks tab, create a new folder called 'Test: Software Deployments'
4. Right-Click your new folder, and select Import from the context menu
   

   Click to view.

5. In the 'File Open' dialog, navigate to your recently saved XML file, and click OK
6. Click 'OK' on the informational dialog which appears to notify you that this object already exists, and will therefore be moved
   

   Click to view.

You should now see your SWD package appear with its pretty blue box icon. Using the same export/import procedure above, you can noproceedde by moving your reports and collections too until all the console objects relating to that particular deployment are in the same place. Cool huh?

Summary

These articles have been by nature quite tricky because of all the SQL, and in fairness it might have scared you all off completely when it comes to scoping your deployments. I hope it hasn't though.

The collection template SQL code I use pretty much for every deployment. You can put in extra filters of course for extra domain scoping etc. You'll need to create the two functions I've listed here though before this template will work. These are,

1. Custom_ConvertVersionToBigint
2. Custom_ExtractNumeric

The two last tips I hope you find useful too -I must say I love being able to put all my software rollout objects together. It certainly lowers the frustration levels when beginning a deployment.

Kind Regards,
Ian./

Building Better Collections for Software Delivery: Part 2