Using Active Directory Group Membership for Software Delivery

Active Directory and Altiris Notification Server seem to have a love hate relationship. The Active Directory connector loves to import user names and computer names, but it hates to do anything related to group membership. A common request has been the ability to incorporate user group information from Active Directory into collections in order to allow user based software delivery. In other words, install Quickbooks to all of the users who are in the Accounting OU. This tip steps through how to accomplish this.

The first step is to create a connection between the Microsoft SQL Server hosting the Altiris databases and the Active Directory. This is accomplished through a SQL concept known as "Linked Servers." There is a bit of black magic to making linked servers work and a full technical drill down into how they work is beyond the scope of this article. Suffice it to say running the following query in SQL Management Studio should achieve the desired result:

EXEC sp_addlinkedserver @Server = 'ADSI', @srvproduct='Active Directory Service Interfaces', @provider = 'ADSDSOObject', @datasrc='servername.domainname.com'
GO

In case it is not obvious, the only thing that needs to be customized in the SQL above is to substitute servername.domainname.com with the correct domain information.

Once a linked server is created, SQL now has the ability to run queries against the linked server. In this case, those queries will be LDAP queries to pull out the user and group membership information from Active Directory and link that back to the information Altiris knows about primary owner. Below is the SQL statement necessary, followed by an explanation of what this is doing:

SELECT c.[name] as [Computer Name],[vResource].[Guid] as [GUID]
FROM [vResource]
JOIN [Inv_AeX_AC_Primary_User] ON [vResource].[Guid] =[Inv_AeX_AC_Primary_User].[_ResourceGuid] JOIN VComputer C ON vresource.GUID = c.guid
JOIN  
(SELECT sAMAccountName as 'User' FROM OPENQUERY( ADSI, 'SELECT sAMAccountName   
	FROM ''LDAP://CORP''  
	WHERE memberOf= ''CN=%OU%,OU=Groups,DC=DOMAIN,DC=NAME,DC=com''')) ad  
        ON ad.[User] = [Inv_AeX_AC_Primary_User].[User]   
        AND [Inv_AeX_AC_Primary_User].[Month] = DATENAME(m, CURRENT_TIMESTAMP)   
	AND [Inv_AeX_AC_Primary_User].[Domain] = 'DOMAIN'   
WHERE [vResource].[IsManaged] = '1'   
AND  
(  
      [vResource].[ResourceTypeGuid] = '493435F7-3B17-4C4C-B07F-C23E7AB7781F' /* Computer */ OR   
      [vResource].[ResourceTypeGuid] = '2C3CB3BB-FEE9-48DF-804F-90856198B600' /* VirtualMachine */  
)

The SQL above is being used as a report in Notification Server with a Global parameter called "OU."What is happening in the SQL statement is that an LDAP query is being passed to Active Directory. The LDAP query is going to pull out the account name (sAMAccountName) for every user that is a member of the group that is specified in the %OU% parameter. Remember that %OU% in Notification Server represents a Global Report Parameter. Using the SQL above, a new report is created in Notification Server. This report contains a Global Parameter named "OU" that is configured as seen in the screenshot below:

Click to view.

The user is prompted to enter the name of the group they are looking for each time the report is run and Notification Server substitutes the typed in name for the name of the OU.

Now that the LDAP query results are returned, the usernames are compared against the Primary user information collected from Altiris Inventory Solution. Each month primary user is generated and the previous months are archived so the query sets the current month to be used for the comparison. It is also important to make sure the primary user matches the domain that was specified in the LDAP query and so this is hardest in final line of the SQL before the last where clause.

After the comparisons have been made and filtered based on the primary user, the results are further refined by the final where clause. IsManaged = 1 means that the computers in question have the Altiris Agent installed, and the two ResourceTypeGuids specified are the GUIDs to ensure that only Computers or Virtual Computers are returned in the results.

The result is a list of the computer names and their GUIDs that are associated to the users in the AD Group that was specified.

Using the {SQL} button in the report toolbar, a dynamic collection can now be created and used as the target for a software delivery task.