VBScript to extract users group membership to Excel 1

JayIT · Oct 4, 2011

The subject line says it all, I need a quick way to find out what users are members of in AD.

To go a step further, if it's all possible, I would like to be able to select specific groups and export that to an Excel doc.

Can I run a query against an entire OU and only get the names of those that that members specific groups?

In other words, if Bob Marley and Michael Jackson are is member of administrators group, sales group, IT group and HR group, and I want to know who is member of IT and HR the results should only show Bob and Michael names and those 2 groups.

Any help would save me days of work every 2 months… ?

PHV · Oct 4, 2011

What have YOU tried so far and where in YOUR code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

JayIT · Oct 4, 2011

First of all, I need to specify that I am NOT a programmer what so ever, I just need to find a way to get this part of my new job more productive.

Searching online and trying things I got a script to tell me who is member of what group but what I need is the other way around.

Below is what I have

Const ADS_NAME_INITTYPE_GC = 3 '<--(needs clear access to the Global Catalog!)
Const ADS_NAME_TYPE_NT4 = 3
Const ADS_NAME_TYPE_1779 = 1

Set WshNetwork = WScript.CreateObject("WScript.Network")
NbDomain = WshNetwork.UserDomain

Do until Instr(UCASE(sNTGroup), "\")
sNTGroup = InputBox("Enter the Groupname" & VBNewLine & VBNewLine _
& "notation:" & vbNewLine & "NetBios-Domainname\groupname" & vbNewLine & "(backslash as separator)", _
"Get Members",NbDomain & "\citrix admins")
If sNTGroup = Cancel Then WScript.Quit
Loop

strNTName = split(sNTGroup,"\")(1)
strNetBIOSDomain = split(sNTGroup,"\")(0)

Set objTrans = CreateObject("NameTranslate")
objTrans.Init ADS_NAME_INITTYPE_GC, ""
objTrans.Set ADS_NAME_TYPE_NT4, strNetBIOSDomain & "\" & strNTName

' distinguishedname:
strItemDN = Trim(Replace(objTrans.Get(ADS_NAME_TYPE_1779), "/", "\/"))

Set objGroup = GetObject _
("LDAP://" & strItemDN)
objGroup.GetInfo

arrMemberOf = objGroup.GetEx("member")
List = "Members: "
For Each strMember in arrMemberOf
List = List & strMember & VBNewLine
Next

Wscript.echo List

'wscript.echo list > "c:\script\log.txt

JayIT · Oct 4, 2011

I alos found this one but i am getting an "object required: 'objTextFile' on line 7 char 1

set xapp = WScript.CreateObject("Excel.Application")
xapp.Visible = True
set workbook = xapp.Workbooks.Add
set worksheet = workbook.Worksheets("sheet1")

i = 0
Do Until objTextFile.AtEndOfStream
strNextLine = objTextFile.Readline
objDictionary.Add i, strNextLine
i = i + 1
Loop

y=1
worksheet.cells(y,1).value="Name"
worksheet.cells(y,2).value="Display Name"
worksheet.cells(y,3).value="Mail"
worksheet.Columns(1).ColumnWidth=15
worksheet.Columns(2).ColumnWidth=20
worksheet.Columns(3).ColumnWidth=35
worksheet.Cells(y, 1).Font.Italic = True
worksheet.Cells(y, 1).Font.Bold = True
worksheet.Cells(y, 2).Font.Italic = True
worksheet.Cells(y, 2).Font.Bold = True
worksheet.Cells(y, 3).Font.Italic = True
worksheet.Cells(y, 3).Font.Bold = True

Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
'Create output file
Set fs = CreateObject ("Scripting.FileSystemObject")

Set objRoot = GetObject("LDAP://RootDSE")
strSAT = objRoot.Get("DefaultNamingContext")

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
For Each objItem in objDictionary
strMail = objDictionary.Item(objItem)

objCommand.CommandText = _
"SELECT Name, displayName, mail FROM 'LDAP://dc=XXXXXX,dc=XXX' WHERE objectCategory='user' AND mail =" & "'" & strMail & "'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst

Do Until objRecordSet.EOF
strName = objRecordSet.Fields("Name").Value
strDisplayName= objRecordSet.Fields("displayName").Value
strMail= objRecordSet.Fields("mail").Value
strDepartment = objRecordSet.Fields("department").Value
objRecordSet.MoveNext
worksheet.cells(y+1,1).value=strName
worksheet.cells(y+1,2).value=strDisplayName
worksheet.cells(y+1,3).value=strMail
y=y+1
Loop
Next
MsgBox "Script has completed..."
WScript.Quit

Geates · Oct 5, 2011

That error is indicating that objTextFile was not previously defined.

I'm confused. How does the first script you posted not accomplish what you originally asked for?

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

JayIT · Oct 5, 2011

it doesn't, that's hy I am asking for help.

Geates · Oct 5, 2011

I misread the question. Instead of asking for a group, ask for an OU. The distinguished name for an OU "Users" on the abc.net domain is as follows

CN=Users,DC=abc,DC=net

here's a script that accomplishes what you want.

Code:

function getOUMembers(strDN)
	'Default return value
	getOUMembers = "None"
	
	'Prepare to query LDAP for a User's memberships (.MemberOf property)
	set objCommand = CreateObject("ADODB.Command")
	set objConnection = CreateObject("ADODB.Connection")
	objConnection.Provider = "ADsDSOObject"
	objConnection.Open "Active Directory Provider"
	objCommand.ActiveConnection = objConnection

	strQuery = "SELECT member FROM 'LDAP://" & strDN & "'"
	
	'Execute query
	objCommand.CommandText = strQuery
	set objRecordSet = objCommand.Execute

	'Traverse returned memberships
	'Depending on how 'clean' your LDAP is, the query may return null values
	objRecordSet.MoveFirst
	do until objRecordSet.EOF
		obj = objRecordSet.Fields(0)
		'if the object is an array of strings (8192 + 8) then traverse those items
		if (vartype(obj) = 8204) then
			arrMembers = obj
			for i = 0 to ubound(arrMembers)
				strMembers = strMembers & arrMembers(i) & vbNewLine
			next
		end if
		objRecordSet.moveNext
	loop
	getOUMembers = strMembers
end function

set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

strOU = inputBox ("Enter OU name: ")
strOU = "CN=" & strOU & "," & strDNSDomain 'CN=xxx,DC=xxx,DC=xxx,DC=xxx 
wscript.echo getOUMembers(strOU)

The comments in this script are what I have observed. If someone thinks otherwise, please say so.

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

JayIT · Oct 5, 2011

This kind of stuff makes me what to take some programming classes. Thanks for helping me.

When I run your script I get a “Table does not exist." Line 16 Char 5

Geates · Oct 5, 2011

Something in LDAP isn't configured. Put a msgbox strQuery on Line 13. What does it say?

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

JayIT · Oct 5, 2011

SELECT member FROM 'LDAP;//CN=******,DC=******,DC=ADS'

PS ****=domain name

Geates · Oct 5, 2011

OUs can be members of other OUs (nested). If you want to collect from a nested OU, you'll have to expressly say so. To get the members of OU GroupA in OU Users.

CN=GroupsA,OU=Users,DC=abc,DC=net

replace all code below strDNSDomain (line 36) with

Code:

strOU = inputBox ("Enter complete OU name (eg. Users\GroupA): ")
arrOUs = split(strOU, "\")
strDN = "CN=" & arrOUs(ubound(arrOUs))
for i = 0 to ubound(arrOUs) - 1
	strDN = strDN & ",OU=" & arrOUs(i)
next
strDN = strDN & "," & strDNSDomain 'CN=xxx,DC=xxx,DC=xxx,DC=xxx 
wscript.echo getOUMembers(strDN)

or put on error resume next as the first line in the function to hide all errors (not recommended)

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

JayIT · Oct 5, 2011

ok, your script is working like a charm. is there a quick way to change it so it looks at a user and returns if he is a member of specific groups??

Geates · Oct 5, 2011

sure. in the getOUMembers function, change the query to get memberof

Code:

strQuery = "SELECT memberOf FROM 'LDAP://" & strDN & "'"

The strDN's CN must reflect the users sam name and the OUs the user resides in

Code:

strDN = "CN=John R. Doe,OU=IT,OU=User,DC=abc,DC=net"

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

JayIT · Oct 6, 2011

the second line of code does not go into the scrip, it's what I input in the popup box,right?

Geates · Oct 6, 2011

I suppose so. The second line is to show you in what format an LDAP distinguished name need to be to enumerate user memberships.
For John R. Doe, you would input User\IT\John R. Doe.

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

JayIT · Oct 6, 2011

hum... regardless of what I enter it keeps teling me that there is no such object on the server, and I know I am there...

Below is the code I have.

Code:

function getOUMembers(strDN)
    'Default return value
    getOUMembers = "None"
    
    'Prepare to query LDAP for a User's memberships (.MemberOf property)
    set objCommand = CreateObject("ADODB.Command")
    set objConnection = CreateObject("ADODB.Connection")
    objConnection.Provider = "ADsDSOObject"
    objConnection.Open "Active Directory Provider"
    objCommand.ActiveConnection = objConnection

    strQuery = "SELECT memberOf FROM 'LDAP://" & strDN & "'"
   msgbox strQuery  
    'Execute query
    objCommand.CommandText = strQuery

    set objRecordSet = objCommand.Execute

    'Traverse returned memberships
    'Depending on how 'clean' your LDAP is, the query may return null values
    objRecordSet.MoveFirst
    do until objRecordSet.EOF
        obj = objRecordSet.Fields(0)
        'if the object is an array of strings (8192 + 8) then traverse those items
        if (vartype(obj) = 8204) then
            arrMembers = obj
            for i = 0 to ubound(arrMembers)
                strMembers = strMembers & arrMembers(i) & vbNewLine
            next
        end if
        objRecordSet.moveNext
    loop
    getOUMembers = strMembers
end function

set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

strOU = inputBox ("Enter complete OU name (eg. Users\GroupA): ")
arrOUs = split(strOU, "\")
strDN = "CN=" & arrOUs(ubound(arrOUs))


for i = 0 to ubound(arrOUs) - 1
    strDN = strDN & ",OU=" & arrOUs(i)
next
strDN = strDN & "," & strDNSDomain 'CN=xxx,DC=xxx,DC=xxx,DC=xxx 
wscript.echo getOUMembers(strDN)

Geates · Oct 6, 2011

The distinguished name (DN) is not found in LDAP. It suggests that the path you are using is wrong. What does you DN looking like?

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

JayIT · Oct 6, 2011

I did enter the wrong stuff, now it's working...

If a user is member of 5 groups but I only care and need to query 2 specific groups, where and how to I input that in the script?

and this should be my final question, I promise! THANK YOU SO MUCH FOR YOUR HELP!!!!!

Geates · Oct 6, 2011

Then function returns a string of members each separated by a newline. Right now, this string is being immediately echoed. Instead, store it in a variable so we can use it elsewhere. Use instr() to find text in a string. It will return a 0 if the text is not found. Otherwise, the position where the text is found is returned.

Code:

[s]wscript.echo getOUMembers(strDN)[/s]
strMembers = getOUMembers(strDN)

intGroupA = instr(strMembers, "GroupA")
intGroupB = instr(strMembers, "GroupB")

if (intGroupA > 0) then strGroups = strGroups & "GroupA" & vbNewLine
if (intGroupB > 0) then strGroups = strGroups & "GroupB" & vbNewLine

I reference this link daily.

http://raqxtr.sstar.com/caspdoc/html/vbscript_language_reference.htm

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

JayIT · Oct 6, 2011

ok... so if i run it and nothing shows up on the screen... it means that I am doing something wrong.... but what??

Code:

'wscript.echo getOUMembers(strDN)

strMembers = getOUMembers(strDN)

intGroupA = instr(strMembers, "julestest")
'intGroupB = instr(strMembers, "GroupB")

if (intGroupA > 0) then strGroups = strGroups & "julestest" & vbNewLine
'if (intGroupB > 0) then strGroups = strGroups & "GroupB" & vbNewLine

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!

VBScript to extract users group membership to Excel 1

MIS

MIS

MIS

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Similar threads

Log in

Part and Inventory Search

Sponsor