Link to home
Start Free TrialLog in
Avatar of bsharath
bsharathFlag for India

asked on

Find each user in an OU which all Distribution groups he is in or security groups he is in.

Hi,

Find each user in an OU which all Distribution groups he is in or security groups he is in.
Need to query distribution and security sepertately and get the output as this..

In one file
username
All Distribution groups
All Distribution groups
All Distribution groups

In another file
username
All Security groups
All Security groups
All Security groups

Regards
Sharath
Avatar of RobSampson
RobSampson
Flag of Australia image
Try this.

Regards,

Rob.
strOU = "OU=IT Users,OU=Users,OU=Civic Centre,OU=Sites,"
strDistributionGroups = "MembersOfDistibutionGroups.txt"
strSecurityGroups = "MembersOfSecurityGroups.txt"
 
If Trim(strOU) <> "" Then
	If Right(strOU, 1) <> "," Then strOU = strOU & ","
Else
	strOU = ""
End If
Set objRootDSE = GetObject("LDAP://RootDSE")
Set objOU = GetObject("LDAP://" & strOU & objRootDSE.Get("defaultNamingContext"))
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objDist = objFSO.CreateTextFile(strDistributionGroups, True)
Set objSec = objFSO.CreateTextFile(strSecurityGroups, True)
objDist.WriteLine "Distribution Group Members from LDAP://" & strOU & objRootDSE.Get("defaultNamingContext")
objSec.WriteLine "Security Group Members from LDAP://" & strOU & objRootDSE.Get("defaultNamingContext")
For Each objUser In objOU
	If objUser.Class = "user" Then
		boolFirstGroup = True
		Set colGroups = objUser.Groups
		For Each objGroup In colGroups
			Select Case objGroup.GroupType
			    Case 2
			        'Wscript.Echo "This is a global distribution group."
			        If boolFirstGroup = True Then
			        	objDist.WriteLine VbCrLf & objUser.samAccountName
			        	boolFirstGroup = False
			        End If
			        objDist.WriteLine objGroup.CN
			    Case 4
			        'Wscript.Echo "This is a domain local distribution group."
			        If boolFirstGroup = True Then
			        	objDist.WriteLine VbCrLf & objUser.samAccountName
			        	boolFirstGroup = False
			        End If
			        objDist.WriteLine objGroup.CN
			    Case 8
			        'Wscript.Echo "This is a universal distribution group."
			        If boolFirstGroup = True Then
			        	objDist.WriteLine VbCrLf & objUser.samAccountName
			        	boolFirstGroup = False
			        End If
			        objDist.WriteLine objGroup.CN
			    Case -2147483646
			        'Wscript.Echo "This is a global security group."
			        If boolFirstGroup = True Then
			        	objSec.WriteLine VbCrLf & objUser.samAccountName
			        	boolFirstGroup = False
			        End If
			        objSec.WriteLine objGroup.CN
			    Case -2147483644
			        'Wscript.Echo "This is a domain local security group."
			        If boolFirstGroup = True Then
			        	objSec.WriteLine VbCrLf & objUser.samAccountName
			        	boolFirstGroup = False
			        End If
			        objSec.WriteLine objGroup.CN
			    Case -2147483640
			        'Wscript.Echo "This is a universal security group."
			        If boolFirstGroup = True Then
			        	objSec.WriteLine VbCrLf & objUser.samAccountName
			        	boolFirstGroup = False
			        End If
			        objSec.WriteLine objGroup.CN
			End Select
		Next
		Set colGroups = Nothing
	End If
Next
objDist.Close
objSec.Close
MsgBox "Done"

Open in new window

Avatar of bsharath
bsharath
Flag of India image

ASKER

Rob the output is nice but for example
I am a member of 5 Distribution groups where as the output shows me as 10 groups and when manually i go to the groups i am not there .

For the security groups my name is not available there at all. Where as i am a member of many Security groups...
Avatar of bsharath
bsharath
Flag of India image

ASKER

Rob the output is nice but for example
I am a member of 5 Distribution groups where as the output shows me as 10 groups and when manually i go to the groups i am not there .

For the security groups my name is not available there at all. Where as i am a member of many Security groups...
ASKER CERTIFIED SOLUTION
Avatar of rejoinder
rejoinder
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bsharath
bsharath
Flag of India image

ASKER

I get the users queries only to the alphabit "R"
After that S,T,U,V,W starting users are not queried...

Avatar of bsharath
bsharath
Flag of India image

ASKER

I get the users queries only to the alphabit "R"
After that S,T,U,V,W starting users are not queried...

Avatar of RobSampson
RobSampson
Flag of Australia image
Would that be only returning 1000 records?  The "Page Size" property of the connection needs to be specified, as described by Microsoft:
"By default Active Directory gives us back only 1,000 records. However, we can change that by specifying a page size, something we do using code similar to this:

objCommand.Properties("Page Size") = 1000

What good does that do us? Well, surprisingly, it does quite a bit of good. When you specify a page size in your search script you do two things. First, you override Active Directorys default limit of 1,000 items being returned in a search query. Second, you specify how many items should be returned in a single batch. When you set a page size Active Directory wont try to gather up all the user accounts and return them en masse; instead, Active Directory will gather up and transmit these accounts in batches. Because we set the page size to 1,000 that means Active Directory will gather up the first 1,000 accounts and send you those. After that first batch is sent it will then collect the next 1,000 accounts and send those. Because there is a break between batches (even if that break isnt discernible to you) it reduces the load on the domain controller."
"The long and short of it: without specifying a page size your search script will return only 1,000 items, maximum. Specify a page size and youll get back everything, albeit in batches."



So, try this. I've added a few Page Size lines...

Regards,

Rob.
'http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_23845528.html
 
Const adVarChar = 200
Const VarCharMaxCharacters = 255
Const adFldIsNullable = 32
 
Dim arrDomainNames
 
'Uncomment the next line to input your own domain names
'arrDomainNames = array("DOMAIN","DC=subdomain1,DC=domain,DC=com")
 
'Edit this line to point to the OU you desire to query
strOU = "OU=IT Users,OU=Users,OU=Civic Centre,OU=Sites,"
 
strDistributionGroups = "MembersOfDistibutionGroups.txt"
strSecurityGroups     = "MembersOfSecurityGroups.txt"
 
If Trim(strOU) <> "" Then
	If Right(strOU, 1) <> "," Then strOU = strOU & ","
Else
	strOU = ""
End If
 
if NOT IsArray(arrDomainNames) then
    GetDomainNames
End If
 
Sub GetDomainNames
    set objRootDSE   = GetObject("LDAP://RootDSE")
    strBase          = "<LDAP://cn=Partitions," & objRootDSE.Get("ConfigurationNamingContext") & ">;"
    strFilter        = "(&(objectcategory=crossRef)(systemFlags=3));"
    strAttrs         = "name,trustParent,nCName,dnsRoot,distinguishedName;"
    strScope         = "onelevel"
    set objConn      = CreateObject("ADODB.Connection")
    objConn.Provider = "ADsDSOObject"
    
    objConn.Open "Active Directory Provider"
    objConn.Properties("Page Size") = 1000
    set objRS = objConn.Execute(strBase & strFilter & strAttrs & strScope)
    objRS.MoveFirst
    
    set arrDomainNames     = CreateObject("Scripting.Dictionary")
    set dicDomainHierarchy = CreateObject("Scripting.Dictionary")
    set dicDomainRoot      = CreateObject("Scripting.Dictionary")
    
    while not objRS.EOF 
        dicDomainRoot.Add objRS.Fields("name").Value, objRS.Fields("nCName").Value
        if objRS.Fields("trustParent").Value <> "" then
            arrDomainNames.Add objRS.Fields("name").Value, 0
            set objDomainParent = GetObject("LDAP://" & objRS.Fields("trustParent").Value)
            dicDomainHierarchy.Add objRS.Fields("name").Value,objDomainParent.Get("name")
       else 
            arrDomainNames.Add objRS.Fields("name").Value, 1
       end if
       objRS.MoveNext
    wend
    for each strDomain in arrDomainNames
        'msgbox strDomain
    next
End Sub
 
Set GroupMembershipDB = CreateObject("ADOR.Recordset")
GroupMembershipDB.Fields.Append "SAMAccountName", adVarChar, VarCharMaxCharacters, adFldIsNullable
GroupMembershipDB.Fields.Append "PrimaryGroupToken", adVarChar, VarCharMaxCharacters, adFldIsNullable
GroupMembershipDB.Fields.Append "DistinguishedName", adVarChar, VarCharMaxCharacters, adFldIsNullable
GroupMembershipDB.Fields.Append "MemberDistinguishedName", adVarChar, VarCharMaxCharacters, adFldIsNullable
GroupMembershipDB.Fields.Append "Type", adVarChar, VarCharMaxCharacters, adFldIsNullable
GroupMembershipDB.Open
 
'Run sub to populate the group members db
FillGroupList
 
'Query OU and compare users to groups they are members of
GetUsersBasedOnOU
 
Sub FillGroupList
    Set adoCommandGL = CreateObject("ADODB.Command")
    Set adoConnectionGL = CreateObject("ADODB.Connection")
    adoConnectionGL.Provider = "ADsDSOObject"
    adoConnectionGL.Open "Active Directory Provider"
    adoCommandGL.ActiveConnection = adoConnectionGL
    adoCommandGL.Properties("Page Size") = 1000
 
    for each strDomain in arrDomainNames
        strBase = "<LDAP://" & strDomain & ">"        
        strFilter = "(objectCategory=group)"
        strAttributes = "sAMAccountName,primaryGroupToken,distinguishedName,samaccounttype,member,managedby,mail"
        strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
        Set adoRecordsetGL = CreateObject("ADODB.Recordset")
        adoRecordsetGL.CursorLocation = 3
        adoRecordsetGL.Sort = "distinguishedname"
        adoRecordsetGL.Open strQuery, adoConnectionGL, , , 1
        Do Until adoRecordsetGL.EOF
            intNumberOfMembers = 0
            strNTName            = adoRecordsetGL.Fields("sAMAccountName").Value
            strPrimary           = adoRecordsetGL.Fields("primaryGroupToken").Value
            strdistinguishedName = adoRecordsetGL.Fields("distinguishedName").Value
            strType              = GroupType(adoRecordsetGL.Fields("samaccounttype").Value)
            if NOT IsNull(adoRecordsetGL.Fields("member").Value) then
                for each strMember in adoRecordsetGL.Fields("member").Value
                    GroupMembershipDB.AddNew
                    GroupMembershipDB("sAMAccountName")          = strNTName
                    GroupMembershipDB("primaryGroupToken")       = strPrimary
                    GroupMembershipDB("distinguishedName")       = strdistinguishedName
                    GroupMembershipDB("MemberDistinguishedName") = strMember
                    GroupMembershipDB("Type")                    = strType
                    GroupMembershipDB.Update
                next
            else
                GroupMembershipDB.AddNew
                GroupMembershipDB("sAMAccountName")          = strNTName
                GroupMembershipDB("primaryGroupToken")       = strPrimary
                GroupMembershipDB("distinguishedName")       = strdistinguishedName
                GroupMembershipDB("MemberDistinguishedName") = ""
                GroupMembershipDB("Type")                    = strType
                GroupMembershipDB.Update
            End if
            adoRecordsetGL.MoveNext
        Loop
    next
End Sub
 
Function GroupType(strType)
    Select Case strType
        Case 2,268435457,4,536870913,8,268435457 'Distribution Groups
            GroupType = "Distribution Group"
        Case -2147483646,268435456,-2147483644,536870912,-2147483640,268435456 'Security Groups
            GroupType = "Security Group"
        Case Else
            GroupType = "Security Group"
    End Select
End Function
 
Sub GetUsersBasedOnOU
    Set objRootDSE = GetObject("LDAP://RootDSE")
    Set objFSO     = CreateObject("Scripting.FileSystemObject")
    Set objDist    = objFSO.CreateTextFile(strDistributionGroups, True)
    Set objSec     = objFSO.CreateTextFile(strSecurityGroups, True)
 
    objDist.WriteLine "Distribution Group Members from LDAP://" & strOU & objRootDSE.Get("defaultNamingContext")
    objSec.WriteLine "Security Group Members from LDAP://" & strOU & objRootDSE.Get("defaultNamingContext")
 
    Set adoCon = CreateObject("ADODB.Connection")
    adoCon.Provider = "ADsDSOObject"
    adoCon.Open "ADSI"
    adoCon.Properties("Page Size") = 1000
    Set adoRS = adoCon.Execute("SELECT cn,distinguishedName,primaryGroupID FROM 'LDAP://" & strOU & objRootDSE.Get("defaultNamingContext") & "' WHERE objectClass='user' AND objectCategory='Person' Order By CN")
    If Not adoRS.EOF Then
        Do While Not adors.EOF
            strCN                = adoRs.Fields("cn")
            strdistinguishedName = adoRs.Fields("distinguishedName")
            strprimaryGroupID    = adoRs.Fields("primaryGroupID")
            
            GroupMembershipDB.Filter = "memberDistinguishedname = '" & strdistinguishedName & "' OR PrimaryGroupToken = '" & strprimaryGroupID & "'"
            GroupMembershipDB.Sort   = "SAMAccountName"
            
            if GroupMembershipDB.RecordCount > 0 then
                GroupMembershipDB.MoveFirst
                Do Until GroupMembershipDB.EOF
                    strType    = GroupMembershipDB.Fields.Item("type").Value
                    strNTName  = GroupMembershipDB.Fields.Item("samaccountname").Value
                    strPrimary = GroupMembershipDB.Fields.Item("PrimaryGroupToken").Value
                    
                    if strType = "Security Group" then
                        if strLastCNSec <> strCN then
                            objSec.WriteLine vbCRLF & strCN
                        end if
                        objSec.WriteLine vbTab & strNTName
                        strLastCNSec = strCN
                    else
                        if strLastCNDist <> strCN then
                            objDist.WriteLine vbCRLF & strCN
                        end if
                        objDist.WriteLine vbTab & strNTName
                        strLastCNDist = strCN
                    end if
                    
                    GroupMembershipDB.MoveNext
                Loop
            end if
            adoRS.MoveNext
        Loop
    End If
    adors.Close
    Set adoRS = Nothing
    adoCon.Close
    Set adoCon = Nothing
    objDist.Close
    objSec.Close
End Sub
 
wscript.echo "Done"

Open in new window

Avatar of bsharath
bsharath
Flag of India image

ASKER

I get this...
---------------------------
Windows Script Host
---------------------------
Script:      C:\Find each user in OU for groups.vbs
Line:      146
Char:      5
Error:      Item cannot be found in the collection corresponding to the requested name or ordinal.
Code:      800A0CC1
Source:       ADODB.Connection

---------------------------
OK  
---------------------------

Avatar of bsharath
bsharath
Flag of India image

ASKER

I get this...
---------------------------
Windows Script Host
---------------------------
Script:      C:\Find each user in OU for groups.vbs
Line:      146
Char:      5
Error:      Item cannot be found in the collection corresponding to the requested name or ordinal.
Code:      800A0CC1
Source:       ADODB.Connection

---------------------------
OK  
---------------------------

Avatar of RobSampson
RobSampson
Flag of Australia image
Hmmm, OK, comment out line 146, which is this:
    adoCon.Properties("Page Size") = 1000

and see if you get any further....

Rob.
Avatar of bsharath
bsharath
Flag of India image

ASKER

Now it works but still still "R" letter
Avatar of bsharath
bsharath
Flag of India image

ASKER

Now it works but still still "R" letter
Avatar of rejoinder
rejoinder
Flag of Canada image
Can you change the line;
adoCon.Properties("Page Size") = 1000

to read;
adoCon.Properties("Page Size") = 4000

I don't recall if this will work but I think we should give it a try anyway.
Avatar of bsharath
bsharath
Flag of India image

ASKER

I get this

---------------------------
Windows Script Host
---------------------------
Script:      C:\Find each user in OU for groups.vbs
Line:      146
Char:      1
Error:      Item cannot be found in the collection corresponding to the requested name or ordinal.
Code:      800A0CC1
Source:       ADODB.Connection

---------------------------
OK  
---------------------------
Avatar of bsharath
bsharath
Flag of India image

ASKER

I get this

---------------------------
Windows Script Host
---------------------------
Script:      C:\Find each user in OU for groups.vbs
Line:      146
Char:      1
Error:      Item cannot be found in the collection corresponding to the requested name or ordinal.
Code:      800A0CC1
Source:       ADODB.Connection

---------------------------
OK  
---------------------------
Avatar of rejoinder
rejoinder
Flag of Canada image
How about;
adoCon.Properties("Page Size") = 100
Avatar of bsharath
bsharath
Flag of India image

ASKER

Still get the same error...
Avatar of bsharath
bsharath
Flag of India image

ASKER

Still get the same error...
SOLUTION
Avatar of RobSampson
RobSampson
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bsharath
bsharath
Flag of India image

ASKER

Thank you worked perfect... What was the change?

Please suggest about the points....
Avatar of RobSampson
RobSampson
Flag of Australia image
OK, the code is all rejoinder's, so give most points to there.

An example of the change I made, was to have the script support the Page Size property, by executint the query with an ADODB.Command object instead of the ADODB.Connection object.

That way, Active Directory returns the data in chunks, and goes beyond the 1000 record limit.

'===============FROM THIS====================
    set objRootDSE   = GetObject("LDAP://RootDSE")
    strBase          = "<LDAP://cn=Partitions," & objRootDSE.Get("ConfigurationNamingContext") & ">;"
    strFilter        = "(&(objectcategory=crossRef)(systemFlags=3));"
    strAttrs         = "name,trustParent,nCName,dnsRoot,distinguishedName;"
    strScope         = "onelevel"
    set objConn      = CreateObject("ADODB.Connection")
    objConn.Provider = "ADsDSOObject"
    objConn.Open "Active Directory Provider"
    set objRS = objConn.Execute(strBase & strFilter & strAttrs & strScope)
============================================

'=================TO THIS====================
      Const ADS_SCOPE_ONELEVEL = 1
      Const ADS_SCOPE_SUBTREE = 2
    set objRootDSE   = GetObject("LDAP://RootDSE")
    Set objCommand =   CreateObject("ADODB.Command")
    set objConn      = CreateObject("ADODB.Connection")
    objConn.Provider = "ADsDSOObject"
   
    objConn.Open "Active Directory Provider"
    Set objCommand.ActiveConnection = objConn
    objCommand.Properties("Page Size") = 1000
      objCommand.Properties("Searchscope") = ADS_SCOPE_ONELEVEL
   
    objCommand.CommandText = "SELECT name,trustParent,nCName,dnsRoot,distinguishedName FROM 'LDAP://cn=Partitions," & objRootDSE.Get("ConfigurationNamingContext") & "'" & _
          " WHERE objectcategory='crossRef' AND systemFlags=3"
    Set objRS = objCommand.Execute
============================================


Regards,

Rob.
Avatar of bsharath
bsharath
Flag of India image

ASKER

Thank you both... :-))