In this article, I hope to shed some light on the process involved when creating collections for software delivery. To put the article into context, I'll be covering the key points with respect to an Adobe Reader upgrade to version 8.1.2.

In order to reveal the upgrade paths you might have to consider in testing, I provide some simple SQL. This code also helps by revealing naming and version conventions which is critical when designing your queries. As the SQL created by collection query builder can be quite off-putting, I'll show how this code can be simplified. This makes it clearer (and therefore more useful) as a collection template. Further, for those who rely on heavily on query builder, I illustrate a common pitfall for the unwary.

To finish, I present a simple report tip which is fabulous for monitoring the progress of rollouts.

Gathering Data - Client Inventory

Collection building for software rollouts is quite daunting to those new to Notification Server. In this article we'll explore the process behind building collections for the Adobe Reader so that we can upgrade to the latest version 8.1.2.

The first thing to do when tasked with any software upgrade is to assess its current distribution across your computer estate. This is vital - you need to know the scale of the rollout for bandwidth issues, and the likely upgrade paths. It has the added benefit of giving you vital naming clues to help you formulate your collections. For this, we could use the data from either the hardware or software inventories gathered by Inventory Solution.

To the left is a screenshot from Resource Manager showing the Software Inventory data classes. This information has been collected from the software inventory client disks scan which looks for dynamic linked library (.DLL) and executable (.EXE) files. As Altiris is aware of the major software vendors' naming irregularities, this scan is quite smart and can build up a good software profile if you ensure inventory solution is kept up-to-date. The thoroughness of the scan comes at a price however -the software inventory scan is the most intensive of all the inventories.

In contrast to software inventory, the Hardware Inventory is a far lighter, and therefore a far quicker scan. Although its main task is to gather your system device details, a number of OS data classes are also populated. In the Resource Manager screenshot to the left, you can see that one of the OS data classes is the Add/Remove Programs list. While this list is not foolproof in determining what software is installed, it is getting harder to find legitimate software packages which don't put an entry in here.

It's very much swings and roundabouts when it comes to deciding which inventory you decide to use when planning software delivery. I personally like to grab the data from the Add/Remove programs list. Here's why:

• Hardware Inventory is quick
  Hardware Inventory is so lightweight, you can force an inventory run at the end of a software delivery task to instantly refresh your Add/Remove program data. This is great for software delivery 'console huggers'.
• Software Inventory determines file presence
  This is a small point, but it can occasionally catch you out. Software inventory cannot tell the difference between a program which is installed, or one which is lying dormant on a PC as source files (this can have its uses though, as some vendors do have as a licensing condition that having the source on a computer counts as an install).
• Software Inventory requires Inventory Solution Updates
  The Software classes built by software inventory are Altiris' interpretation of the software profile on each PC. Altiris use their experience of vendor versioning and naming irregularities to mould the gathered inventory data into classes which are more reliable for your queries. The downside of this is that you have to keep your inventory solution updated so that your software classes to get the latest tweaks. In a highly change-managed environment, this can be a pain.

Computer Collection Overview

Focusing now on the Adobe Reader upgrade, in order to configure software delivery to update Adobe Reader, we need to identify the machines which have Adobe Reader installed at a version number less than 8.1.2

We tackle this by building collections - that is a logical grouping of machines set apart by some commonality in their configuration.

The figure opposite formalises the Adobe Reader Upgrade using some a couple of set diagrams. Set C represents the collection of machines requiring the upgrade, this being derived from the collection of machines with Adobe Reader installed (Set A) minus the collection of machines with Adobe Reader installed at version 8.1.2 (Set B).

Whilst we technically require only one collection to implement our software upgrade (that being the collection of computers requiring the Adobe upgrade), it is quite useful to create all three collections depicted above.

• Computers with Adobe Reader installed (any version)
  This collection will contain 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 will identify 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 identify all the computers with Adobe Reader with a version number below 8.1.2 -created in Altiris console by manipulating the above two collections.

Inventory Data Quality

When searching software distributions across our PC-estates, we often blindly trust that vendors' naming conventions have been consistent across their product lines through the years. This however is not always the case -vendor versioning and naming irregularities can creep into the neat software tables built by Altiris. Checking your tables with simple queries can help identify possible pain points, and allow you to amend your collection code so that they contain the computers you expect them to.

Lets first take a look at what happens if you rely on software inventory, but don't keep inventory solution current. On our server, inventory solution hasn't been updated for a few months - so let's take a peek at the Adobe software table. Below is a query which looks for all entries in the Adobe software table which have "Adobe Reader" in the name,

SELECT  COUNT(* )   AS COUNT, 
     productname, 
     productversion 
FROM   aexinv_aex_sw_adobe 
WHERE  productname LIKE '%Adobe Reader%' 
GROUP BY productname,productversion 
ORDER BY productname 

This query allows us to view the name and version variations, and as you can see the result is not pretty. Altiris doesn't do badly on being able to standardise the product name to "Adobe Reader" - just a couple of machines with versions 7.0 and 7.0.6 haven't had their names normalised. The version numbers are however a bit of a mess -they span from a simple "7" to "6.0.2a.2004052400 CE". This is not helpful for SQL queries.

A real problem which has surfaced however is this: Adobe Reader version 8.1.2 is not listed at all. This is odd -I know for a fact we have distributed this update to over 1000 PCs already.

A little digging reveals that the machines with 8.1.2 installed have for some reason been filed in the Adobe table under version 8.1.0.2007051100. This is a symptom of our inventory solution software criteria being many months behind the updates.

Let's now take a look at the result from hardware inventory. The hardware inventory table we need to query is inv_aex_os_add_remove_programs,

SELECT  COUNT(* ) AS 'Count', 
     [name], 
     version 
FROM   inv_aex_os_add_remove_programs 
WHERE  [name] LIKE '%Adobe Reader%' 
GROUP BY [name],version 
ORDER BY [name] 

This actually looks much better for version comparisons, as the formatting is much more consistent. Further, the Add/Remove program data can do something the software inventory could not -it is able to distinguish between machines with 8.1.2 and 8.1.1. This might seem pedantic, but in this case its important -only 8.1.2 addresses a recent security advisory.

The formatting of the data in the tables is quite important when it comes to building collections. And looking at the Add/Remove table data brings to mind the following points,

• Are the Language Packs going to be a problem?
  Can language packs exist at a lower version than the Adobe Reader package? If so, we'd need to ensure our queries don't confuse the language packs with the Adobe Reader package itself. As it happens, some testing with 8.1.2 upgrades shows that upgrading removes any previously existing Adobe language packs -so no need to worry on this score. This is a good example though of where product add-ons might conflict in your queries and be confused with the package itself.
• Our Collections should ignore Adobe Reader CE versions
  The CE version is the central european version of Adobe Reader, and this can only upgraded to version 7. Sensible therefore to ignore this in the rollout (only one person uses this anyway, so we can chat to them directly about this Adobe upgrade.)

So, with these points in mind, let's move on and start building our collections.

The "Computers with Adobe Reader (any version)" Collection

When you create collections, you've got to decide first where to put them.

Opposite is a section of the Resources tab in the Altiris Console, illustrating an SWD hierarchy containing Adobe Reader, Microsoft Office and AV (Sophos). What really matters is not the specific root location of your SWD collections, but rather that you form a decent hierarchy to prevent collection sprawl.

Creating the Basic Collection with Query Builder

To create a collection, right-click your destination folder in the console, and select "New" and then "Collection" from the context menu. The "New Collection" window will then appear as I've shown opposite where you can see i've entered in the collection name. We have the option of creating our query using SQL directly here, or by using a query builder. Lets start with the Query builder -select the Resource Type as "Computer" and click the "No Filters" hyperlink to begin building this computer collection according to our Adobe Reader criteria.

On the filter screen which now appears, we get the opportunity to configure a filter to select specific machines based on our inventory criteria. In the Table dropdown, select "Inv_Aex_OS_Add_Remove_Programs" which is the table collected from the hardware inventory which holds the OS Add/Remove program data. The fields dropdown will automatically populate with the fields for that table (nice) and here i've selected "Name". The rest is straight forward too -the operator is "Like" and the value is "Adobe Reader%" -the % is a wildcard and just means that anything can follow. This is essential as we need to pick up strings like "Adobe Reader 7.0.1", "Adobe Reader 8.1.1" etc..

Clicking OK presents a summary screen which allows us to manage our filters. Click OK to return to our "New Collection" window -notice how it now says we have "1 filter".

Simplifying the Collection SQL

If we now click the "Enter SQL Directly" radio button we should see the following SQL,

SELECT guid
FROM  vresource
WHERE resourcetypeguid IN (SELECT resourcetypeguid
              FROM  resourcetypehierarchy
              WHERE baseresourcetypeguid = '493435f7-3b17-4c4c-b07f-c23e7ab7781f')
    AND (guid IN (SELECT [_resourceguid]
           FROM  [inv_aex_os_add_remove_programs]
           WHERE [inv_aex_os_add_remove_programs].[name] LIKE 'Adobe Reader%'))

This looks quite daunting - and it needn't be. This query is first doing a SELECT covering all possible resources (vResource), filtering on the BaseResourceTypeGuid being '493435f7-3b17-4c4c-b07f-c23e7ab7781f'. This nice long GUID just represents Computer resources, but there is already a view specific to computer resources though called vComputer! So, we can make the above SQL far less intimidating by removing the vResource filtering, replacing it with vComputer,

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

This is instantly much nicer. This SQL says collect all the computer GUIDs from the Add/Remove program table where the [name] field begins with "Adobe Reader". From here, any GUID found here which is also present in the vComputer table is SELECTed.

Modifying the Collection to Ignore Adobe Reader CE

Modifying the collection to ignore the Central European installations of Adobe Reader is quite simple. We just exclude the "Adobe Reader" program entries which also the letters "CE". The SQL for this is then,

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

Modifying the Collection to Ignore Language Packs

Whilst I'm fairly sure that language packs won't present a problem, I'm open to the possibility Adobe Reader can be upgraded and a previous language pack be present. So, to be sure, let's add a modification to the collection to be sure these packs are excluded from the collection members. This is actually very similar to the above modification; we just want to ignore "Adobe Reader" entries which also include the word "Language",

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

Testing the Collection

Click Apply, and check the sanity of the result.

In our case, we're just one shy of 2200 installations which sounds about right.

The "Computers with Adobe Reader 8.1.2" Collection

This collection is going to be so similar to the one we've just made, that the quickest way forward is to clone it. To do this, simply right-click the "Computers with Adobe Reader (any version)" collection in the resource window, and select "clone" from the context menu.

In the pop-up box that appears, change the name to "Computers with Adobe Reader 8.1.2". You should now see the "Computers with Adobe Reader 8.1.2" now appear beside the one created earlier.

The only amendment we need to make to this collection is to refine the SQL thereby ensuring that only Adobe Reader installations at version 8.1.2 are selected.

Query Builder Problem

Even though we've already committed ourselves to hand-tweaking the collection SQL directly, I feel its important to emphasis a problem you might encounter using the Query Builder. To illustrate, you'd imagine the most straightforward way to search for Adobe Reader installations at version 8.1.2 would be to have one filter search for "Adobe Reader", and to add another searching for a program version of "8.1.2". But, this would not perform the SQL we expect -and let me explain why.

These two filters together actually create a collection of computers which,

• have installed a piece of software whose name begins with "Adobe Reader"
• have any software installed at version "8.1.2".

This other piece of software could be flash at version 8.1.2, or anything. The point is it isn't necessarily Adobe Reader. This problem only surfaces when comparing fields within the same table. Be warned.

Editing the SQL to SELECT Version Number

Having done a lot with the SQL already, its only a minor amendment to the code to filter on the Adobe Reader version number being "8.1.2". To do this, choose the "Edit SQL Directly" radio box when editing the collection. Amend the SQL as follows so it reads as follows,

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 '%Language%'
AND [inv_aex_os_add_remove_programs].[version]='8.1.2')

Here the last line just adds another condition, specifying that the version field should be '8.1.2'.

Testing the Collection

Click Apply, and once again check the sanity of the result.

The "Computers Requiring Adobe Reader 8.1.2 Upgrade" Collection

You'll be pleased to hear that this is an immensely simple collection to create. This collection is equal to the collections of computers which Adobe Reader installed at any version, minus those which are already uptodate. Altiris allows you to build collections exactly in this way, by introducing specific exclusions and inclusions.

1. Create a new collection in the rollout folder. Call it "Computers requiring Adobe Reader 8.1.2 Upgrade"
2. Under "Explicit Inclusions and Exclusions", beside "Collections to be included in this collection" click "Select a collection".
3. In the Collection Selector dialog, navigate to and select the "Computers with Adobe Reader (any version)" collection. Click Apply.
4. Under "Explicit Inclusions and Exclusions", beside "Collections to be excluded in this collection" click "Select a collection".
5. In the Collection Selector dialog, navigate to and select the "Computers with Adobe Reader 8.1.2" collection. Click Apply.

The collection should then look as shown on the left, and this is the collection you should be pumping your software delivery too.

Now, all you need to do is point your "Adobe Reader 8.1.2 Upgrade" task at the "Computers requiring Adobe Reader 8.1.2" collection. It a good idea to run this task daily on this collection -I often find that configuring tasks to run ASAP isn't very robust.

Reporting

The three collections are fantastic for initiating and monitoring the progress of rollouts, but I am usually bothered by the lack of detail I can see in the collection view. To help assess whether machines are having problems installing the update, I like to see how many days its been since they've last contacted Notification Server. This is because only machines which are connecting to Notification Server will receive the SWD task, so machines which are turned off or absent for extended periods will skew the rollout stats. For get this additional information, we need a report.

To start, I recommend a similar report hierarchy in the console "Reports" tab, as you have for the collections in the "Resources" tab. This will make navigation much easier.

To create such a report, right-click your destination report folder in the console and select "New" and then "Report". Give the report name "Computers requiring the Adobe Reader 8.1.2 Upgrade", and paste in the SQL below, and click "Finish"

SELECT  TOP 2000 vc.guid                    AS '_Guid',
         vc.[domain]                  AS 'Domain',
         vc.[name]                   AS 'Name',
         Datediff(dd,rus.[modifieddate],Getdate())   AS 'Last Seen (Days)',
         CONVERT(VARCHAR(11),rus.[createddate],103)  AS 'Agent Install Date'
FROM   vcomputer vc
     JOIN vresource vr
      ON vc.guid = vr.guid
     JOIN resourceupdatesummary rus
      ON vc.guid = rus.resourceguid
       AND rus.inventoryclassguid = 'C74002B6-C7B9-47BB-A5D6-3031AF73BB8D'
WHERE  vc.guid IN (SELECT w2._resourceguid
           FROM  inv_aex_ac_client_agent w2
           WHERE w2._resourceguid = vc.guid)
ORDER BY 'Last Seen (Days)'

The trick now is to edit the report you've just created, and in particular the 'Level 0' SQL query we've just pasted in. We now get the option to "Filter this report against defined collections". Check this box, and select the "Computers requiring Adobe Reader 8.1.2 Upgrade" by clicking the "Select a Collection" hyperlink along side "Collections included in this collection".

Click "Finish" and then "Apply" to save the changes, and now you have a report which shows the computer details (Name/Domain) and how long its been since NS last saw this machine along with the agent install date. You can customise the SQL further by adding other useful bits like OS, Service pack, IP address and free disk space if you want.

The beauty of this method is that the report can be cloned and repointed to any of your collections to give this view. Quite powerful I find.

Summary

I hope this article has been useful, and not too scary with all the SQL i've included. Its been a long trek, but hopefully this has got you thinking about,

• Software naming conventions and add-ons, and how these might affect your queries
• Whether query builder is right for you
• How to target the upgrade paths you need to consider in advance of rollouts
• Writing your own SQL collection code ;-)
• Using Reports with collection filters to give more detailed information

Kind Regards,
Ian./

Building Better Collections for Software Delivery: Part 2