Announcement

Collapse
No announcement yet.

LDAP filters for group and members!

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • LDAP filters for group and members!

    hi all,
    I need to create the list of all groups and distribution lists used in domain in the form of Excel file. I know I can find all groups and distributionlist by "(objectcategory=group)", and list their names, but how can I list all members name in next columns( member's CN only)?
    following is my VB script to create that excel file.

    Code:
    ' Bind to RootDSE - this object is used to 
    ' get the default configuration naming context
    set objRootDSE = getobject("LDAP://RootDSE")
    
    ' File name to export to
    strExportFile = "C:\MyExport.xls" 
    ' Root of search set to default naming context.
    ' RootDSE saves hard-coding the domain.  
    ' If want to search within an OU rather than the domain,
    ' specify the distinguished name of the ou.  e.g. 
    strRoot = objRootDSE.Get("DefaultNamingContext")
    ' Filter for user accounts - could be modified to search for specific users,
    ' such as those with mailboxes, users in a certain department etc.
    strfilter = "(objectcategory=group)"
    ' Attributes to return from the query
    strAttributes = "cn," & _
                    "member," &_
             	"groupType," & _
    		"mail," & _
    		"managedby," & _
    		"Description"
    'Scope of the search.  Change to "onelevel" if you didn't want to search child OU's
    strScope = "subtree"
    
    set cn = createobject("ADODB.Connection")
    set cmd = createobject("ADODB.Command")
    
    cn.open "Provider=ADsDSOObject;"
    cmd.ActiveConnection = cn
    cmd.commandtext = "<LDAP://" & strRoot & ">;" & strFilter & ";" & _
    		   strAttributes & ";" & strScope
    
    set rs = cmd.execute
    
    ' Use Excel COM automation to open Excel and create an excel workbook
    set objExcel = CreateObject("Excel.Application")
    set objWB = objExcel.Workbooks.Add
    set objSheet = objWB.Worksheets(1)
    
    ' Copy Field names to header row of worksheet
    For i = 0 To rs.Fields.Count - 1
    	objSheet.Cells(1, i + 1).Value = rs.Fields(i).Name
    	objSheet.Cells(1, i + 1).Font.Bold = True
    Next
    
    ' Copy data to the spreadsheet
    objSheet.Range("A2").CopyFromRecordset(rs)
    ' Save the workbook
    objWB.SaveAs(strExportFile)
    
    ' Clean up
    rs.close
    cn.close
    set objSheet = Nothing
    set objWB =  Nothing
    objExcel.Quit()
    set objExcel = Nothing
    thanks!
    jimmy
    Last edited by AndyJG247; 26th March 2009, 20:51.

  • #2
    Re: LDAP filters for group and members!

    Ok, I wrote a script myself, hope it will help other people has same issue.

    Code:
    Option Explicit
    
    Const ADS_SCOPE_SUBTREE = 2
    
    Dim rootDSE
    Dim strRootDSE
    Dim strCommand
    Dim strGroupObj
    
    Dim strMember
    
    Dim objExcel
    Dim objConnection
    Dim objCommand
    Dim objRecordSet
    
    Dim objGroup
    Dim objMember
    
    
    Dim arrMemberOf
    
    Dim x
    Dim objWB
    Dim objSheet
    
    Dim strGroupName
    Dim strType
    Dim strMail
    Dim strOwner
    Dim strDes
    
    Dim strExportFile
    Dim strMemberName
    
    Dim arrUser
    Dim arrOwner
    
    
    ' File name to export to
    strExportFile = "C:\MyExport1.xls"
    
    
    'connect to the root of AD
    Set rootDSE = GetObject("LDAP://RootDSE")
    strRootDSE = rootDSE.Get("defaultNamingContext")
    
    
    strCommand = "SELECT cn, distinguishedName, mail, managedBy, description FROM 'LDAP://" & strRootDSE & "' WHERE objectCategory='group' AND cn = '*' AND member = '*'"  
    
    
    'WScript.Echo strRootDSE
    'WScript.Echo strCommand
    
    Set objExcel = CreateObject("Excel.Application")
    Set objWB = objExcel.Workbooks.Add
    set objSheet = objWB.Worksheets(1)
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objCommand =   CreateObject("ADODB.Command")
    objConnection.Provider = "ADsDSOObject"
    objConnection.Open "Active Directory Provider"
    Set objCommand.ActiveConnection = objConnection
    
    objCommand.Properties("Page Size") = 1000
    objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
    'objCommand.Properties("Sort on") = "cn"
    
    
    objCommand.CommandText = strCommand
    Set objRecordSet = objCommand.Execute
    
    objRecordSet.MoveFirst
    
    objExcel.Cells(1, 1).Value = "Group Name"
    objSheet.Cells(1, 1).Font.Bold = True
    objSheet.Cells(1, 1).ColumnWidth = 30
    
    objExcel.Cells(1, 2).Value = "Members"
    objSheet.Cells(1, 2).Font.Bold = True
    objSheet.Cells(1, 2).ColumnWidth = 25
    
    objExcel.Cells(1, 3).Value = "Type"
    objSheet.Cells(1, 3).Font.Bold = True
    objSheet.Cells(1, 3).ColumnWidth = 25
    
    objExcel.Cells(1, 4).Value = "Email"
    objSheet.Cells(1, 4).Font.Bold = True
    objSheet.Cells(1, 4).ColumnWidth = 35
    
    objExcel.Cells(1, 5).Value = "Owner"
    objSheet.Cells(1, 5).Font.Bold = True
    objSheet.Cells(1, 5).ColumnWidth = 15
    
    objExcel.Cells(1, 6).Value = "Description"
    objSheet.Cells(1, 6).Font.Bold = True
    objSheet.Cells(1, 6).ColumnWidth = 35
    
    
    x=2
    Do Until objRecordSet.EOF
        strGroupObj = "LDAP://" & objRecordSet.Fields("distinguishedName").Value
        strGroupName = objRecordSet.Fields("cn").Value
    
        'strType= objRecordSet.Fields("groupType").Value
        Set objGroup = GetObject(strGroupObj)
    Select Case objGroup.GroupType
        Case 2
            strType= "global distribution group"
        Case 4
            strType= "domain local distribution group"
        Case 8
            strType= "universal distribution group"
        Case -2147483646
            strType= "global security group"
        Case -2147483644
            strType= "domain local security group"
        Case -2147483640
            strType= "universal security group"
    End Select
    
    
        strMail= objRecordSet.Fields("mail").Value
        'strowner not working with Fiileds function.
        'strOwner= objRecordSet.Fields("managedBy").Value
        strOwner= objGroup.managedBy
        
        strDes= objRecordSet.Fields("description").Value  
    
        'WScript.Echo strGroupObj
        'WScript.Echo strGroupName
    
         objExcel.Cells(x, 1).Value = strGroupName
         objExcel.Cells(x, 3).Value = strType
         objExcel.Cells(x, 4).Value = strMail
    
         'Next line is use for empty value error handling.
         On Error Resume Next
         strOwner = Mid(strOwner,4,330) 
         arrOwner = Split(strOwner, "," )
         strOwner = arrOwner(0)
        
        'WScript.Echo strOwner
    
         objExcel.Cells(x, 5).Value = strOwner
    
         objExcel.Cells(x, 6).Value = strDes
    
    
    
        arrMemberOf = objGroup.GetEx("member")
        
    For Each strMember in arrMemberOf
    
        On Error Resume Next 
        strMember = Mid(strMember,4,330) 
        arrUser = Split(strMember, "," )
        strMemberName = arrUser(0)
        
        'WScript.echo strMemberName
        objExcel.Cells(x, 2).Value = strMemberName
    
        x = x + 1
    Next
    objRecordSet.MoveNext
    
    Loop
    
    ' Save the workbook
    objWB.SaveAs(strExportFile)
    
    
    objRecordSet.close
    objConnection.close
    set objSheet = Nothing
    set objWB = Nothing
    objExcel.Quit()
    set objExcel = Nothing
    Last edited by AndyJG247; 26th March 2009, 20:52.

    Comment


    • #3
      Re: LDAP filters for group and members!

      I wish I was clever enough to understand that! I've dropped it into CODE tags to make it easier for people to read and use.
      cheers
      Andy

      Please read this before you post:


      Quis custodiet ipsos custodes?

      Comment

      Working...
      X