Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Building Better Collections for Software Delivery: Part 2

Created: 26 Jun 2008 • Updated: 09 Apr 2009 | 10 comments
Language Translations
ianatkin's picture
+5 5 Votes
Login to vote

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

Comments 10 CommentsJump to latest comment

carlsson's picture

Thanks for these articles i look forward to them and learn so much from them :o) C

Hojiblanca

0
Login to vote
Nebster's picture

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

shouldn't have 2 *10^4 on the "c" part

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

0
Login to vote
ianatkin's picture

All in the subject really! I'll edit the article to remove the erroneous 10^4. The code is OK -its just my lazy documentation of it!

Cheers,
Ian./

Ian Atkin, IT Services, Oxford University, UK

Connect Etiquette: "Mark as Solution" those posts which assist you most in resolving your problem, and give a thumbs up to useful articles and downloads

0
Login to vote
Nebster's picture

Hehe, can't be as bad as mine!

Whatever programming I do, there's no documentation at all!

0
Login to vote
ntownsen's picture

Ian:

Your articles are wonderful.  Thank you. 

I understand the concept this article explains, but I do not know how to implement it.  What do I do with the 'Converting the Version String' programming and the 'SQL Code for Converting a version string to an integer'?  I image that once I get them saved to the correct location the SQL update to the collection code will be able to work.

Lots to learn!

Nancy

0
Login to vote
ianatkin's picture

Hi Nancy,

First -thankyou for the compliment. It's good to see that this article series on software deployment is still useful!

The programming blocks need to be copied into a query window within SQL Server Management Studio.

Now, if you can't find this under your Start Menu (in you SQL Server folder) then it's likely you haven't installed this feature with SQL Server. If that's so, just modify the install (through Add/Remove programs) and add this feature in. I think this is installed with  Management Tools -but not 100% sure on that!

Kind Regards,
Ian./

Ian Atkin, IT Services, Oxford University, UK

Connect Etiquette: "Mark as Solution" those posts which assist you most in resolving your problem, and give a thumbs up to useful articles and downloads

0
Login to vote
KSchroeder's picture

Hi Ian,

Just wanted to mention that we may be resurrecting your fine work here in our 7.1 environment, and thanks again!

Kyle

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

0
Login to vote
JeanWilson's picture

That would be awesome to update this for 7.1 or 7.5

0
Login to vote
JeanWilson's picture

That would be awesome to update this for 7.1 or 7.5

0
Login to vote
ianatkin's picture

Hi BillyJean,

The software management framework does a lot of this natively so I think implementing this technique in the 7.x world means you'll lose some of the benefits of the this framework.

In the v7 world, the focus is on deploying software using rules as the primary mechanism rather than filters. Compliance is useful as it means you can target your whole estate (i.e. a simple filter) with a piece of software, and then design a software complliance rule to ensure it only downloads and installs on machines that lack it when the software's compliance schedule runs.

Advantages of using v7's software management framework features are,

  1. The compliance dashboards which give you an instant view of your estate's compliance across the various software packages you are rolling out
     
  2. No more software being re-installed erroneously as a result of the server-side inventory not being refreshed quickly enough to exclude the PC from the deployment collection/filter.

Disadvantages are,

  1. Setting up deployments using the software management framework is much more cumbersome, requiring many more mouse clicks and navigation points (which is frustrating if you don't like the web console)
     
  2. There is a bit of legwork in understanding all the pieces which are present in the software management framework and what the implication of (using them)/(not using them) are. i.e. in what  scenarios  is superscence useful... when to use applicability rules... etc

I would say though that once you have a process with 7.x using the software management framework that you'd likely find the effort to get there worthwhile.
 

Kind Regards,
Ian./

Ian Atkin, IT Services, Oxford University, UK

Connect Etiquette: "Mark as Solution" those posts which assist you most in resolving your problem, and give a thumbs up to useful articles and downloads

0
Login to vote