A few months ago, I wrote a Juice article 'Building Better Collections for Software Delivery'. In that article, we covered how you can scope your software deployments so that you only push software to the machines which required it. Today, let's see how we can simplify these collections by creating a custom SQL function to recast the version string for each application into a integer.

Introduction

In the last article, we took a look at how the Inv_Aex_OS_Add_Remove_Programs table could be used to help target software deployments. Despite the fact that this table contains software information, it is actually gathered from the nippy Hardware Inventory scan which is run daily in most environments.

The last article targeted a rollout of Adobe Reader 8.1.2 and we built three collections,

• Computers with Adobe Reader installed (any version)

  This collection contained all machines with Adobe Reader installed at any version. This allows us to see how many machines we've got across our estate with Adobe Reader installed.

• Computers with Adobe Reader 8.1.2 installed

  This collection identified all the computers with the most current version of Adobe Reader installed. Specifically, the version of the product being 8.1.2

• Computers requiring the Adobe Reader 8.1.2 Upgrade

  This collection will identified all the computers with Adobe Reader with a version number not equal to 8.1.2 -created in Altiris console by manipulating the above two collections.

These collections work perfectly fine for rolling out Adobe Reader 8.1.2 as long as you keep an eye the Adobe website for updates. This is because once 8.1.3 comes out and users start to install it you hit a problem -you'll start downgrading clients.

You see, users with admin rights will start upgrading to 8.1.3, and when they do they will appear in the Computers requiring the Adobe Reader 8.1.2 Upgrade collection. This is because in our current strategy, any computer with Adobe Reader, but not specifically at the exact version 8.1.2 will have the 8.1.2 package installed.

In some cases, this behaviour of forcing downgrades is in your favour if you want to set a solid software version distribution across your PC estate. In more flexible environments however, where you want to keep a minimum baseline to keep abreast of security alerts, this downgrading is going to be upsetting for your users.

The downgrades can be avoided of course by deactivating the policy to install Adobe Reader 8.1.2, but this approach isn't sensible if you are managing lots of software packages. What you really want is to upgrade to Adobe Reader 8.1.2 only when a lesser version is installed. This then would allow those users with admin rights to upgrade without fear of it being downgraded 24hrs later.

SQL and the Problem with Version Number Comparisons

If you want to look for computers with Adobe Reader installed with a version less than 8.1.2 you might use some SQL along the following lines...

....
where [inv_aex_add_remove_programs].[version] < '8.1.2'
...

This approach looks sensible, but it has a flaw -the comparison isn't numerical, its ASCII. To illustrate, lets take the example of a scoped roll-out some software, product X. The current release of product X is v3.12.89, and your current baseline is 3.7.2, so you write SQL along the lines of,

....
where [inv_aex_add_remove_programs].[version] < '3.12.89'
...

But does SQL think that 3.12.89 is greater than 3.7.0? To find out, try some simple SQL as follows,

if '3.12.89' > '3.7.0' select 'OK'
if '3.12.89' < '3.7.0' select 'Ooops'

The result is not good. The SQL comparison code finds the strings equal for the first two characters, but when it compares the 7 in the '3.7.0' version string to the 1 in the '3.12.89' version string, it finds in favour of the 7. What we need is a smarter way to compare version strings. What we need is a sexy SQL function to sort this mess out for us....

Converting Version Strings to Integers

Most software version strings follow the a numeric versioning scheme, expressed as three numbers, major.minor.revision

The idea is that the major number increments when the software has a seismic shift in functionality, and the minor field is incremented to reflect extra features and major bug fixes. The revision field is usually incremented for minor bug fixes and code revisions which have little impact on the functioning of the software itself.

Sometimes, a fourth field is added, the build, which references a particular compilation of the code. Of course, this scheme is only a scheme, and not a hard-and-fast rule. Some vendors might put in date fields, and others might throw in a few alpha-numerics in the strings for good measure.

Converting the Version String

To enable our version numbers to be compared, we need to convert them into a form which facilitates comparison. The simplest way I could think of was to convert the version string to an integer, giving each field a multiplier to give it numerical superiority over the field that follows

So, if we take a version string as following the form,

a.b.c.d

Then to convert this to an integer (and assuming the largest field value to be 9999 I can perform the following calculation to get a representative integer,

BIGINT = d*1 
      + c*10^4
      + b*10^4 *10^4 
      + a*10^4 *10^4 *10^4

This means that the string 3.12.89 becomes a very large integer indeed,
3001200890000

I can divide it up as follows to make my multiplications clearer,
3 0012 0089 0000

So now we have a clear way of comparing version strings. Lets now see if we can build some SQL to do s for us.

SQL Code for Converting a version string to an integer

Without beating about the bush here, the code I came up with is as follows,

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. By mulitpling
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
*/

Create 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


    /* 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((@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((@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((@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((@Str4) as BIGINT) * 1
      END
    
RETURN @VersionBigint  
END

To summarise the function of the above code, it creates a function called Custom_ConvertVersionToBigint which takes a dot delimeted version string as its argument and returns an equivalent integer. It does this by breaking up the string into the individual fields and issuing each field an appropriate multiplier according to its importance. When these new values are added together, the resulting Big Integer should be representative of the version string for numeric comparisons.

Lets give is a go,

select [dbo].[Custom_ConvertVersionToBigint]('3.12.89')

Result: 3001200890000

New Collection Code

Now we have a spunky function to allow more reliable version number comparisons, lets revisit our collections. And of course, we now only need one collection,

• Computers requiring the Adobe Reader 8.1.2 Upgrade

  This collection will identified all the computers with Adobe Reader with a version number below 8.1.2

And, all this needs is a simple revision to the SQL presented in the last article,

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')) 

So, the above code will now look for any computers which have a application named 'Adobe Reader' which has a version number less than 8.1.2.

Note how my custom function is placed last in the AND list. This is to, I hope, improve the efficiency of the query by only looking at the version details if all the naming filter criteria have been satisfied.

Summary

Today's article has been mostly an exercise in SQL programming, and I have today illustrated how to create a SQL function to assist your software delivery collections. In the previous article we used some relatively simple SQL and three collections in order to identify the machines which require upgrades. In this article I've introduced a function which allows you to drop the collections used to just one.

The sharp-eyed, and perhaps more experienced software deployment Guru's might see some flaws with this approach,

• Some horrible apps have version strings using commas, not full stops
• Some apps have a variety of non-numeric characters in their version strings
• Some horrible apps don't remove their previous version from the Add/Remove programs list

And these, will of course be dealt with in Part 3, where I'll also show you a cool trick to keep your collections, policies and software delivery packages all in one place.

Kind Regards,
Ian./

Building Better Collections For Software Delivery

Building Better Collections for Software Delivery, Part 3