Summary
Reconciles "target" AD Groups, DB Groups, or Text Files from "source" AD groups, DB Groups, or SQL Queries. Does not create AD or DB users or modify properties of existing AD or DB users. Target AD or DB groups are not created by this script.
The DBADReconcile.vbs Vbscript is run every hour as a scheduled task on the windows server. It does the following:
Queries the ED.Group_Reconciliations and ED.Group_Reconcile_Exceptions tables to get all reconciliations, sorted by run order
Loop through each reconciliation and see it it is time to execute it. Some reconciliations are scheduled hourly, others weekly or monthly.
If reconcile is executed:
Determine source group members
Determine reconcile exceptions: members that should never be added to target group and members that should never be removed from target group
Get target group members (except in the case of a text file target)
Add or remove members to target AD or DB group based on rules of reconcile and existing exceptions. In the case of text files, generate xml representation of source group and apply xslt to produce text file.
Send email notification
Log reconciliation results
This application is only used by the IT Group of CIO.
Reconciliations are edited via supr G Microsoft Access Interface. They may be edited directly in the SQL server tables, but this is not recommended because supr G offers additional data associated with DB and AD groups.
Reconciliation id
Reconciliation name
Reconciliation notes
Disabled = Y | N
Owners: Comma separated list of UIUC netids that are responsible for this script.
Notifications: Comma separated list of email addresses that recieve script notifications when actions or errors occur.
Logging: Comma separated list of email addresses that recieve results each time script is run (even if no actions or errors occur).
Frequncy: always,hourly,daily,weekly,monthly
Run_Time: time of day if daily, weekly, or monthly on 24 hour clock.
Day: day of week if weekly (monday,tuesday,...) day of week if monthly (1,2,3,...)
Order. If two scripts are scheduled to run at the same time (have the same Periodicity, Time, and Day configuration), this integer will determine which is run first. 1, 2, .... from lowest to highest this is optional.
SourceType: AD, DBSQL, or DBCOE
SourceLogic: AD group name for AD SourceType, SQL statement that returns column of unique netids for DBSQL SourceType, COE DB group Name for DBCOE SourceType
TargetType: AD, DBSQL, FILE, or DBCOE
TargerGroupName: AD group name for AD targets and COE DB Group Name for COEDB tagets.
TargetDBSelectSQL rquired for target groups of type DBSQL: SQL statement that returns column of unique netids
TargetDBInsertSQL (required for target groups of type DBSQL if TargerInsertUserNames isn't set to "I".An SQL insert statement that will insert one user into into a group defined by the targetdbselectsql statement. Must have a [username] substitution tag which will be replaced with the username being added.
TargetDBdeleteSQL (required for target groups of type DBSQL if TargerDeleteUserNames isn't set to "I"): An SQL insert statement that will delete one user into into a group defined by the targetdbselectsql statement. Must have a [username] substitution tag which will be replaced with the username being deleted.
TargetDeleteUserNames: A,AE,ME,I. How should usernames found in the target, but not found in the Source be dealt with?
AE - Automatically Delete with email notification to script admins
ME - Don't delete, but send email notification to script admins first time doesn't reconcile
I - Ignore this type of discrepency.
TargetInsertUserNames: A,AE,ME,I. How should usernames found in source, but not in target be dealt with?
AE - Automatically Insert with email notification to script admins
ME - Don't Insert, but send email notification to script admins first time doesn't reconcile
I - Ignore this type of discrepency.
Target File. Where target file should be written.
Target XSLT. Location of transform to be applied to group data to create file.
Username
Exception_type
Allow. Allow user to remain in a group, even if source logic doesn't put them in the group.
Deny. Don't allow user in the group, even if source logic does put them in the group.
Exception_expiration. When does this exception expire.
DBADReconcile.vbs VBScript file
ED.Group_Reconciliations configuration for each group reconcile.
ED.Group_Reconcile_Exceptions membership exceptions to target group
ED.Group_Reconcile_Transactions log of executed group reconciles
Xslt files are used when target is a text file. These are generally kept in same folder as vbscript file. Their location for a given reconcile is specified in the ED.Group_Reconciliations table.
Must be scheduled as task on a server.