export user "memberOf" for each user -> without ldifde/csvde 1

kokser · Oct 7, 2009

Hello. I am using this script.

Code:

Dim objFSO, WriteText, strText, strNewText, objFile, objConnection, objCommand, objRecordSet, objUser, strUserDN, strResult, arrProxyAddresses, objMemberOf

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCOmmand.ActiveConnection = objConnection

objCommand.CommandText = _
"SELECT Name, sAMAccountName, mail, proxyAddresses, homeDirectory, homeDrive, memberOf, scriptPath, distinguishedName FROM 'LDAP://dc=company,dc=local' WHERE objectCategory='Person'" 
Set objRecordSet = objCommand.Execute

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set WriteText = objFSO.OpenTextFile("test.txt", 8, true)

objRecordSet.MoveFirst

While Not objRecordSet.EOF
	strUserDN = objRecordSet.Fields("distinguishedName")
	set objUser = GetObject("LDAP://" & strUserDN)
		if objUser.AccountDisabled = FALSE Then
			Do Until objRecordSet.EOF
				WriteText.WriteLine("Name" & vbTab & vbTab & ":" & objRecordSet.Fields("Name").Value & vbCr)
				WriteText.WriteLine("Account" & vbTab & vbTab & ":"  & objRecordSet.Fields("sAMAccountName").Value & vbCr)
				WriteText.WriteLine("Mail Addresser" & vbTab & ":" & vbCrLf & vbTab & vbTab & objRecordSet.Fields("mail").Value & vbCr)
					arrProxyAddresses = objRecordSet.Fields("proxyAddresses")
					if isArray(objRecordSet.Fields("proxyAddresses")) Then
						strResult = "Proxy Addresses" & vbTab & ":"
						For Each ProxyAddress in arrProxyAddresses
							WriteText.WriteLine (vbTab & vbTab & ProxyAddress & vbCr) 
						Next
						objRecordSet.MoveNext
					End if
				WriteText.WriteLine("Memberships" & vbTab & vbTab & ":" & vbCr)
					
				WriteText.WriteLine("Homedir" & vbTab & vbTab & ":" & objRecordSet.Fields("homeDirectory").Value & vbCr)
				WriteText.WriteLine("Homedrive" & vbTab & ":" & objRecordSet.Fields("homeDrive").Value & vbCr)
				WriteText.WriteLine("Scriptpath" & vbTab & vbTab & ":" & objRecordSet.Fields("scriptPath").Value & vbCrLf)
			objRecordSet.MoveNext
			Loop
		Else
			objRecordSet.MoveNext
		End if
Wend

WriteText.Close
Set WriteText = NOTHING

Set objFile = objFSO.OpenTextFile("test.txt", 1, true)
strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, "smtp", "")
strNewText = Replace(strNewText, "SMTP", "")
Set objFile = objFSO.OpenTextFile("test.txt", 2, true)
objFile.WriteLine strNewText
objFile.Close

Set objFSO= NOTHING

WScript.Echo "Done"

Under the line
WriteText.WriteLine("Memberships" & vbTab & vbTab & ":" & vbCr)
I want to enumerate all the groups that a user is member of. I think it is possible to do it the same way I did with proxyaddresses, but I can't find the correct value for each "groups" instead of "proxyaddress".

mrmovie · Oct 7, 2009

is it the 'memberOf' field?

kokser · Oct 7, 2009

what I want to convert it to is

Code:

					arrMemberOf = objRecordSet.Fields("memberOf")
					if isArray(objRecordSet.Fields("memberOf")) Then
						strResult = "NEED VALUE" & vbTab & ":"
						For Each NEED VALUE in arrMemberOf
							WriteText.WriteLine (vbTab & vbTab & NEED VALUE & vbCr) 
						Next
						objRecordSet.MoveNext
					End if

mrmovie · Oct 7, 2009

arrMemberOf = objRecordSet.Fields("memberOf")
if isArray(objRecordSet.Fields("memberOf")) Then
strResult = "NEED VALUE" & vbTab & ":"
For Each aMemberOf in arrMemberOf
WriteText.WriteLine (vbTab & vbTab & aMemberOf & vbCr)
Next
End if

note the fact I have removed the objRecordSet.MoveNext.
You need to remove this method call from all over your While Wend loop, otherwise you will skip lots of information. have just one .MoveNext right before the Wend

kokser · Oct 7, 2009

nice, it almost works the way I want it to ;P

script:

Code:

					arrMemberOf = objRecordSet.Fields("memberOf")
					if isArray(objRecordSet.Fields("memberOf")) Then
						strResult = "aMemberOf" & vbTab & ":"
						For Each aMemberOf in arrMemberOf
							WriteText.WriteLine (vbTab & vbTab & aMemberOf & vbCr)
						Next
					End if

output from 1 user:

Code:

CN=ReportingGroup {dad5784b-342b-492b-8ec2-1f14901ee1db},OU=CRM 4.0 - AVOS,OU=Services-ServiceAccounts,OU=Teknik,DC=Company,DC=local
CN=UserGroup {dad5784b-342b-492b-8ec2-1f14901ee1db},OU=CRM 4.0 - AVOS,OU=Services-ServiceAccounts,OU=Teknik,DC=Company,DC=local
CN=ReportingGroup {b9b40a27-6097-42a6-b3dc-cc6c4c5622d4},OU=CRM 4.0,OU=Services-ServiceAccounts,OU=Teknik,DC=Company,DC=local
CN=UserGroup {b9b40a27-6097-42a6-b3dc-cc6c4c5622d4},OU=CRM 4.0,OU=Services-ServiceAccounts,OU=Teknik,DC=Company,DC=local
CN=PrivUserGroup {b9b40a27-6097-42a6-b3dc-cc6c4c5622d4},OU=CRM 4.0,OU=Services-ServiceAccounts,OU=Teknik,DC=Company,DC=local
CN=PrivUserGroup {965c4041-4b72-4a42-82bf-b341cf484870},OU=CRM 4.0,OU=Services-ServiceAccounts,OU=Teknik,DC=Company,DC=local
CN=PrivReportingGroup {b9b40a27-6097-42a6-b3dc-cc6c4c5622d4},OU=CRM 4.0,OU=Services-ServiceAccounts,OU=Teknik,DC=Company,DC=local
CN=PrivReportingGroup {965c4041-4b72-4a42-82bf-b341cf484870},OU=CRM 4.0,OU=Services-ServiceAccounts,OU=Teknik,DC=Company,DC=local
CN=ReportingGroup {965c4041-4b72-4a42-82bf-b341cf484870},OU=CRM 4.0,OU=Services-ServiceAccounts,OU=Teknik,DC=Company,DC=local
CN=UserGroup {965c4041-4b72-4a42-82bf-b341cf484870},OU=CRM 4.0,OU=Services-ServiceAccounts,OU=Teknik,DC=Company,DC=local
CN=RSA Users,CN=Users,DC=Company,DC=local

I would like it to only output the actual group (CN=group).

mrmovie · Oct 7, 2009

do a Replace(Left(aMemberOf, InStr(aMemberOf, ",")), "CN=", "")
have you made the changes suggested with the .MoveNext?

kokser · Oct 7, 2009

yes, I removed these lines from the script, and it still works. Don't think anything changed though.

Code:

					arrMemberOf = objRecordSet.Fields("memberOf")
					if isArray(objRecordSet.Fields("memberOf")) Then
						For Each aMemberOf in arrMemberOf
							WriteText.WriteLine (vbTab & vbTab & aMemberOf & vbCr)
						Next
					End if

I will try adding the replace line.

kokser · Oct 7, 2009

I get this error now

Code:

Line: 57
Char: 75
Error: Expected ')'

at this line

Code:

strNewText = Replace(strNewText, "Left(aMemberOf, InStr(aMemberOf, ",")), "CN=", "")

kokser · Oct 7, 2009

ah, I fixed it.
Changed to:

Code:

strNewText = Replace(strNewText & Left(aMemberOf, InStr(aMemberOf, ",")), "CN=", "")

BUT, the output is now (just 1 of many lines)

Code:

ReportingGroup {dad5784b-342b-492b-8ec2-1f14901ee1db},OU=CRM 4.0 - AVOS,OU=Services-ServiceAccounts,OU=Teknik,DC=AdvizorIT,DC=local

Which is better, but I don't want anything else than 'ReportingGroup'

mrmovie · Oct 7, 2009

perhaps an

aArray = Split(aMemberOf, ",")
Msgbox aArray(0)

is easier?

kokser · Oct 7, 2009

I'm not experienced enough to know what to do with that.
This script is something I have made over 2 weeks of googling around and picking up small parts and editing.

Can you show me an example of what you want to do with that array?

mrmovie · Oct 7, 2009

aArray = Split(aMemberOf, ",")
strNewText = aArray(0)

kokser · Oct 7, 2009

either I'm putting it the wrong place in the script, orit just doesn't do anything

kokser · Oct 8, 2009

Either way I decided to try something else.

Here is the final script if anyone should ever need it.

Code:

Dim objFSO, WriteText, strText, strNewText, objFile, objConnection, objCommand, objRecordSet, objUser, strUserDN, strResult, arrProxyAddresses, objMemberOf, strFile, StrGrp, strMail

set objFSO = CreateObject("Scripting.FileSystemObject")
strFile = "test.txt"
If objFSO.FileExists("test.txt") Then
objFSO.DeleteFile(strFile)
end if

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCOmmand.ActiveConnection = objConnection

objCommand.CommandText = _
"SELECT Name, sAMAccountName, mail, proxyAddresses, homeDirectory, homeDrive, memberOf, scriptPath, distinguishedName FROM 'LDAP://dc=Company,dc=local' WHERE objectCategory='Person'"
Set objRecordSet = objCommand.Execute

Set WriteText = objFSO.OpenTextFile("test.txt", 8, true)

objRecordSet.MoveFirst

While Not objRecordSet.EOF
	strUserDN = objRecordSet.Fields("distinguishedName")
	set objUser = GetObject("LDAP://" & strUserDN)
		if objUser.AccountDisabled = FALSE Then
			Do Until objRecordSet.EOF
				WriteText.WriteLine("Name" & vbTab & vbTab & ":" & objRecordSet.Fields("Name").Value & vbCr)
				WriteText.WriteLine("Account" & vbTab & vbTab & ":"  & objRecordSet.Fields("sAMAccountName").Value & vbCr)
				WriteText.WriteLine("Mail Addresser" & vbTab & ":" & vbCrLf & vbTab & vbTab & ":" & objRecordSet.Fields("mail").Value & vbCr)
					arrproxyAddresses = objRecordSet.Fields("proxyAddresses")
					if isArray(objRecordSet.Fields("proxyAddresses")) Then
						For Each ProxyAddress in arrProxyAddresses
							strMail = Right(ProxyAddress,Len(ProxyAddress)-4)
							WriteText.WriteLine (vbTab & vbTab & strMail & vbCr)
						Next
					End if
				WriteText.WriteLine("Memberships" & vbTab & ":" & vbCr)
					arrMemberOf = objRecordSet.Fields("memberOf")
					if isArray(objRecordSet.Fields("memberOf")) Then
						For Each aMemberOf in arrMemberOf
							StrGrp = Right(aMemberOf,Len(aMemberOf)-3)
							StrGrp = Left(StrGrp,InStr(StrGrp,",")-1)
							WriteText.WriteLine (vbTab & vbTab & ":" & StrGrp & vbCr)
						Next
					End if
				WriteText.WriteLine("Homedir" & vbTab & vbTab & ":" & objRecordSet.Fields("homeDirectory").Value & vbCr)
				WriteText.WriteLine("Homedrive" & vbTab & ":" & objRecordSet.Fields("homeDrive").Value & vbCr)
				WriteText.WriteLine("Scriptpath" & vbTab & vbTab & ":" & objRecordSet.Fields("scriptPath").Value & vbCrLf)
			objRecordSet.MoveNext
			Loop
		Else
			objRecordSet.MoveNext
		End if
Wend

WriteText.Close
Set WriteText = NOTHING
Set objFSO= NOTHING

WScript.Echo "Done"

mrmovie · Oct 8, 2009

i see you didnt take my advice with regards the numerous .MoveNexts you have? not to mention the fact you have two iterations ( a while\wend and a nested Do Until)through the recordset (good luck with that), sounds like you know best though and my posts were little more than a distraction

kokser · Oct 8, 2009

I guess I misunderstood what you meant with the .MoveNext. I did remove them from the small part where you mentioned it, I just didn't know you meant the whole script. I appreciate your help, but I haven't coded a lot and don't even know what a nested Do Until means. I have tested the script on several of my customers domains, and it works fine

mrmovie · Oct 8, 2009

this is the third post in the forum on roundabouts the same subject, previous helpful posts from members have resulted in no response from yourself. personally i post out of the goodness of my heart.

'customer domains'? well, i would advise that you take the care to read through your code line by line so that you understand exactly what it is doing before you run it against anything. i believe if you read your code you will see the errors in it..this part is of particular interest:

While
set objUser = GetObject("LDAP://" & strUserDN)
if objUser.AccountDisabled = FALSE Then
Do Until objRecordSet.EOF
'pump out all the user data
.movenext
Loop
Wend

can you see what is wrong?

kokser · Oct 8, 2009

I honestly don't see what is wrong.

It checks if a user is disabled. If it is, it skips the user. If not, it proceeds with "pumping out the data" that I request. Then moves on the the next user. It does this until there are no more users.

I tested the script on my own domain before using it on customers, and the results have been nothing but what I wanted.

There is always space for improvement, if you want to help, but I just can't see anything wrong with my script (you kow like when you work with something and can't find the error, and then someone else looks at it for 2 seconds and then shows you the error).

mrmovie · Oct 8, 2009

well, if you are sure you have tested then my observations must be wrong, that your script will find the first 'enabled' user account and proceed to output the rest of the users information to your text file regardless of it they are 'enabled' or not

kokser · Oct 8, 2009

that may be true. I did just see a disabled user in the test.txt, so your observations are most likely correct. It's not that big a deal to include disabled users, it's just easier if they aren't included.

Would moving
if objUser.AccountDisabled = FALSE Then
into the Do Until loop work? I will try

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

export user &quot;memberOf&quot; for each user -&gt; without ldifde/csvde 1

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor

export user "memberOf" for each user -> without ldifde/csvde 1