Please help with VBSCRIPT doing a simple LDAPquery enumerating members

[dniz0r]

Hello, I am trying to enumerate all users in Active Directory and display what groups they are a member of.

My script seems to work fine when i do a wscript.echo to output the data, but when I try and write the output to a file by objfile.writeline i get an "error code: 800A01C3", "Error: Object not a collection."

please help, any advice is appreciated.

my code is
--------------------------

Option Explicit
Dim adoCommand, adoConnection,strFile, strBase, strFilter, strAttributes, WriteText
Dim objFSO, objFile, OutputFilePath, objTextFile, objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strCN, strMember, strGroup, arrGroups



strFile = "memberOf_out.txt"
OutputFilePath = "C:\Documents and Settings\hcabrera\My Documents\code\logs\"

' Create the File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(strFile) Then
wscript.echo "File already exists. Appending to file: " & strFile
Else
Set objFile = objFSO.CreateTextFile(strFile)
Wscript.Echo "Just created " & strFile
End If


' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection

' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = "<LDAP://" & strDNSDomain & ">"

' Filter on user objects.
strFilter = "(&(objectCategory=person)(objectClass=user))"
'strFilter = "(&(objectCategory=group)(|(cn=Test*)(cn=Admin*)))"


' Comma delimited list of attribute values to retrieve.
strAttributes = "sAMAccountName,cn,MemberOf"


Set objTextFile = objFSO.OpenTextFile(strFile, 8, True)


' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

' Run the query.
Set adoRecordset = adoCommand.Execute

' Enumerate the resulting recordset.
Do Until adoRecordset.EOF
' Retrieve values and display.
arrGroups = adoRecordset.Fields("memberOf").Value
strName = adoRecordset.Fields("sAMAccountName").Value
strCN = adoRecordset.Fields("cn").value
For Each strGroup In arrGroups
'
' WriteText.writeline("Name:" & strName & vbCr)
'writeText.writeline("Memberships:" & strGroup & vbCr)
'Wscript.Echo "NT Name: " & strName & ", Common Name: " & strCN & ", Member Of: " & strGroup
objTextFile.WriteLine("NT Name: " & strName & ", Member Of: " & strGroup)
Next
' Move to the next record in the recordset.
adoRecordset.MoveNext
Loop

' Clean up.
adoRecordset.Close
adoConnection.Close

 

[markdmac]

Here is a sample that will do what you are asking.

'==========================================================================
'
' NAME: ListUsers.vbs
'
' AUTHOR: Mark D. MacLachlan , The Spider's Parlor
' URL: [URL unfurl="true"]http://www.thespidersparlor.com[/URL]
' DATE  : 12/4/2007
' COPYRIGHT (c) 2007 All Rights Reserved
'
' COMMENT: 
'
'    THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF
'    ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
'    THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
'    PARTICULAR PURPOSE.
'
'    IN NO EVENT SHALL THE SPIDER'S PARLOR AND/OR ITS RESPECTIVE SUPPLIERS 
'    BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY
'    DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS,
'    WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS
'    ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE
'    OF THIS CODE OR INFORMATION.
'
'==========================================================================
On Error Resume Next

Dim qQuery, objConnection, objCommand, objRecordSet, obj
Dim oRootDSE, strDomain

Set oRootDSE = GetObject("LDAP://rootDSE")
strDomain = oRootDSE.get("defaultNamingContext")

' other categories = computer, user, printqueue, group
qQuery = "<LDAP://" & strDomain &">;" & _
		"(objectCategory=person)" & _
       ";sAMAccountName,distinguishedName;subtree"

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Open "Provider=ADsDSOObject;"
objCommand.ActiveConnection = objConnection
objCommand.CommandText = qQuery
Set objRecordSet = objCommand.Execute

While Not objRecordSet.EOF
    Set objUser = GetObject("LDAP://" & objRecordSet.Fields("distinguishedName"))
    For Each Group In objUser.MemberOf
    	Set objGroup = GetObject("LDAP://" & Group)
    	If Len(objGroup.Name) > 0 Then
    		groupName = Right(objGroup.Name,Len(objGroup.Name)-3)
	    	groupReport = groupReport & vbTab & groupName & vbCrLf
	    End If
    Next
    Report = Report & objRecordSet.Fields("sAMAccountName") & vbCrLf & groupReport
    groupReport = Nothing
    objRecordSet.MoveNext
    
Wend

objConnection.Close

Dim objFSO
Set objFSO = CreateObject("scripting.filesystemobject")
Set ts = objFSO.CreateTextFile("report.txt",2)
ts.write Report
WScript.Echo "Done"

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[dniz0r]

Hi Mark,


Thanks for the response. That is my first VBScript ever,. i come from more of a perl/php background but now i work in a all windows environment so im going to focus on learning some vbscript.

I figured out what my issue was. The for loop was causing that error if the key value(memberOf) was empty. so i did:

If IsArray(arrGroups) Then

For Each strGroup In arrGroups
'Wscript.Echo "NT Name: " & strName & ", Common Name: " & strCN & ", Member Of: " & strGroup
objTextFile.WriteLine("NT Name: " & strName & ", Member Of: " & strGroup & ", PNUM: " & strEmployeeid & ", Supervisor: " & strManager)
Next
Else
objTextFile.writeline("NT Name: " & strName & ", Member Of: No Memberships")
End If


Thanks again for the feedback!

regards.

 

[TheKidd]

Welcome to the Windows world dniz0r. You might want to start learning Powershell as well. It has a slight perl feel to it.

 

[dniz0r]

Thanks!

yea i've already started messing with powershell because the security engineer i replaced had written a few scripts with it.

 

[dniz0r]

Hey guys,, can someone help me output/parse this a little better ?? This is what my output looks like:


NAME Member Of Employee ID Supervisor Title
NT Name: kapadmin Member Of: CN=Administrators,CN=Builtin,DC=charlie,DC=aninc,DC=com PNUM: Supervisor: Title:
NT Name: user1 Member Of: CN=KHE Domain Admins,OU=Secured,OU=Groups OU,DC=charlie,DC=aninc,DC=com PNUM: Supervisor: Title:
NT Name: backup Member Of: CN=KHER Non-Employee Users,OU=Atlanta Office,OU=Admin,OU=Security,OU=Groups OU,DC=charlie,DC=aninc,DC=com PNUM: Supervisor: Title:


it looks like its tabbing some of the elements of the array but not all. IT also doesnt seem like its aligning things properly. Any help would be greatly appreciated.



this is my updated code.



Option Explicit
Dim adoCommand, adoConnection,strFile, strBase, strFilter, strAttributes, WriteText
Dim objFSO, objFile, OutputFilePath, objTextFile, objRootDSE, strDNSDomain, strQuery
Dim strManager, strTitle, strEmployeeid, adoRecordset, strName, strCN, strMember, strGroup, arrGroups


strFile = "memberOf_out.txt"
OutputFilePath = "C:\Documents and Settings\hcabrera\My Documents\code\logs\"

' Create the File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(strFile) Then
wscript.echo "File already exists. Appending to file: " & strFile
Else
Set objFile = objFSO.CreateTextFile(strFile)
Wscript.Echo "Just created " & strFile
End If


' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection

' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = "<LDAP://" & strDNSDomain & ">"

' Filter on user objects.
strFilter = "(&(objectCategory=person)(objectClass=user))"
'strFilter = "(&(objectCategory=group)(|(cn=Test*)(cn=Admin*)))"


' Comma delimited list of attribute values to retrieve.
strAttributes = "sAMAccountName,cn,MemberOf,employeeid,manager,title"


Set objTextFile = objFSO.OpenTextFile(strFile, 8, True)


' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

' Run the query.
Set adoRecordset = adoCommand.Execute


objTextFile.WriteLine("NAME" & vbTab & "Member Of" & vbTab & "Employee ID" & vbTab & "Supervisor")




' Enumerate the resulting recordset.
Do Until adoRecordset.EOF
' Retrieve values and display.
arrGroups = adoRecordset.Fields("memberOf").Value
strName = adoRecordset.Fields("sAMAccountName").Value
strCN = adoRecordset.Fields("cn").value
strEmployeeid = adoRecordset.Fields("employeeid").value
strManager = adoRecordset.Fields("manager").value
strTitle = adoRecordset.Fields("title").value

If IsArray(arrGroups) Then

For Each strGroup In arrGroups
'Wscript.Echo "NT Name: " & strName & ", Common Name: " & strCN & ", Member Of: " & strGroup
objTextFile.WriteLine("NT Name: " & strName & vbTab & "Member Of: " & strGroup & vbTab & "PNUM: " & strEmployeeid & vbTab & "Supervisor: " & strManager & vbTab & "Title: " & strTitle)
Next
Else
objTextFile.writeline("NT Name: " & strName & vbTab & "NO Group Memberships " & vbTab & "PNUM: " & strEmployeeid & vbTab & "Supervisor: " & strManager & vbTab & "Title: " & strTitle)
End If


' Move to the next record in the recordset.
adoRecordset.MoveNext
Loop

' Clean up.
adoRecordset.Close
adoConnection.Close

 

[markdmac]

Are you running this from a workstation or the server? I would recommend that you use a workstation and then code your information directly into an Excel spreadsheet so it will be more organized and easier to manipulate.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[dniz0r]

Hi Mark,


Yes i am doing it from my workstation.. I guess I will try and look for some documentation on how to export it directly into an excell spreadsheet

 

[markdmac]

Here is a simple example of reading and writing from/to Excel

'this grabs an already open excel spreadsheet
set x = getobject(,"excel.application")

'Read a value in cell A1: Note the columns are 
'referred to by NUMBER not Letter
'              row,column
login = x.cells(1, 1).value
msgBox login

'write to a cell
x.cells(1, 2).value = "This is some text I wrote"

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[tsuji]

[1]
>I figured out what my issue was. The for loop was causing that error if the key value(memberOf) was empty. so i did:
What you did is still not complete taking into account of all possible outcomes. This is how it's done.
[tt]
If IsArray(arrGroups) Then

For Each strGroup In arrGroups
'Wscript.Echo "NT Name: " & strName & ", Common Name: " & strCN & ", Member Of: " & strGroup
objTextFile.WriteLine("NT Name: " & strName & ", Member Of: " & strGroup & ", PNUM: " & strEmployeeid & ", Supervisor: " & strManager)
Next
[red]ElseIf (TypeName(arrGroups) = "String") Then
objTextFile.WriteLine("NT Name: " & strName & ", Member Of: " & arrGroup & ", PNUM: " & strEmployeeid & ", Supervisor: " & strManager)[/red]
Else
objTextFile.writeline("NT Name: " & strName & ", Member Of: No Memberships")
End If
[/tt]
[1.1] ref

http://www.rlmueller.net/MemberOf.htm

[2] As to alignment of data etc, it is not very suitable to be handled in a plain text file alone. You can simply out the data to a text file with tab separated csv. Open the csv simply any viewer that can read column structure, like excel or whatever. Use a csv as a vehicle for data exchange, unless you want to view data directly and immediately, then you directly output to an excel spreadsheet and that is not handled by filesystemobject.

 

[TheKidd]

I agree with markdmac and tsuji, but if you do want to output directly to excel, here's a snippet that might help you:

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