By David Wiseman (Administrator)Created 06 Oct 2012
My Rating:
Vote
Rating:
(1 votes)
Views:8253
Downloads:201
Source:

Add Users to Groups from CSV

Language:  VBScript

Compatibility

Windows XP Yes Windows 2003 Yes
Windows 2000 Yes Windows NT Unknown
Vista Yes Windows 2008 Yes
Description

Script to update group membership from a CSV file.


Notes
This script can be used to add users to groups from a CSV file.  The CSV file should called "groupmemberupdate.csv" and located in the root of your C:\ drive, but this can be changed by editing the "Setup" section of the script.  The CSV file should look similar to the following:
 
UserName,Group
UserA,Group1
UserA,Group2
UserA,Group3
UserB,Group1
UserB,Group4
 
Based on the CSV file above, the script will add UserA to groups 1..3 and UserB to groups 1 & 4.  The script only adds users to groups - users are not removed from any groups that they are already a member of.  
 
Code

Line Numbers: On  Off      Plain Text
Option Explicit
' Created By David Wiseman
' http://www.wisesoft.co.uk
' *************************************************
' * Description
' *************************************************
' Script to update group membership based on a CSV file
' *************************************************
' * Instructions
' *************************************************
' Requires a CSV file with the headers "UserName" and "Group"
' Edit the variables in the "Setup" section as required.
' Run this script from a command prompt in cscript mode.
' e.g. cscript groupmemberupdate.vbs
' You can also choose to output the results to a text file:
' cscript groupmemberupdate.vbs >> results.txt
' *************************************************
' *************************************************
' * Constants / Decleration
' *************************************************
CONST adOpenStatic = 3
CONST adLockOptimistic = 3
CONST adCmdText = &H0001
Const ADS_NAME_INITTYPE_DOMAIN = 1
Const ADS_NAME_TYPE_NT4 = 3
Const ADS_NAME_TYPE_1779 = 1
Dim strFolder,strCSVFile, strCSVFolder, strDomain
Dim strUserDN, strGroupDN, strUser,strGroup, strPreviousUser,strPreviousGroup
Dim cn,rs, objSystemInfo, objGroup
' *************************************************
' * Setup
' *************************************************
' Folder where CSV file is located 
strCSVFolder = "C:\"
' Name of the CSV File
strCSVFile = "groupmemberupdate.csv"
' *************************************************
' * End Setup
' *************************************************
' Get domain name
SET objSystemInfo = CREATEOBJECT("ADSystemInfo") 
strDomain = objSystemInfo.DomainShortName
' Setup ADO Connection to CSV file
SET cn = CREATEOBJECT("ADODB.Connection")
SET rs = CREATEOBJECT("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strCSVFolder & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited"""

rs.Open "SELECT * FROM [" & strCSVFile & "]", _
          cn, adOpenStatic, adLockOptimistic, adCmdText
             
' Read CSV File
DO Until rs.EOF
	' Read UserName/GroupName columns from CSV file
	strUser= rs("UserName")
	strGroup= rs("Group")
	' Translate username to distinguished name format. e.g. CN=david.wiseman,OU=MyUsers,DC=wisesoft,DC=co,DC=uk
	If strUser <> strPreviousUser Then ' Don't bother with name translate if user is same as previous row
		strUserDN =  GetDN(strUser,strDomain)
	End If
	' Translate group to distinguished name format. e.g. CN=IT Operations,OU=MyGroups,DC=wisesoft,DC=co,DC=uk
	If strGroup <> strPreviousGroup Then ' Don't bother with name translate if group is same as previous row
		strGroupDN = GetDN(strGroup,strDomain)
	End If
	' Bind to group object
	Set objGroup = GetObject("LDAP://" & strGroupDN)
	On Error Resume Next ' Ignore errors
	' Add user as member of group
	objGroup.Add "LDAP://" & strUserDN
	' Check if an error occurred (e.g. user is already a member of the group)
	select Case Err.Number
	Case -2147019886 
		WScript.Echo "'" & strUser & "' is already a member of '" & strGroup & "'"
	Case 0
		WScript.Echo "Added '" & strUser & "' to group '" & strGroup & "'"
	Case Else
		WScript.Echo "Error adding user '" & strUser & " to group '" & strGroup & "': " & Err.Number & " " & Err.Description
	End Select 
	Err.Clear
	On Error GoTo 0 ' Turn off resume next error handling
	' Store name of group/user we've just processed
	strPreviousUser = strUser
	strPreviousGroup = strGroup
	' Move to next row in CSV
	rs.MoveNext
Loop
' Get distinguished name from NT name. e.g. David.Wiseman >>> CN=david.wiseman,OU=MyUsers,DC=wisesoft,DC=co,DC=uk
' See http://www.rlmueller.net/NameTranslateFAQ.htm for more info on NameTranslate
FUNCTION GetDN(ByVal strName,ByVal strDomain)
	Dim objTrans, strDN
	SET objTrans = CREATEOBJECT("NameTranslate")
	objTrans.Init ADS_NAME_INITTYPE_DOMAIN, strDomain
	objTrans.SET ADS_NAME_TYPE_NT4, strDomain & "\" & strName 
	strDN = objTrans.GET(ADS_NAME_TYPE_1779) 
	GetDN = strDN
END Function

 


Got a useful script? Click here to upload!


 

  Post Comment
Order By:  
User Comments
      
Be the first to post a comment!