No announcement yet.

LDAP filters for group and members!

  • 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.

    ' 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," & _
    '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") "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
    ' Copy data to the spreadsheet
    ' Save the workbook
    ' Clean up
    set objSheet = Nothing
    set objWB =  Nothing
    set objExcel = Nothing
    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.

    Option Explicit
    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
    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
    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
    ' Save the workbook
    set objSheet = Nothing
    set objWB = Nothing
    set objExcel = Nothing
    Last edited by AndyJG247; 26th March 2009, 20:52.


    • #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.

      Please read this before you post:

      Quis custodiet ipsos custodes?