Video Screencast Help

Updated Version of SQL Trigger to Sync DS Computer Group Structure with Active Directory *(Updated 9-29-2009)*

Created: 19 Aug 2008 • Updated: 29 Sep 2009 | 12 comments
dallasr's picture
+4 4 Votes
Login to vote

This is a modified version of CondorMan's SQL trigger to sync your DS Computer group structure with your AD OU structure. So first off, I just want to give full credit and thanks to CondorMan on the original work that he posted HERE.

I have made modifications to his trigger to handle some issues we were experiencing when we implemented it in our environment and also to add some extra functionality, but the main parts of the logic are all his, so please credit him for it. I thought about just posting this in a comment reply to his article but figured it would've looked pretty ugly and doing it here would be easier to read and give more space to discuss the changes.

Instructions to install trigger:

  1. Open SQL Server Management Studio and connect to the server you want to implement this on.
  2. Click File > Open > File... and select the attached ADSyncTrigger.sql file and click Open.
  3. Click Execute to run the file, which creates the trigger in the eXpress database.
NOTE: This has only been tested with DS 6.9, it may work for other versions but I'm not sure.

So, what's changed?

  1. I added a condition so that the trigger would run if the last_inventory field was updated. The original trigger would run when msnet_dns_domain or msnet_domain_ou fields were updated. I added the condition for the last_inventory field as well so that we could force the trigger to run by running a Get Inventory job on a computer. We then have our inventory run every day on our DS consoles so that the structure will be synched up every day. This helped resolve instances where a computer was manually moved to another group, the ou/domain fields weren't changed so the trigger wouldn't run and these computers would remain stuck in the wrong folder.

    Code:

    IF (UPDATE(msnet_domain_ou) OR UPDATE(msnet_dns_domain) OR UPDATE(last_inventory))
    
    
  2. I added check to see if the computer was both in an Automation session and in the New Computers group, if it is then the trigger will exit and not run. This was needed to resolve an issue we noticed where the trigger was interfering with the Initial Deployment job causing it not to run.

    Code:

    SELECT @CurrEnv = boot_env FROM sessions WHERE computer_id = (SELECT computer_id FROM INSERTED)
    If @CurrGroupID = -7 AND @CurrEnv = 1
    	Return	--Exit trigger
    	
    
  3. I added a check to see if the msnet_dns_domain or msnet_domain_ou values are blank or NULL and if they are the trigger will put the computer in the root of the All Computers group and exit. This was to resolve issues where blank computer group names were being created for computers that were in the computers container in AD or when computers were in a workgroup.

    Code:

    If @GroupName Is Null OR @DomainOU Is Null OR @GroupName = '' OR @DomainOU = ''
    BEGIN
    UPDATE computer SET group_id = NULL WHERE computer_id = (SELECT computer_id FROM INSERTED)
    	Return	--Exit trigger
    END
    
    
  4. I added a condition so that trigger doesn't attempt to delete the New Computers or All Computers groups. The original trigger was causing problems for us when a computer was the last computer in the New Computers group, because the trigger was trying to delete the group as it does for normal computer groups.

    Code:

    IF((SELECT COUNT(1) FROM computer WHERE group_id = @GroupID) < 2 AND @CurrGroupID != -7 AND @CurrGroupID Is Not Null)
    
    
  5. Added various comments and made some formatting changes for easier readability.

Enjoy!

License: AJSL
By clicking the download link below, you agree to the terms and conditions in the Altiris Juice Software License
Support: User-contributed tools on the Juice are not supported by Altiris Technical Support. If you have questions about a tool, please communicate directly with the author by visiting their profile page and clicking the 'contact' tab.

Comments 12 CommentsJump to latest comment

mjphelan's picture

hiyas,

i'n having a minor but irritating ussie with this script.

for the most part it's working fine, but I have 3 or 4 computers accounts that are definately in OU's but keep moving to the root of "All Computers" in DC

is there anything I can do to mitigate this?

all the best

0
Login to vote
AdamCollett's picture

I have implemented the script with the daily "Get Inventory" and our AD structure is being mirrored accross perfectly. Also the slow down and occasional freeze in Altiris has gone with your adjusted version of the trigger.

Absolutely perfect!

0
Login to vote
Tim Maides's picture

Is there any way to create a group and have the computers that are not in AD stay in there.

It seems as soon as I create and add them to a new group the script removes and puts them under all computers.

0
Login to vote
Paul vD's picture

Hi mjphelan, I am having the same issue. I have roughly 3500 PCs and each morning I have about 20 to 30 PCs at the root that was already in the right group in the DS console?  Did you find a solution on your side?

P.S. Thank to both of you CondorMan & dallasr! This is a really nice script!

0
Login to vote
dallasr's picture

Hey everyone, I haven't checked on this post in a long time and never received any notifications that comments were being posted so I apologize for not responding to any of you. There seem to be 2 issues people are experiencing with this version of the trigger:
1) Some events are showing up in the event log saying "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION" and another one with a bunch of SQL in it.
2) Computers going to All Computers when they don't have the Domain or OU populated in its properties in DS (although this was by design for my requirements)

For issue #1 this is because I overlooked a mistake in the trigger. If a computer was the last computer in the group it would try to delete the group and then move the computer instead of moving the computer and then deleting the group.

For issue #2 this was just how I designed it. I wanted to see all of the computers that for one reason or another didn't have the domain or OU populated in it's properties in DS. This will happen when a machine is a member of a workgroup or when the machine is in the Computers container instead of a standard OU. I also noticed this happens with Domain Controllers. So, I just had them default to the root of All Computers, but it sounds like most peole would rather have them dumped into a dedicated group instead.

I updated the trigger attached to this post and fixed both issues. It will now first move a computer when it is the last in a group and then delete the group. And now when it comes across a machine that doesn't have the domain or OU populated instead of putting them in the root of All Computers, it will create a group called Unknown if it doesn't exist already, and then move the computer into that group.

Hopefully this version properly addresses all of your issues. Let me know if there are any other things you'd like to see.

+1
Login to vote
Adam Collett's picture

Thank you so much for taking the time to look into this one - I shall give this a try over the next couple of days and let you know if the event log messages have now gone.

Many many thanks

Adam

0
Login to vote
Adam Collett's picture

I cannot thank you enough for fixing the issues, I can confirm that there are now no errors in the event log and the trigger is working exactly as expected! I hope Symantec take note and build this feature into DS for future versions - it is SO handy to know everything is in synch with AD.

0
Login to vote
Tim Maides's picture

I love the script you put together but I would like to shut if off.

Is there any way I can shut this off and then run it again in a couple weeks or so?

0
Login to vote
dallasr's picture

Just curious, but what is your reasoning for wanting to do this?
That being said, you should be able to disable the trigger by running the following SQL:

USE eXpress
GO
DISABLE TRIGGER ou2group ON computer;
GO

Then to enable the trigger again, you would run the following:

USE eXpress
GO
ENABLE TRIGGER ou2group ON computer;
GO

Unfortunately, I don't have time to really test this out, but that should work fine.

0
Login to vote
Tim Maides's picture

I work at a college and I have many different departments. Some of these departments have labs and other rouge machines that do not connect to the domain. Each area can manage there computers but the can only see what is in there folder.

If I create a folder called Lab in the already athletic folder (which is in sync with ad) and put all the lab machines in the lab folder by the time tomorrow comes around all my machines will be in the unknown folder mingled in with other computers from other departments not connected to the domain.

I wish I could create a folder in DS and put computers in that folder then put a lock on the folder it so the sync will not move the computers out of it when it is run again. I would only want to lock on a folder with computers that don't join the domain.

--

---

0
Login to vote
dbunch's picture

Worked like a charm...

Great Script.

Thanks CondorMan and DallasR

0
Login to vote
Gurugabe's picture

I know this is old, but I hope I can get some HELP. Our Altiris DS 6.9 crashed and upon trying to repair it I could only get it to accept eXpress-ds for the database whereas before we used eXpress and now the script is not working due to the -

What can I do to fix this? I was working great before, the only problem I had with it before is whenever a computers aclient or dagent refreshed itself, the computer would go to the unknown folder until I went to change agent settings - production agent, which would put the computer back into the correct folder.

0
Login to vote