Two parts - VBScript on server to generate a CSV file and copy to ZCS server. Perl script on ZCS to transform into zmprov commands

Const ADS_SCOPE_SUBTREE = 2
Const ForReading = 1, ForWriting = 2, ForAppending = 8
 
 
'standard stuff, create objects etc
set net = wscript.createobject("wscript.network")
set fso = wscript.createobject("scripting.filesystemobject")
Set shell = wscript.createobject("wscript.shell")
 
 
'AD objects for locations
Set adcnx = createobject("ADODB.Connection")
Set adcmd = createobject("ADODB.Command")
 
 
' Init AD stuff for locations
adcnx.Provider = "ADsDSOObject"
adcnx.Open "Active Directory Provider"
adcmd.ActiveConnection = adcnx
 
 
' File to write to
strCSVFile = shell.CurrentDirectory & "\users.csv"
 
 
' Query AD string - get users
adcmd.CommandText = _
		"SELECT name, description, canonicalName, displayName, givenName, sn, sAMAccountName, userAccountControl, mail, distinguishedName, memberOf "_
		& "FROM 'LDAP://OU=BBS,OU=Establishments,DC=BBARRINGTON,DC=internal' " _
    & "WHERE objectClass='user' AND objectClass<>'computer'" _
		& "ORDER BY Name"
 
' Properties for AD command
adcmd.Properties("Page Size") = 9999
adcmd.Properties("Timeout") = 10
adcmd.Properties("Searchscope") = ADS_SCOPE_SUBTREE
adcmd.Properties("Cache Results") = False
 
 
' Open file to write to
set fUsers = fso.OpenTextFile(strCSVFile, ForWriting, True, TristateFalse )
 
 
' Create recordset to iterate through locations
Set rstUsers = adcmd.Execute
rstUsers.MoveFirst
i=0
 
Do Until rstUsers.EOF
 
	' Get attributes
	strName						= lcase(rstUsers.Fields("Name").Value)
	arrDesc						= rstUsers.Fields("description").Value
	strDisplay				= rstUsers.Fields("displayName").Value
	strFirst					= rstUsers.Fields("givenName").Value
	strLast						= rstUsers.Fields("sn").Value
	strSAMAcctName		= rstUsers.Fields("sAMAccountName").Value
	strUserAcctCtl		= rstUsers.Fields("userAccountControl").Value
	strEmail					= rstUsers.Fields("mail").Value
	strDN							= rstUsers.Fields("distinguishedName").Value
	arrMemberOf				= rstUsers.Fields("memberOf").Value
 
	'msgbox strname, vbinformation, struseracctctl 
	' Find out if user is disabled or not	
	Select Case strUserAcctCtl
		Case "544"
			strStatus = "active"
		Case "546"
			strStatus = "locked"
	End Select
 
	' Get description
	For Each strValue In arrDesc
		strDesc = trim(strValue)
	Next
 
	' See which COS we need to apply
	strCOS = "null"
	Select Case strDesc
		Case "Admin User", _
		"Deputy Head Teacher", _
		"Head Teacher", _
		"Non-Teaching Staff User", _
		"Student Teacher", _
		"Support Staff User", _
		"Support Staff Users", _
		"System Administrator User", _
		"Teaching Staff User"
			strCOS = "bbstaff"
		Case "Student User"
			strCOS = "bbstudent"
	End Select
 
	' Now the distribution lists we belong to (big'un!)
	strLists = vbNullString
	' Check user is active
	If strStatus = "active" AND strCos = "bbstaff" Then
		' Get all groups they are a member of
		For Each strGroupDN in arrMemberOf
			strList = ""
			' Get just the first part of the distinguished name of the group
			arrGroupName = split(strGroupDN, ",")
			strGroupName = arrGroupName(0)
			strGroupName = Replace(strGroupName, "CN=", "")
 
			Select Case strGroupName
				Case "BBS Non-Teaching Staff", "BBS Admin Users", "Domain Admins"
					strList = "supportstaff"	
				Case "BBS Teaching Staff"
					strList = "teachers"
				Case "BBS Art Teachers"
					strList = "art"
				Case "BBS English Teachers"
					strList = "english"
				Case "BBS History Teachers"
					strList = "history"
				Case "BBS ICT Teachers"
					strList = "ict"
				Case "BBS Maths Teachers"
					strList = "maths"
				Case "BBS MFL Teachers"
					strList = "mfl"
				Case "BBS Music Teachers"
					strList = "music"
				Case "BBS PE Teachers"		', "CN=BBS Physical Education Teachers"
					strList = "pe"
				Case "BBS RE Teachers"		', "CN=BBS Religious Education Teachers"
					strList = "re"
				Case "BBS Science Teachers"
					strList = "science"
				Case "BBS Teaching Staff"
					strList = "teachers"
				Case "BBS Technology Teachers"
					strList = "technology"
			End Select
 
			if(len(strList)>0) Then strLists = strLists & strList & ":"
			'msgbox strgroupdn & vbcrlf & vbcrlf & strLists, vbinformation, strname
		Next
		strLists = strLists & "staff"
		'msgbox strLists, vbinformation, strname
	End If
 
	' Now check user's "mail" property is set. If not, set it.
	' If we have a COS and No email specified
	If (IsNull(strEmail)) AND (strCos <> "null") Then
		' Set email!
		strEmail = strName & "@bishopbarrington.net"
		set objUser = GetObject("LDAP://" & strDN)
		objUser.Put "mail", strEmail
		objUser.SetInfo
		set objUser = Nothing
	End If
 
	' Define string
	' CSV Fields: username,displayname,firstname,lastname,cosname,status
	strUser = strName & ","	& strDisplay & "," & strFirst & "," & strLast & "," & strCOS & "," & strLists & "," & strStatus
 
	' Write to log file
	if(strCos <> "null") Then fUsers.WriteLine strUser
 
	' Clear variables
	strName = vbNullString
	strDescription = vbNullString
	strDisplay = vbNullString
	strSAMAcctName = vbNullString
	strUserAcctCtl = vbNullString
	strUser = vbNullString
 
	' Carry on
	rstUsers.MoveNext
	i=i+1
 
Loop
 
 
'msgbox "done"
 
' Copy file to zimbra server using PSCP (PuTTY SCP)
strCmd = "cmd /c " & shell.CurrentDirectory & "\pscp.exe -batch -pw PASS " & strCSVFile & " USER@mail.bbs.uhp.me.uk:/tmp/adusers.csv"
shell.run strCmd, 0

/usr/local/sbin/zmadimport.pl

#!/usr/bin/perl -w
 
#use strict;
use Fcntl;
 
 
# Find out how long the script takes to run
system("echo start > /tmp/zmad-start");
 
 
# Variables
my $zimbra_home = "/opt/zimbra";
my $filein = "/tmp/adusers.csv";
my $fileout = "/tmp/adusers.zmp";
my $accts = "/tmp/accts";
my $domain = "bbs.uhp.me.uk";
my $md5file = "/tmp/adusers.csv.last.md5";
 
 
# Check MD5
my $md5new = `md5sum $filein`;
my $md5last = `cat $md5file`;
 
#print "New CSV: $md5new";
#print "Old CSV: $md5last";
#print "\n";
 
if($md5new eq $md5last){ print "File not changed.\n"; exit(0); }
 
 
# Get the COS IDs from zimbra
#
my %cosids = (
	"bbstaff" => getCosId("bbstaff"),
	"bbstudent" => getCosId("bbstudent"),
);
 
 
 
 
# FUNCTION getCosId
# gets the cos id by running zmprov
#
sub getCosId {
	my $cosname = $_[0];
	my $cosid = `zmprov gc $cosname |grep zimbraId:`;
	$cosid =~ s/zimbraId:\s*|\s*$//g;
	return $cosid;
}
 
 
 
 
#####
# MAIN STUFF HERE
 
 
# Get all current accounts
#system("zmprov gaa > $accts");
$accts = `zmprov gaa`;
 
 
# Initialise files
open(CSV, $filein);
open(ZMP, ">$fileout");
my $csv = <CSV>;
 
 
# Parse CSV file
while($csv) {
	my ($username,$displayname,$firstname,$lastname,$cosname,$lists,$status) = split(/\,/, $csv);
 
	#$lists =~ s/\n//;
	my @dists = split(':',$lists);
 
	#print(" - $username\n");
	#foreach $dist(@dists){
		#print "  - $dist";
	#}
 
    $displayname =~ s/\s+/ /;
	$displayname =~ s/\./ /;
	my $email = "$username\@$domain";
	my $cosid = $cosids{$cosname};
 
 
	# Check account exists
	my $exists = $accts;
	if($exists =~ m/$username\@$domain/){
		#print "  - EXISTS!";
		$acctexists = 1;
	} else {
		$acctexists = 0;
	}
 
	print "\n";
 
	# Write to ZMPROV file!
	if($acctexists == 0){
		# New accounts only
		printf ZMP qq{ca $email ''\n};
		printf ZMP qq{ma $email zimbraCOSid "$cosid"\n};
		printf ZMP qq{ma $email cn "$username"\n};
	}
 
	# Update existing accounts!
	# - Name
	if($firstname ne ""){ printf ZMP qq{ma $email givenName "$firstname"\n}; }
	if($lastname ne ""){ printf ZMP qq{ma $email sn "$lastname"\n}; }
	if($displayname ne ""){ printf ZMP qq{ma $email displayName "$displayname"\n}; }
	# - Description field
	my $desc = "";
	if($cosname eq "bbstaff"){ $desc = "Staff Account"; }
	if($cosname eq "bbstudent"){ $desc = "Student Account"; }
	printf ZMP qq{ma $email description "$desc"\n};
	# - Account status
	printf ZMP qq{ma $email zimbraAccountStatus $status\n};
 
 
	# Distribution lists	
	if(scalar(@dists)>1){
		foreach $list(@dists){
			printf ZMP qq{adlm $list\@$domain $email\n};
		}
	}
 
 
	# - END
	printf ZMP qq{\n};
 
	# next line
	$csv = <CSV>;
 
}
 
 
# Close text files
close(CSV);
close(ZMP);
 
 
# Now import the file
system("zmprov --verbose --file=$fileout");
 
 
# Hash the file to compare on the next run
system("md5sum $filein > $md5file");
 
 
# END MAIN STUFF
#####
 
 
# To find out how long the script takes
system("echo end > /tmp/zmad-end");