Video Screencast Help

Deployment Server / Active Directory Synchronization

Created: 06 Nov 2007 • Updated: 10 Dec 2007 | 29 comments
Language Translations
CondorMan's picture
+5 5 Votes
Login to vote

Have you set up an Active Directory Organizational Unit structure? Do you want your Deployment Server to automatically match computer groups to that structure? This guide will help you set up Deployment Server Computer Groups to automatically synchronize to your Active Directory Organizational Units.

First, you will need to verify that all the appropriate credentials are found in your Domain Accounts List. Go to Tools > Options > Domain Accounts. Click the Add button and fill out the information of the Domain Account for each of your Domains. This account should have permissions to query Active Directory to determine Organizational Unit membership. It is also the account used for joining computers to the Domain, so it should have permissions to do a Domain Join.

Second, you will need to have either the AClient or DAgent (for Vista) installed on all of your computers. AClient reports Organizational Unit membership to Deployment Server. If you are importing computers from a .csv or .xls file, make sure to include domain and Organizational Unit information in the import file.

Third, run the following SQL Query against your Deployment Server database.

  1. Copy everything between "--------Start Here--------" and "--------End Here--------"
    --------Start Here--------
    IF EXISTS (SELECT * FROM sysobjects WHERE name='ou2group' AND xtype='TR')
    DROP TRIGGER ou2group
    GO
    CREATE TRIGGER ou2group ON computer AFTER UPDATE AS
    IF (UPDATE(msnet_domain_ou) OR UPDATE(msnet_dns_domain))
    BEGIN
    DECLARE @Done bit
    DECLARE @Left int
    DECLARE @Right int
    DECLARE @GroupName varchar(64)
    DECLARE @DomainOrganizational Unit varchar(256)
    DECLARE @ParentID int
    DECLARE @GroupID int
    
    SELECT @Done = 0, @Left = 0, @Right = 0
    SELECT @GroupName = msnet_dns_domain, @DomainOU = msnet_domain_ou FROM INSERTED
    SELECT @ParentID = group_id FROM computer_group WHERE parent_id IS NULL AND name = @GroupName
    
    IF @ParentID IS NULL
    BEGIN
    EXEC ins_group @GroupName, @ParentID
    SELECT @ParentID = group_id FROM computer_group WHERE parent_id IS NULL AND name = @GroupName
    END
    
    WHILE @Done = 0
    BEGIN
    SET @Left = @Right + 1
    SET @Right = CHARINDEX('/', @DomainOU, @Left)
    IF @Right = 0
    BEGIN
    SET @Right = LEN(@DomainOU) + 1
    SET @Done = 1
    END
    SET @GroupName = SUBSTRING(@DomainOU, @Left, @Right - @Left)
    SET @GroupID = NULL
    SELECT @GroupID = group_id FROM computer_group WHERE parent_id = @ParentID AND name = @GroupName
    IF @GroupID IS NULL
    BEGIN
    EXEC ins_group @GroupName, @ParentID
    SELECT @ParentID = group_id FROM computer_group WHERE parent_id = @ParentID AND name = @GroupName
    END
    ELSE
    SET @ParentID = @GroupID
    END
    SELECT @GroupID = group_id FROM INSERTED
    IF((SELECT COUNT(1) FROM computer WHERE group_id = @GroupID) < 2)
    DELETE FROM computer_group WHERE group_id = @GroupID
    UPDATE computer SET group_id = @ParentID WHERE computer_id = (SELECT computer_id FROM INSERTED)
    END
    GO
    --------End Here--------
    
    
  2. Open SQL Query Analyzer
    1. For SQL 2000, open Start > All Programs > Microsoft SQL Server>Query Analyzer
    2. For SQL 2005, open Start > All Programs > Microsoft SQL Server 2005>SQL Server Management Studio and click on the "New Query" button
  3. In the Database Drop-down, select your Deployment Server Database (default is eXpress)
  4. Paste the SQL Query that you copied in step 1 into the Query Window
  5. Press the F5 key to execute the query
  6. Check the status message to verify that the query has executed completely

You have just added a SQL Trigger that will be run every time a computer record is updated. When it is run, it will add the computer to a Computer Group that matches its Active Directory Organizational Unit membership. You might want to force your computers to update so that the membership is updated immediately. To force an update, open the Deployment Server Console and select View > Reset Client Connections. When the computers connect back to Deployment Server, you will see that they are automatically added to a Computer Group structure that matches your Active Directory Organizational Unit.

The SQL Trigger

I will now explain, step-by-step, what the SQL Trigger does.

  1. We declare all of the variables we will use. Here is a brief description of each.

    DECLARE @Done bit
    A true/false value that will be set to 1 (true) when all groups for the computer's Organizational Unit have been created

    DECLARE @Left int
    This is the character position where the current Organizational Unit we are looking at starts

    DECLARE @Right int
    This is the character position where the current Organizational Unit we are looking at ends

    DECLARE @GroupName varchar(64)
    This is the name of the Organizational Unit we are looking at

    DECLARE @DomainOU varchar(256)
    This is the entire Organizational Unit in the format Organizational Unit/Organizational Unit/Organizational Unit/...

    DECLARE @ParentID int
    This is the Computer Group number of the parent group of the computer or the Organizational Unit we are looking at

    DECLARE @GroupID int
    This is the Group number of the Organizational Unit we are looking at

  2. We determine if a Computer Group already exists for the Domain Name. If it does, we remember the group number.
    SELECT @GroupName = msnet_dns_domain, @DomainOU = msnet_domain_ou FROM INSERTED
    SELECT @ParentID = group_id FROM computer_group WHERE parent_id IS NULL AND name = @GroupName
    
    
  3. If the Domain Computer Group does not exist, we create one and remember the group number.
    IF @ParentID IS NULL
    BEGIN
    EXEC ins_group @GroupName, @ParentID
    SELECT @ParentID = group_id FROM computer_group WHERE parent_id IS NULL AND name = @GroupName
    END
    
    
  4. For each Organizational Unit listed in DomainOU, we check to see if a group already exists. If it does, we remember the group number, otherwise we create one and remember the group number.
    WHILE @Done = 0
    BEGIN
    
    
    1. Find the next Organizational Unit Name and remember it as DomainOU
      SET @Left = @Right + 1
      SET @Right = CHARINDEX('/', @DomainOU, @Left)
      IF @Right = 0
      BEGIN
      SET @Right = LEN(@DomainOU) + 1
      SET @Done = 1
      END
      SET @GroupName = SUBSTRING(@DomainOU, @Left, @Right - @Left)
      
      
    2. Determine if the Computer Group already exists.
      SET @GroupID = NULL
      SELECT @GroupID = group_id FROM computer_group WHERE parent_id = @ParentID AND name = @GroupName
      
      
    3. If the group doesn't already exist, create one and remember the group ID.
      IF @GroupID IS NULL
      BEGIN
      EXEC ins_group @GroupName, @ParentID
      SELECT @ParentID = group_id FROM computer_group WHERE parent_id = @ParentID AND name = @GroupName
      END
      
      
    4. If the group does already exist, remember the group ID
      ELSE
      SET @ParentID = @GroupID
      END
      
      
  5. After we have created the entire Organizational Unit structure in Deployment Server, we check to see if the computer was the last one in the group, if it was, remove the group.
    SELECT @GroupID = group_id FROM INSERTED
    IF((SELECT COUNT(1) FROM computer WHERE group_id = @GroupID) < 2)
    DELETE FROM computer_group WHERE group_id = @GroupID
    
    
  6. Last, it adds the computer to the correct Organizational Unit Group in Deployment Server.
    UPDATE computer SET group_id = @ParentID WHERE computer_id = (SELECT computer_id FROM INSERTED)
    
    

Comments 29 CommentsJump to latest comment

Andrew Souter's picture

Doesnt Deployment give you integration with Active Directory without the need for writing any SQL scripts to make it happen?

0
Login to vote
CondorMan's picture

No. The only integration Deployment Server makes with Active Directory is to use Active Directory Groups for console login authentication.

+1
Login to vote
frendon's picture

It seems to be working for me but not all computers have been reassigned to their correct OUs yet, Also is there a way to change it so the groups don't show all in lowercase??

0
Login to vote
vpalisoc's picture

does this work with DS 6.1? What I'm trying to do is to deploy a RIP to a certain AD Group. Am I headed in the right direction with this?

Virgil C. Palisoc
Information Systems Analyst II
Data Systems Unit
San Diego Police Department
vpalisoc@pd.sandiego.gov

Virgil C. Palisoc City of San Diego | San Diego Police Department | Data Systems Unit Information Systems Analyst III | Altiris Administrator Ph: 619-531-2229 Fax: 619-531-2101 email: vpalisoc@pd.sandiego.gov

0
Login to vote
i2professional@yahoo.com's picture

it will be appreciable if you could provide snapshots for this

+2
Login to vote
frendon's picture

This trigger worked fine initially, it replicated most of our AD structure and moved some computers accordingly, of course every OU showed up in lowercase which is a little annoying but not a big issue.

However after a while I had to disable the trigger because each 15mins the deployment console would stop responding, the only way to bring it back up would be restarting the "SQL Server" Service and disabling the trigger. Has anyone else encountered this issue? Do you have any recommendations on how often the client connections should be updated or anything like that? Thanks again for your help. This would really help us if we can get it to work properly.

0
Login to vote
DSnelleman's picture

This is exactly what I was looking for.
There is just a small detail.
Variables can not use spaces at least in SQL 2000.
So

@DomainOrganizational Unit

should be something like

@DomainOrganizational_Unit

And there is a typo in the following line

IF((SELECT COrganizational UnitNT(1) FROM computer WHERE group_id = @GroupID) < 2)

Should be

IF((SELECT COUNT(*) FROM computer WHERE group_id = @GroupID) < 2)

Regards,
Dennis Snelleman

0
Login to vote
CondorMan's picture

Oops! Thanks for catching that.

I did a search-and-replace just before submitting it to replace OU with the actual text "Organizational Unit". I will fix the typos and re-submit the article.

+1
Login to vote
mervync's picture

I think that this trigger might break the ability to add computers to the 'New Computer' group, for Initial Deployment or other uses. It gives an error stating that

"Computer.group" does not exist. Cannot modify child in "computer".

Have any of you had this error appear? This is the only change we've made to the DS implementation, and we've since started seeing this error.

Thanks in advance.

0
Login to vote
CondorMan's picture

I will look into that scenario. Thank you for the feedback.

+1
Login to vote
smithld's picture

Great tip first up, it's just the starting block I was after to get going with a more complex requirement for keeping AD structure and DS structure in sync :)

Did you ever find out anything about this error? I've recently re-installed a fresh DS6.9 server (with pre-existing computers with aclient installed that I picked up when they checked back in) and this trigger runs for a few updates but then I get errors in the event log relating to the update query and no more database updates happen.

When I manually run the query on the database, I get the above error. Note that this was with an existing computer in the database, not a new one that I created.

The full error when I run this manually is:

'Server: Msg 30003, Level 16, State 1, Procedure tu_computer, Line 197

"computer_group" does not exist. cannot modify child in "computer".'

I can't find a SP by that name, but it must exist somewhere to get to Line 197 I'd think.

There is also a following error in Event Viewer saying 'The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.'

Anyone else got any ideas?

0
Login to vote
michael-green's picture

We had implemented this for our 1300 computer DS SQL server to have this trigger runaway thus creating a 332GB eXpress transaction log file. Has anyone else noticed this? We would really like to use this for our environment; however, this ultimately brought our DS to its knees. Any other suggestions within this trigger that might eliminate this issue?

0
Login to vote
songs's picture

I assume this occurred during the initial update for all 1300 devices?

Although not recommended in triggers you could wrap the delete and update statements with their own begin and commit transaction statements (note that the stored procedure ins_group is performing begin/commits/rollbacks too). This would allow you to perform transaction log truncations, or backups in parallel to maintain space in that the completed transactions would be removed from the logs. The other option is to not allow your transaction log to dynamically increase; rather set the initial size large, and perform backups or truncations of the transaction log at a more frequent interval.

Updated portion of trigger example:

...
BEGIN
	begin transaction
	DELETE FROM computer_group WHERE group_id = @GroupID
	if (@@error != 0)
      rollback transaction
	else
      commit transaction
END
begin transaction
UPDATE computer SET group_id = @ParentID WHERE computer_id = (SELECT computer_id FROM INSERTED)
if (@@error != 0)
    rollback transaction
else
   commit transaction
...

You might review http://support.microsoft.com/kb/873235 for more info on transaction logs.

HTH

0
Login to vote
marcos's picture

Hi!

This big transaction log was created, I guess, with MS SQL set in full mode. Can you confirm this?

If I don't enable the transactions on the MS SQL server this wouldn't happen. Is that correct?

Thanks!

0
Login to vote
lobster's picture

I ran the query and received the following messages:

Server: Msg 137, Level 15, State 1, Procedure ou2group, Line 13
Must declare the variable '@DomainOU'.
Server: Msg 137, Level 15, State 1, Procedure ou2group, Line 25
Must declare the variable '@DomainOU'.
Server: Msg 137, Level 15, State 1, Procedure ou2group, Line 28
Must declare the variable '@DomainOU'.
Server: Msg 137, Level 15, State 1, Procedure ou2group, Line 31
Must declare the variable '@DomainOU'.

I'm a newbie to SQL...

Thanks.

0
Login to vote
lobster's picture

Got it. Just changed the domain to our domain name.

0
Login to vote
lobster's picture

Nope, don't have it. After reading more carefully it should stay DomainOU in order to get the proper OU from the Domain.
I changed it back and now I get the following message:

Server: Msg 208, Level 16, State 4, Procedure ou2group, Line 1
Invalid object name 'computer'.

Any ideas?

Thanks.

0
Login to vote
CondorMan's picture

The most likely reason you are gettin that error is because the query is being run against the wrong database. In query analyzer, you will see a drop-down-list of databases and Master will be selected by default. You can either switch it from Master (or whatever database is selected) to eXpress (or whatever your Deployment Server database is named), or you can place the following lines before anything else in your query:

use eXpress
go

+1
Login to vote
marcos's picture

Hi there!

TIME: We currently have a fleet of 4500 computers. Are you able to tell me how much time it may take for the Trigger to complete the initial task of putting all those computers in the correct structure?

CURRENT DS STRUCTURE: What happens to the structure of the computers currently set in DS? does it get deleted? Does it remain there without computers in it?

LIMITING THE SCOPE: I don't know much of SQL, but can you limit the syncronization to a defined OU in AD (so it doesn't syncronize the structure of computers outside that OU?)

Thanks so much!
Marcos Carot Collins
Murdoch University
www.murdoch.edu.au

0
Login to vote
marcos's picture

Hi again!

I noticed in our testing environment that OUs that have names with a combination of cases (ex: DoAffAc) which is done for readability, doesn't replicate in DS: you get doaffac, always in lower case.

Is there a way to preserve the case?

Thank you!

0
Login to vote
smithld's picture

I think that's how the AClient reports it back to DS. I'm not sure if there's a way to change the way AClient sends OU information.

0
Login to vote
backmsco's picture

I have run the AD sync script, and it worked wonderfully initially. Now however it seems that altiris is taking one of our OU containers and all of its sub containers and reordering them in a fashion that no longer resembles AD. It only seems to affect this one OU which has me confused, any ideas?

0
Login to vote
gewu's picture

Guys,

I'm a complete noob on SQL and I'm getting these errors running the script against the correct database

Msg 102, Level 15, State 1, Procedure ou2group, Line 8
Incorrect syntax near 'varchar'.
Msg 137, Level 15, State 1, Procedure ou2group, Line 13
Must declare the scalar variable "@DomainOU".
Msg 137, Level 15, State 2, Procedure ou2group, Line 25
Must declare the scalar variable "@DomainOU".
Msg 137, Level 15, State 2, Procedure ou2group, Line 28
Must declare the scalar variable "@DomainOU".
Msg 137, Level 15, State 2, Procedure ou2group, Line 31
Must declare the scalar variable "@DomainOU".

What am I doing wrong ?

Regards,

Gert

0
Login to vote
frendon's picture

I am getting the same errors as you gewu.

0
Login to vote
frendon's picture

I found the problem gewu,

On line 11

change
"DECLARE @DomainOrganizational Unit"
to
"DECLARE @DomainOU"

After this it will run fine.

0
Login to vote
dallasr's picture

FYI to all...I posted a modified version of this trigger here: https://www-secure.symantec.com/connect/downloads/... It should help with some of the issues people may have been experiencing. Thanks to CondorMan for a great contribution and getting the ball rolling with this!

0
Login to vote
Marcosrcc's picture

Hi!

I am having the same database errors in the even viewer. Were you able to fix it?

I am actually using the "improved trigger" that used to be in the article linked in the last comment of this one (but seems to be inaccessible now).

Thanks a lot!

Marcos R Carot Collins
Murdoch University

0
Login to vote
Marcosrcc's picture

Hi!

Looks like in the migration from Juice, your article was lost. May you re-post the modified version?

Thanks!

Marcos R Carot Collins
Murdoch University

0
Login to vote
ohzone - CherylPeterson's picture

https://www-secure.symantec.com/connect/downloads/...

I've also updated it above

Cheryl

Endpoint Management,
Endpoint Virtualization
Managing Mobility
Community Manager
www.twitter.com/EMnV_symc
Need Altiris help? IRC chat #Altiris

0
Login to vote