--=====================================================================================
-- SQL Source File -- Created with SAPIEN Technologies PrimalScript 2007
--
-- NAME: ou2group.sql
--
-- ORIGINAL AUTHOR: CondorMan
-- ORIGINAL INFO:
--
http://juice.altiris.com/article/2835/deployment-server-active-directory-synchronization
--
-- MODIFIED BY: Ryan Dallas
-- DATE : 8/18/2008
--
-- DESCRIPTION: Creates trigger on update for the computers group in the
-- Altiris eXpress database. Trigger keeps DS Computer Group
-- structure in sync with AD OU hierarchy.
--=====================================================================================
--Make sure we are using the eXpress database
USE eXpress
--If trigger currently exists delete it from database
IF EXISTS (SELECT * FROM sysobjects WHERE name='ou2group' AND xtype='TR')
DROP TRIGGER ou2group
GO
--Create trigger to happen after updates to computers table
CREATE TRIGGER ou2group ON computer AFTER UPDATE AS
--Only run if msnet_domain_ou, msnet_dns_domain, or last_inventory fields are updated
IF (UPDATE(msnet_domain_ou) OR UPDATE(msnet_dns_domain) OR UPDATE(last_inventory))
BEGIN
DECLARE @Done bit
DECLARE @Left int
DECLARE @Right int
DECLARE @GroupName varchar(64)
DECLARE @DomainOU varchar(256)
DECLARE @ParentID int
DECLARE @GroupID int
DECLARE @CurrGroupID int
DECLARE @CurrEnv int
SELECT @Done = 0, @Left = 0, @Right = 0
SELECT @GroupName = msnet_dns_domain, @DomainOU = msnet_domain_ou, @CurrGroupID = group_id FROM INSERTED
SELECT @CurrEnv = boot_env FROM sessions WHERE computer_id = (SELECT computer_id FROM INSERTED)
--Check to make sure this computer is both NOT in the New Computers group and NOT in an Automation session
--This is our means of checking for machine that should run the Initial Deployment job.
If @CurrGroupID = -7 AND @CurrEnv = 1
Return --Exit trigger
--If msnet_dns_domain or msnet_domain_ou are empty or NULL, then move computer to root of All Computers
--by setting group_id for the computer to NULL. This prevents blank group names from being generated
--for computers that are in the Computers container in AD or are joined to a workgroup
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
--Get group_id of computer group in the root of All Computers that matches the msnet_dns_domain
--of the computer and store it in @ParentID
SELECT @ParentID = group_id FROM computer_group WHERE parent_id IS NULL AND name = @GroupName
--If the group does not already exist, then create it and then store the group_id in @ParentID
IF @ParentID IS NULL
BEGIN
--use Altiris built-in Stored Procedure, ins_group, to create group
EXEC ins_group @GroupName, @ParentID
--Get group_id of computer group in root of All Computers that matches msnet_dns_domain
SELECT @ParentID = group_id FROM computer_group WHERE parent_id IS NULL AND name = @GroupName
END
--Retrieve OU's from msnet_domain_ou value and create proper group hierarchy if it doesn't exist already
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
--Get group_id of computer group that matches OU we are currently working with
SELECT @GroupID = group_id FROM computer_group WHERE parent_id = @ParentID AND name = @GroupName
--If group does not exist, then create it
IF @GroupID IS NULL
BEGIN
--use Altiris built-in Stored Procedure, ins_group, to create group
EXEC ins_group @GroupName, @ParentID
--Get group_id of computer group that matches OU we are currently working with
SELECT @ParentID = group_id FROM computer_group WHERE parent_id = @ParentID AND name = @GroupName
END
ELSE
SET @ParentID = @GroupID --If group does exist then set @ParentID to the group_id of the group
END
--Set @GroupID to the current group_id of the computer
SELECT @GroupID = group_id FROM INSERTED
--Delete group computer is currently in, if it is the last computer in that group since it will be empty
--after the computer is moved to it's new group. But first make sure it is NOT in either the New Computers
--or the All Computers group since we don't want the trigger to attempt deleting those groups.
IF((SELECT COUNT(1) FROM computer WHERE group_id = @GroupID) < 2 AND @CurrGroupID != -7 AND @CurrGroupID Is Not Null)
DELETE FROM computer_group WHERE group_id = @GroupID
--Move computer to new group
UPDATE computer SET group_id = @ParentID WHERE computer_id = (SELECT computer_id FROM INSERTED)
END
GO