Client Management Suite

Notification Server is a complex beast, and if it has a beating heart it is surely the collection update schedules. These schedules ensure all our collections are fresh, and reflect the latest client inventory. These update schedules are absolutely critical for software deployment activities as they ensure our tasks are scoped across our computer estate as intended by our SQL filters.

One problem which emerges when you dig into the collection update schedules is that its not entirely clear sometimes which collections will update, and why. For most Altiris Administrators, operating in blissful ignorance isn't an issue, as simple collections will always behave as expected. If however you are the kind of admin who is always trying to push Altiris that little bit harder, then you'll probably need to understand a bit more about the collection update schedules.

Today's article aims to explain to you ambitious admins out there the hows and whys of collection updating, so that you can understand under what conditions your collections will and won't update. I attach a couple of SQL queries to help flag up collections which are being missed by the automatic update schedules, and further show a fire-and-forget mechanism to dynamically detect such collections and update them in a resource efficient manner.

Attached to today's article is a zip which contains the SQL and batch file discussed below.

Membership Update Methods

When you create a new collection, the first thing you do is decide whether the collection is going to be updated manually, or automatically. In most situations, collections are the result of a SQL query and you'll want the computers listed within to reflect the most current client data available from your CMDB. For this reason the default Membership Update type for collections is automatic.

Contrary to initial expectations though, collections configured to automatically update will not necessarily update in accordance with the internal NS schedules. This is because collections configured with the automatic Membership update method will only update if they are considered active.

So, what defines a collection as being active? It turns out that this means that the collection has an enabled policy associated with it. Whilst this seems clear, the opposite turns out not to always hold true. Collections which aren't associated with enabled policies can in fact also be updated on the automatic update schedules! This undocumented behaviour can lead unwary Altiris Administrators to erroneously conclude that ALL collections configured to automatically update will update on the internal schedules. Further, when you select collections in the console you are lulled into a false sense of security by the fact that this manual action triggers an ad-hoc automatic update. It is therefore very hard to see collections which aren't updating automatically.

It is however critical to understand what collections will and won't automatically update. When scoping software deployments you'll likely become very confused if you don't have a good handle on what is happening under the hood.

The Collection Update Schedules

As mentioned above, there are two types of updating strategies collections can use -automatic and manual. Collections which are configured to automatically update will be updated periodically with the collection update scheduled tasks. Those configured to manually update will require you to log into the console to force an update. As already mentioned, the membership of the majority of collections we create we'll want to update automatically, so Altiris configures this as the default behaviour.

The collection updating schedules are found in the Altiris Console under, Configuration -> Settings -> Notification Server Settings -> Automatic Collection Updating. Below is screenshot from a production NS Server,

From the above we see that,

1. The Delta Update schedule is configured for 15 mins and 45 mins past the hour
2. The Policy Change schedule is configured for on the hour and on the half hour
3. The Always Refresh schedule is configured for 2.45am daily

For clarity, the schedule definitions are,

• Policy Changed Collection Update Schedule
  Updates all collections which are in use when one or more policies have been created or modified.

• Collection Delta Update Schedule
  Updates all collections which are in use if there is any new or modified inventory since the last collection update

• Always Refresh Collection Update Schedule
  Updates all in use collections regardless of inventory status or any of the previous settings.

Notice I have emphasized the term "in use" in each of the above definitions.If you recall, Notification Server by design only considers a collection '"in use" if it has an active policy associated with it. This is sensible from a CPU resource viewpoint -after all why bother updating collections which don't appear to actually do anything? Generally speaking this is not a problem, the gotcha (and focus of this article) is that sometimes you and your NS might disagree on what it decides isn't in use.

Collection Includes and Excludes

Collection Includes and Excludes are usually no stranger to those NS Administrators implementing software rollouts. They give you the ability to build one collection based on the contents of other collections. But, have you ever paused to think as to whether NS considers collections referenced in an include/exclude as in use?

To illustrate, let's consider a collection include example. Imagine you have a software rollout going to five departments, and you choose to create a master collection for the deployment, to which you intend to sequentially add each department collection as the deployment progresses. In this case, your department collections will not have a policy associated with them -only the master collection has the policy applied. As a result, Notification Server should not actually consider your department collections as in use.

This can't be right though can it? In all these years of performing rollouts with Altiris, this would have presented a pretty big problem if include/exclude collections weren't automatically updated right? Let's do an experiment to check out what happens. Below is a folder I've created in my Tasks tab in the console, and in it I have a test package, a policy (enabled) and 3 collections.

The three collections (all of which are configured to automatically update) are,

1. Test_Target_Collection_For_Rollout
   This is the master collection for the rollout, the one the software delivery policy applies too. It has an explicit inclusion, this being the collection Test_Nested_Collection'

2. Test_Nested_Collection
   This collection isn't associated directly with the software delivery policy (only indirectly through the explicit inclusion). In a real rollout you might have several such nest collections corresponding to pilot groups, departments etc...

3. Test_Unassociated_Collection
   This collection isn't associated with the software delivery policy, either directly or indirectly. Its there as a control so that I can check the sanity of my collection updates.

Given that that software delivery policy applies only to the collection Test_Target_Collection_For_Rollout , this should mean that in a live environment (where clients are regularly posting inventory) that this collection should be updated on the delta update. In fact, only this collection should be updated on the delta update, as this is the only collection with a policy applied to it. For the schedules above, this means our collection will be updated at around 15 minutes and 45 minutes past the hour.

As just looking at a collection can trigger an update, its difficult to see when collections last updated automatically without resorting to SQL. In our case , we want to look then at resource collections with a name like 'TEST_'. Resource Collections though aren't the only collection types, so for efficiency we'll filter our query also by the collection class guid,

The following SQL should do the trick,

select i.name [Collection Name],c.lastupdated [Date Last Updated]
from item i join collection c
on i.guid = c.guid
and i.classguid like 'B8B666E1-FED3-4482-8D5A-0895658317B2'
and i.name like 'Test_%'

This SQL will harvest the update times of collections whose names are prefixed with "Test_" which have the ResourceCollection class guid. The result is as follows,

And the result is interesting. I've highlighted in bold the collections Test_Nested_Collection and Test_Manual_Collection because these have both been updated at the end of the last delta update. This means that the nested collection is updated in the delta update even though there is no policy directly applied to it. Not that we should complain, this updating behaviour is exactly what we'd like. Incidentally, the other two collections have update times which simply reveal the last time they were clicked on in the console.

Further, it turns out that if I add another level of nesting with a collection include/exclude (by adding Test_Unassociated_Collection temporarily as an include to Test_Nested_Collection) then this deep nested collection too is updated on the delta update.

So what is causing the nested collection to update? According to KB26794, these nested collections shouldn't update as there are no policies directly applied to them. It states though that we can see which collections are updated by looking at the stored procedure spGetCollectionUpdateList . This takes as an argument the guid of the update schedule type,

So we should be able to predict the collections being updated on the delta update with the following query,

exec dbo.spGetCollectionUpdateList null, null, 'A4519B4B-4D5B-4CD4-A534-965A8A8EC4E55' 

Only this doesn't return nested collections -only the master collection targeted by the software delivery policy is returned. The reason for this anomaly can be revealed by a SQL trace. It turns out that as each in use collection is updated, the underlying engine code triggers an update to every subcollection should they exist. So even through nested collections are not generally considered to be 'in use', they still will get updated.

This does present a problem in predicting which collections are going to be updated however, which we'll come to later.

Referencing Collections in T-SQL

So, it seems for most day-to-day tasks we're safe, collections are updated in a sane manner, even if we use includes and excludes. However, there is another way in which we might reference collections for software rollouts -within custom T-SQL code directly,

Where here I show as an example a collection targeted by the policy to rollout Adobe Digital Editions. The collection SQL (not shown) looks at all the computers which have older versions of Adobe Digital Editions, but then this last piece shown in the window helps returns the GUIDs which are also present in the Adobe Digital Editions 1.7.2 Deployment Group collection (see my Software Delivery articles for further details). This is a strategy similar to that used by Patch Management where you have the concept of creating intersect collections.

In this case though, the Adobe Digital Editions 1.7.2 Deployment Group collection is neither nested, nor is it targeted by a policy. It will therefore never be updated (with the exception of when we navigate to it in the console, triggering an ad-hoc update). Here then is a problem which we need to solve -how to update collections which are missed by design in the automatic update schedules.

Standard Methods for Forcing Collections to Update Automatically

As you might expect, this problem has been around for a while. Altiris KB26794 offers two approaches for forcing collections to update,

1. Associate them with a Policy
   You can create a dummy software package which does nothing, and create an active policy which runs in say 2099. Target this policy at any collections you need to automatically update, but won't under normal circumstances. This has the advantage that you are using NS's own internal mechanisms to force your collections to update, and is CPU efficient. A disadvantage is that in a large enterprise it can be a tedious process, which by its manual nature can often be forgotten. Linking dummy policies to collections also adds an additional untangling step when retiring and deleting software rollout console objects, which can be very frustrating.

2. Use the Directory Collection Update Utility for Hierarchy Update
   The UpdateAllCollectionsInDir.exe utility in the KB article allows you to update collections en-mass in a folder hierarchy. The idea being to run this regularly as a scheduled task to ensure your collections undergo an update. This will however update all collections in the specified hierarchy, whether they have recently been updated or not. This can therefore whack your CPU in a similar vein to a delta update, so best used with care on small folder hierarchies.

3. Use the Collection Update Utility, specifying individual collections
   This UpdateAllCollections.exe utility allows you to target specific collections for update. The idea is to collect and maintain the GUIDs of all the collections in your hierarchy which you require to update automatically but are missed by the delta update. You can then call the Collection Update Utility with these GUIDs in a scheduled task. It has the advantage that its CPU efficient, and unlike the dummy policy association is easier to clean up when retiring/deleting rollouts. In a large enterprise, maintaining such lists can be tedious, eventually becoming as a result a neglected task.

In summary, none of the above options is ideal. From the viewpoint of an Altiris Administrator, we do not want the job of our software rollout engineers to be complicated by NS 'quirks'. When our rollout guy creates a collection and specifies the membership update as being automatic I would like them to rest happy in the knowledge that collection will indeed update automatically. I do not want to involve an additional manual step. So I set about looking for a CPU efficient way of combining the above options to enforce a collection update across a folder hierarchy. So, prepared with a 48 pack of Jaffa Cakes I opened up SQL Server Management Studio and started typing...

A New Method for Forcing Collections to Update Automatically

You'll be pleased to know that riding on the blissful wave of a Jaffa cake sugar rush, I came across a neat way to ensure all your collections get updated in a friendly manner. Here's a list of facts which I mulled over in the thought process,

• There is a stored procedure spGetCollectionUpdateList. This provides a list of all collections due to be updated on the automatic update schedules
• Nested collections (also known as sub collections) will update automatically if their root (parent) collection is updated
• There exists an Altiris utility UpdateAllCollections.exe will trigger a collection update on specifically targeted collections

Let's now assume we can tell our software rollout engineers to limit their software rollout collections to a folder hierarchy (or a limited set of folder hierarchies). We can then build our list of collections which won't be automatically updated by,

1. Creating a table of collections configured with the automatic membership update method from within the folder hierarchy
2. Exclude from this table all collections which are nested (sub collections will be updated when their parent is)
3. Exclude from this table all collections which are already targeted by an active policy, and therefore scheduled to update on the delta update

The result is a table of collections which if we updated would ensure (when combined with a delta update) that every collection within the specified folder hierarchy is updated. And because we are avoiding collections directly updated through the delta update schedule, it will be CPU efficient as we will have designed our update to have minimal overlap with the automatic schedules processes. What we have with this targeted approach is an alternative to the UpdateAllCollectionsInDir.exe utility provided by Altiris.

So, to summarise -we've now got a method in mind which has the advantage of excluding lots of collections already scoped in the automatic collection update schedules. This is good -it prevents us wasting vital CPU cycles updating masses of collections which are already up-to-date. This mechanism is also automatic -one in place we don't have to manage any separate manual processes. New collections requiring updates will be added automatically, and they will likewise disappear automatically when deleted.

Let's take the second step then and see if we can write the SQL necessary to expose the collections we need to target.

T-SQL for viewing Root Collections not Updated during the Automatic Update Schedules

Having come up with the above general framework for identifying collections which aren't being automatically updated, we need to now put this down in T-SQL. What follows now is my script view_untargeted_parent_collections.sql, a T-SQL script which reveals all root (parent) collections in a folder hierarchy which are not due to be updated on the automatic update schedules. This is despite these collections being created with the automatic membership update method -they simply miss out on the automatic schedules because they don't have an enabled policy directly applied to them  (or are not explicitly in a nested hierarchy rooted in a collection with a policy applied).

/* Script: View_Untargeted_Parent_Collections.SQL
Author: Ian Atkin
Version: 1.1
Date:  19th March 2010
 
Function:
This script will reveal all parent collections within the specified
folder hierarchy which are not updated on with the automatic collection
update schedules.

Notes:
To amend this script for you environment just set the @RootFolder
variable to the GUID of the folder you use to store your software
rollout collections.
*/
use Altiris;
Declare @RootFolder uniqueidentifier
set @RootFolder = 'GUID_OF_ROOTFOLDER_GOES_HERE'

/* Create temporary tables
   #UpdatingGuids is the list of collections which will be updated on the next delta schedule
   #TreeGuids is the list of objects under a specified folder GUID */

create table #UpdatingGuids (guid uniqueidentifier)
create table #TreeGuids (guid uniqueidentifier)
Declare @DeltaUpdateSchedule uniqueidentifier

set @DeltaUpdateSchedule = 'A4519B4B-4D5B-4CD4-A534-965A8A8EC4E55'

/* Stage 1: Collect all console objects within the specified root folder. 
      Store them in the temporary table #TreeGuids */
insert into #TreeGuids exec [dbo].[spGetFolderDescendants] @RootFolder,1


/* Stage 2: Collect a list of all collection guids which will be updated on
      the next delta update. Store result in #UpdatingGuids */

insert into #UpdatingGuids exec dbo.spGetCollectionUpdateList null, 
null, @DeltaUpdateSchedule 


/* Stage 3: Add to the #UpdatingGuids any collections which are nested. We don't need
      to worry about these as their update will trigger should their parent update */

insert into #UpdatingGuids select subcollectionguid from CollectionIncludeCollection
insert into #UpdatingGuids select subcollectionguid from CollectionExcludeCollection


/* Stage 4: Remove from #TreeGuids all collections present in the #UpdatingGuids table.
      The result will contain all collections which won't be updated automatically. */

Delete from #TreeGuids where guid in
(select * from #UpdatingGuids)


/* Stage 5: Join the results to the item, collection and vCollection tables to 
      force only collections with the automatic membership update
      method to be returned. */

select vc.name,c.lastupdated,vc.guid from #TreeGuids 
join vCollection vc on #TreeGuids.guid =vc.guid
join collection c on vc.guid = c.guid
join item on #TreeGuids.guid =item.guid
where item.state like '%ManualUpdate>false%'

Drop table #UpdatingGuids
Drop table #TreeGuids

Even if you do nothing else with today's article, I recommend you give this script a try in your environment and see if indeed your collections are updating as think they should. This process will of course pick up collections which have policies against them which are now disabled. As a result, you could argue that CPU time is wasted in this attempt to automatically target collections needing a forced update. Whilst this is true, this method will still be a lot less CPU intensive than force-updating the entire folder hierarchy.

T-SQL to Generate the UpdateAllCollections.exe Command-line

Now we need to tie the Altiris collection update utility together with the above T-SQL code to force update our collections. A good approach is to fire off regularly repeating scheduled task on your Notification Server which calls a batch file, say force_update.bat. This batch file will,

1. Call sqlcmd.exe to execute a T-SQL script which will output the collections to the file Call_UpdateAllCollections.bat
2. Execute Call_UpdateAllCollections.bat

This approach means that you'll need to install SQL Server's Client Components on Notification Server if your SQL server is off-box. We'll need to update the previous SQL script though with a SQL cursor if we want to list all the GUIDs on a single line for the Collection Update Utility. This new script, Export_Untargeted_Parent_Collections.sql is simply the previous T-SQL with section 5 onwards replaced with the T-SQL below

/*----------------------------------------------------------------
Script change to View_Untargeted_Parent_Collections.sql to generate
output as single string of collection GUIDs.

New script called Export_Untargeted_Parent_Collections.sql
----------------------------------------------------------------*/


   /* Stage 5: Join the results to the item and vCollection tables to 
	     force only collections with the automatic membership update
         method are returned. */


insert into #output 
select cast(vc.guid AS varchar(128)) from #TreeGuids 
join vCollection vc on #TreeGuids.guid =vc.guid
join collection c on vc.guid = c.guid
join item on #TreeGuids.guid =item.guid
where item.state like '%ManualUpdate>false%'


   /* Stage 6: Use Cusor to loop through every row in the #output table, concatenating
    the Guids into a big list. If more than 20 GUIDs output a commandline to stop GUIDs
    overflowing command shell buffers */


declare @MyGuid varchar(1024)
declare @GuidList varchar(2048)
declare @i int
set @GuidList = "

set @i=0
declare outputCursor CURSOR FOR
select guid from #output

open outputCursor

Fetch next from outputCursor
into @myGuid

while @@FETCH_STATUS=0
BEGIN
 set @GuidList = @GuidList + @myguid + ' '
 set @i = @i +1
 if @i % 20 = 0 
 begin
  select 'UpdateAllCollections.exe ' + @GuidList 
  set @GuidList="
 end
 FETCH NEXT FROM outputCursor
 into @myGuid
 
END
Close outputCursor
DEALLOCATE outputCursor


   /* Stage 7: Write remaining UpdateAllCollections.exe line, including all the GUIDs as argument. */

select 'UpdateAllCollections.exe ' + @GuidList 
drop table #updatingguids
drop table #treeguids
drop table #output

Here, instead of outputtng a nice table we insert the results into a new temporary table #Output. This table is then examined, row by row using a cursor so that we can generate a single string as the argument to the UpdateAllCollections.exe command. I've chosen to execute all the GUIDs in batches of 20 per command line to avoid crossing the 1024 character limit. The reason for clumping as many GUIDs together for each execution of UpdateAllCollections.exe is simply to spread the overhead of the initializing the utility. If we were to just have one GUID called per command execution the script would take a lot longer to run.

To complete, we now need to call this script from a batch file. So, this T-SQL script is now perfect for use with the following batch script,

REM Script: ForceUpdate.bat
REM Author: Ian Atkin
REM Version: 1.1 (March 19th 2010)

REM Function:
REM This script executes the T-SQL script Export_Untargeted_Parent_Collections.sql which generates the
REM UpdateAllCollections.exe command-line, redirecting output to Call_UpdateAllCOllections.bat which
REM is subsequently executed

REM Notes:
REM The only user changeable line is the variable SQLSERVER. If you SQL Server is on the same box
REM as Notification Server, this will be the name of your Notification Server. Otherwise, change it
REM to the name of the SQL Server used by NS.
 
set SQLSERVER="NAME_OF_YOUR_SQL_SERVER"
set sqlcmd="C:\Program Files\Microsoft SQL Server\90\Tools\binn\sqlcmd.exe"

%sqlcmd% -S %SQLSERVER% -b -i Export_Untargeted_Parent_Collections.sql | find /i "UpdateAllCollections" > Call_UpdateAllCollections.bat
Call Call_UpdateAllCollections.bat

If you execute this batch file you'll get a command window open which will show UpdateAllCollections.exe being called, echoing each collection name as it's updated. For best effect, run this as a scheduled task with the same frequency of the delta update schedule. I've tested this in environments where there are over 30 collections in the update list, and have seen no problems.

The Automatic_Collection_Updater.zip Download

In today's download you'll find four files,

1. Collections_Not_Recently_Updated.sql
   A simple T-SQL script to help locate collections which are not updating in specific folder hierarchies. This will help tell you if you've got an updating problem which needs solving, or not.

2. View_Untargeted_Parent_Collections.sql
   The first SQL script from this article which locates root collections which are not targeted by the automatic update schedules. Remember to change the ROOTFOLDER guid to the top-level folder of the hierarchy you want to examine. 

3. Export_Untargeted_Parent_Collections.sql
   The second SQL Script which is intended to be called SQLCMD to generate the script which force updates your collections. Remember to change the ROOTFOLDER guid to the top-level folder of the hierarchy you want to examine.

4. Force_Update.bat
   The batch file which should be called by your scheduled task trigger the force update of your collections. Remember the change the SQLSERVER environment variable to your NS SQL Server.

5. UpdateAllCollections.exe
   The Altiris Utility which does the collection updating magic. Made available here for convenience

And with that, I wish you all the best with your collections!

Kind Regards,
Ian./