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

asked on

Find just the users without a mailbox in all groups in the domain and list the name and group name in a csv file.

Hi,

Find just the users without a mailbox in all groups in the domain and list the name and group name in a csv file.
So i can validate the requirment for this and remove unwanted users.

Regards
Sharath
Avatar of exx1976
exx1976
Flag of United States of America image

Finding all the users without email is easy enough, but what do you mean by list name and "Group name"?  What group name?  What group?
Avatar of bsharath

ASKER

There are times when we end up adding users into distribution groups without a Mailbox. Which is of no use. Users without mailbox who are in groups as Distribution is not required as they dont receive any mails.

I want to find users as such with the group names they are in. To validate before removing them...
There are times when we end up adding users into distribution groups without a Mailbox. Which is of no use. Users without mailbox who are in groups as Distribution is not required as they dont receive any mails.

I want to find users as such with the group names they are in. To validate before removing them...
Avatar of AmazingTech
AmazingTech

OK. Try this one out.

OU of users without email address. Check all groups and nested groups display only when a group or nested group is a distribution group.
Const ADS_GROUP_TYPE_LOCAL_GROUP = &h4
Const ADS_GROUP_TYPE_GLOBAL_GROUP = &h2
Const ADS_GROUP_TYPE_UNIVERSAL_GROUP = &h8
Const ADS_GROUP_TYPE_SECURITY_ENABLED = &h80000000
 
Const E_ADS_PROPERTY_NOT_FOUND = &H8000500D
Const ForWriting = 2
CSVFile = "C:\OUUserGroupMembershipNoEmail.csv"
OUToSearch = "cn=users,dc=domain,dc=com"
 
On Error Resume Next
 
Set objDict = CreateObject("Scripting.Dictionary")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objCSVFile = objFSO.OpenTextFile(CSVFile, ForWriting, True)
 
Set objOU = GetObject _
    ("LDAP://" + OUToSearch)
  
objOU.Filter = Array("User")
 
 
For Each objOUUser In objOU
If objOUUser.Mail = "" Then
    strUserName = objOUUser.sAMAccountName
    arrMembersOf = objOUUser.GetEx("memberOf")
  
    If Err.Number <>  E_ADS_PROPERTY_NOT_FOUND Then
        For Each strMemberOf in arrMembersOf
            WScript.Echo vbTab & strMemberOf
 
            Set objMemberOf = GetObject _
                ("LDAP://" & strMemberOf)
            objMemberOf.GetInfo
 
            strGroupList = UserNestedGroup(strMemberOf, chr(34) + objMemberOf.sAMAccountName + chr(34), FALSE)
        Next
    Else
        WScript.Echo vbTab & "memberOf attribute is not set"
        Err.Clear
    End If
    Wscript.Echo     
end if
Next
 
For Each User In objDict.Keys
    arrMultipleGroups = split(objDict(User),"#")
    For Each GroupMembership in arrMultipleGroups
        objCSVFile.WriteLine Chr(34) & User & Chr(34) & "," & GroupMembership
    Next
Next
 
Set objFSO = Nothing
objCSVFile.Close
 
wscript.echo
 
Function UserNestedGroup(strGroup, ParentGroup, DistributionGroup)
On Error Resume Next
    Set objGroup = GetObject _
        ("LDAP://" & strGroup)
    objGroup.GetInfo
 
    select case objgroup.grouptype
        Case ADS_GROUP_TYPE_LOCAL_GROUP
            wscript.echo "Domain Local Distribution Group"
            DistributionGroup = TRUE
        case ADS_GROUP_TYPE_LOCAL_GROUP Or ADS_GROUP_TYPE_SECURITY_ENABLED
            wscript.echo "Domain Local Security Group"
        case ADS_GROUP_TYPE_GLOBAL_GROUP
            wscript.echo "Domain Global Distribution Group"
            DistributionGroup = TRUE
        case ADS_GROUP_TYPE_GLOBAL_GROUP Or ADS_GROUP_TYPE_SECURITY_ENABLED
            wscript.echo "Domain Global Security Group"
        case ADS_GROUP_TYPE_UNIVERSAL_GROUP
            wscript.echo "Domain Universal Distribution Group"
            DistributionGroup = TRUE
        case ADS_GROUP_TYPE_UNIVERSAL_GROUP Or ADS_GROUP_TYPE_SECURITY_ENABLED
            wscript.echo "Domain Universal Security Group"
    end select
 
    PGroup = objGroup.sAMAccountName
    arrGroupMembersOf = objGroup.GetEx("memberOf")
 
    If Err.Number <> E_ADS_PROPERTY_NOT_FOUND Then
        PG = ParentGroup
 
        For Each strMemberOf In arrGroupMembersOf
            ParentGroup = PG
 
            Set objMemberOf = GetObject _
                ("LDAP://" & strMemberOf)
            objMemberOf.GetInfo
 
            If Instr(1,LookForGroup,objMemberOf.CN,vbtextcompare) <> 0 Then DistributionGroup = TRUE
 
            IF instr(1, ParentGroup, PGroup, vbtextcompare) <> 0 then
                ParentGroup = UserNestedGroup(strMemberOf, ParentGroup, DistributionGroup)
            ELSE
                ParentGroup = UserNestedGroup(strMemberOf, ParentGroup + "," + Chr(34) & PGroup & Chr(34), DistributionGroup)
            End If
        Next
    Else
        IF instr(1, ParentGroup, PGroup, vbtextcompare) = 0 then ParentGroup = ParentGroup + "," + Chr(34) & PGroup & Chr(34)
        If DistributionGroup Then
            If objDict.Exists(strUserName) Then
                objDict.Item(strUserName) = ParentGroup & "#" & objDict.Item(strUserName)
            Else
                objDict.Add strUserName, ParentGroup
            End If
        End If
    End If
    UserNestedGroup = ParentGroup
 
End Function

Open in new window

AT
Is this the only line i need to change
OUToSearch = "cn=users,dc=domain,dc=com"

Show the path where all users are there?
Like
OUToSearch = "OU=countries,Ou=India,dc=domain,dc=com"
AT
Is this the only line i need to change
OUToSearch = "cn=users,dc=domain,dc=com"

Show the path where all users are there?
Like
OUToSearch = "OU=countries,Ou=India,dc=domain,dc=com"
When i run just with that change no error but just the csv file is created empty...
OK. Let's see all users with distribution groups.

Const ADS_GROUP_TYPE_LOCAL_GROUP = &h4
Const ADS_GROUP_TYPE_GLOBAL_GROUP = &h2
Const ADS_GROUP_TYPE_UNIVERSAL_GROUP = &h8
Const ADS_GROUP_TYPE_SECURITY_ENABLED = &h80000000
 
Const E_ADS_PROPERTY_NOT_FOUND = &H8000500D
Const ForWriting = 2
CSVFile = "C:\OUUserGroupMembershipNoEmail.csv"
OUToSearch = "OU=countries,Ou=India,dc=domain,dc=com"
 
On Error Resume Next
 
Set objDict = CreateObject("Scripting.Dictionary")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objCSVFile = objFSO.OpenTextFile(CSVFile, ForWriting, True)
 
Set objOU = GetObject _
    ("LDAP://" + OUToSearch)
  
objOU.Filter = Array("User")
 
 
For Each objOUUser In objOU
'If objOUUser.Mail = "" Then
    strUserName = objOUUser.sAMAccountName
    arrMembersOf = objOUUser.GetEx("memberOf")
  
    If Err.Number <>  E_ADS_PROPERTY_NOT_FOUND Then
        For Each strMemberOf in arrMembersOf
            WScript.Echo vbTab & strMemberOf
 
            Set objMemberOf = GetObject _
                ("LDAP://" & strMemberOf)
            objMemberOf.GetInfo
 
            strGroupList = UserNestedGroup(strMemberOf, chr(34) + objMemberOf.sAMAccountName + chr(34), FALSE)
        Next
    Else
        WScript.Echo vbTab & "memberOf attribute is not set"
        Err.Clear
    End If
    Wscript.Echo     
'end if
Next
 
For Each User In objDict.Keys
    arrMultipleGroups = split(objDict(User),"#")
    For Each GroupMembership in arrMultipleGroups
        objCSVFile.WriteLine Chr(34) & User & Chr(34) & "," & GroupMembership
    Next
Next
 
Set objFSO = Nothing
objCSVFile.Close
 
wscript.echo
 
Function UserNestedGroup(strGroup, ParentGroup, DistributionGroup)
On Error Resume Next
    Set objGroup = GetObject _
        ("LDAP://" & strGroup)
    objGroup.GetInfo
 
    select case objgroup.grouptype
        Case ADS_GROUP_TYPE_LOCAL_GROUP
            wscript.echo "Domain Local Distribution Group"
            DistributionGroup = TRUE
        case ADS_GROUP_TYPE_LOCAL_GROUP Or ADS_GROUP_TYPE_SECURITY_ENABLED
            wscript.echo "Domain Local Security Group"
        case ADS_GROUP_TYPE_GLOBAL_GROUP
            wscript.echo "Domain Global Distribution Group"
            DistributionGroup = TRUE
        case ADS_GROUP_TYPE_GLOBAL_GROUP Or ADS_GROUP_TYPE_SECURITY_ENABLED
            wscript.echo "Domain Global Security Group"
        case ADS_GROUP_TYPE_UNIVERSAL_GROUP
            wscript.echo "Domain Universal Distribution Group"
            DistributionGroup = TRUE
        case ADS_GROUP_TYPE_UNIVERSAL_GROUP Or ADS_GROUP_TYPE_SECURITY_ENABLED
            wscript.echo "Domain Universal Security Group"
    end select
 
    PGroup = objGroup.sAMAccountName
    arrGroupMembersOf = objGroup.GetEx("memberOf")
 
    If Err.Number <> E_ADS_PROPERTY_NOT_FOUND Then
        PG = ParentGroup
 
        For Each strMemberOf In arrGroupMembersOf
            ParentGroup = PG
 
            Set objMemberOf = GetObject _
                ("LDAP://" & strMemberOf)
            objMemberOf.GetInfo
 
            If Instr(1,LookForGroup,objMemberOf.CN,vbtextcompare) <> 0 Then DistributionGroup = TRUE
 
            IF instr(1, ParentGroup, PGroup, vbtextcompare) <> 0 then
                ParentGroup = UserNestedGroup(strMemberOf, ParentGroup, DistributionGroup)
            ELSE
                ParentGroup = UserNestedGroup(strMemberOf, ParentGroup + "," + Chr(34) & PGroup & Chr(34), DistributionGroup)
            End If
        Next
    Else
        IF instr(1, ParentGroup, PGroup, vbtextcompare) = 0 then ParentGroup = ParentGroup + "," + Chr(34) & PGroup & Chr(34)
        If DistributionGroup Then
            If objDict.Exists(strUserName) Then
                objDict.Item(strUserName) = ParentGroup & "#" & objDict.Item(strUserName)
            Else
                objDict.Add strUserName, ParentGroup
            End If
        End If
    End If
    UserNestedGroup = ParentGroup
 
End Function

Open in new window

After a long query time i get 3 blank boxes without any message and it closed. No data in the created csv
After a long query time i get 3 blank boxes without any message and it closed. No data in the created csv
OK. Let's see if the groups are coming in.

I also see 1 other line that can be deleted.

Does the screen show anything when you run this?
Const ADS_GROUP_TYPE_LOCAL_GROUP = &h4
Const ADS_GROUP_TYPE_GLOBAL_GROUP = &h2
Const ADS_GROUP_TYPE_UNIVERSAL_GROUP = &h8
Const ADS_GROUP_TYPE_SECURITY_ENABLED = &h80000000
 
Const E_ADS_PROPERTY_NOT_FOUND = &H8000500D
Const ForWriting = 2
CSVFile = "C:\OUUserGroupMembershipNoEmail.csv"
OUToSearch = "OU=countries,Ou=India,dc=domain,dc=com"
 
On Error Resume Next
 
Set objDict = CreateObject("Scripting.Dictionary")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objCSVFile = objFSO.OpenTextFile(CSVFile, ForWriting, True)
 
Set objOU = GetObject _
    ("LDAP://" + OUToSearch)
  
objOU.Filter = Array("User")
 
 
For Each objOUUser In objOU
'If objOUUser.Mail = "" Then
    strUserName = objOUUser.sAMAccountName
    arrMembersOf = objOUUser.GetEx("memberOf")
  
    If Err.Number <>  E_ADS_PROPERTY_NOT_FOUND Then
        For Each strMemberOf in arrMembersOf
            WScript.Echo vbTab & strMemberOf
 
            Set objMemberOf = GetObject _
                ("LDAP://" & strMemberOf)
            objMemberOf.GetInfo
 
            strGroupList = UserNestedGroup(strMemberOf, chr(34) + objMemberOf.sAMAccountName + chr(34), TRUE)
        Next
    Else
        WScript.Echo vbTab & "memberOf attribute is not set"
        Err.Clear
    End If
    Wscript.Echo     
'end if
Next
 
For Each User In objDict.Keys
    arrMultipleGroups = split(objDict(User),"#")
    For Each GroupMembership in arrMultipleGroups
        objCSVFile.WriteLine Chr(34) & User & Chr(34) & "," & GroupMembership
    Next
Next
 
Set objFSO = Nothing
objCSVFile.Close
 
wscript.echo
 
Function UserNestedGroup(strGroup, ParentGroup, DistributionGroup)
On Error Resume Next
    Set objGroup = GetObject _
        ("LDAP://" & strGroup)
    objGroup.GetInfo
 
    select case objgroup.grouptype
        Case ADS_GROUP_TYPE_LOCAL_GROUP
            wscript.echo "Domain Local Distribution Group"
            DistributionGroup = TRUE
        case ADS_GROUP_TYPE_LOCAL_GROUP Or ADS_GROUP_TYPE_SECURITY_ENABLED
            wscript.echo "Domain Local Security Group"
        case ADS_GROUP_TYPE_GLOBAL_GROUP
            wscript.echo "Domain Global Distribution Group"
            DistributionGroup = TRUE
        case ADS_GROUP_TYPE_GLOBAL_GROUP Or ADS_GROUP_TYPE_SECURITY_ENABLED
            wscript.echo "Domain Global Security Group"
        case ADS_GROUP_TYPE_UNIVERSAL_GROUP
            wscript.echo "Domain Universal Distribution Group"
            DistributionGroup = TRUE
        case ADS_GROUP_TYPE_UNIVERSAL_GROUP Or ADS_GROUP_TYPE_SECURITY_ENABLED
            wscript.echo "Domain Universal Security Group"
    end select
 
    PGroup = objGroup.sAMAccountName
    arrGroupMembersOf = objGroup.GetEx("memberOf")
 
    If Err.Number <> E_ADS_PROPERTY_NOT_FOUND Then
        PG = ParentGroup
 
        For Each strMemberOf In arrGroupMembersOf
            ParentGroup = PG
 
            Set objMemberOf = GetObject _
                ("LDAP://" & strMemberOf)
            objMemberOf.GetInfo
 
            IF instr(1, ParentGroup, PGroup, vbtextcompare) <> 0 then
                ParentGroup = UserNestedGroup(strMemberOf, ParentGroup, DistributionGroup)
            ELSE
                ParentGroup = UserNestedGroup(strMemberOf, ParentGroup + "," + Chr(34) & PGroup & Chr(34), DistributionGroup)
            End If
        Next
    Else
        IF instr(1, ParentGroup, PGroup, vbtextcompare) = 0 then ParentGroup = ParentGroup + "," + Chr(34) & PGroup & Chr(34)
        If DistributionGroup Then
            If objDict.Exists(strUserName) Then
                objDict.Item(strUserName) = ParentGroup & "#" & objDict.Item(strUserName)
            Else
                objDict.Add strUserName, ParentGroup
            End If
        End If
    End If
    UserNestedGroup = ParentGroup
 
End Function

Open in new window

Still the same AT 3 blank boxes and no data within the csv...
Still the same AT 3 blank boxes and no data within the csv...
Is this the proper OU?

OUToSearch = "OU=countries,Ou=India,dc=domain,dc=com"

Or should it be this?

OUToSearch = "OU=India,OU=countries,dc=domain,dc=com"

And your domain is domain.com?
I tried in both ways still no results...

When i go to the OU Properties > Objects i get this

Development.Group.co.uk/Countries/IND/User Accounts/Named Accounts
Now how should i mention this path.
Its a User create OU structure...
I tried in both ways still no results...

When i go to the OU Properties > Objects i get this

Development.Group.co.uk/Countries/IND/User Accounts/Named Accounts
Now how should i mention this path.
Its a User create OU structure...
Hmm...

Try this.

OUToSearch = "OU=Named Accounts,OU=User Accounts,OU=Ind,OU=countries,dc=Development,dc=Group,dc=co,dc=uk"
For many i get this

---------------------------
Windows Script Host
---------------------------
      memberOf attribute is not set
---------------------------
OK  
---------------------------

But data is into the csv...

What does the Colum A,B,C data mean...
For many i get this

---------------------------
Windows Script Host
---------------------------
      memberOf attribute is not set
---------------------------
OK  
---------------------------

But data is into the csv...

What does the Colum A,B,C data mean...
Yes. That is normal. When a group is not nested into another group I'm echoing this message. I could remove it.
Can you remove it and what are the data in A,B,C colums
A = Username
B = User has direct membership
C = B is nested into here
D = C is nested into here

Any column B to end can be the distribution group.
Const ADS_GROUP_TYPE_LOCAL_GROUP = &h4
Const ADS_GROUP_TYPE_GLOBAL_GROUP = &h2
Const ADS_GROUP_TYPE_UNIVERSAL_GROUP = &h8
Const ADS_GROUP_TYPE_SECURITY_ENABLED = &h80000000
 
Const E_ADS_PROPERTY_NOT_FOUND = &H8000500D
Const ForWriting = 2
CSVFile = "C:\OUUserGroupMembershipNoEmail.csv"
OUToSearch = "OU=Named Accounts,OU=User Accounts,OU=Ind,OU=countries,dc=Development,dc=Group,dc=co,dc=uk"
 
On Error Resume Next
 
Set objDict = CreateObject("Scripting.Dictionary")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objCSVFile = objFSO.OpenTextFile(CSVFile, ForWriting, True)
 
Set objOU = GetObject _
    ("LDAP://" + OUToSearch)
  
objOU.Filter = Array("User")
 
 
For Each objOUUser In objOU
If objOUUser.Mail = "" Then
    strUserName = objOUUser.sAMAccountName
    arrMembersOf = objOUUser.GetEx("memberOf")
  
    If Err.Number <>  E_ADS_PROPERTY_NOT_FOUND Then
        For Each strMemberOf in arrMembersOf
            WScript.Echo vbTab & strMemberOf
 
            Set objMemberOf = GetObject _
                ("LDAP://" & strMemberOf)
            objMemberOf.GetInfo
 
            strGroupList = UserNestedGroup(strMemberOf, chr(34) + objMemberOf.sAMAccountName + chr(34), FALSE)
        Next
    Else
        Err.Clear
    End If
    Wscript.Echo     
end if
Next
 
For Each User In objDict.Keys
    arrMultipleGroups = split(objDict(User),"#")
    For Each GroupMembership in arrMultipleGroups
        objCSVFile.WriteLine Chr(34) & User & Chr(34) & "," & GroupMembership
    Next
Next
 
Set objFSO = Nothing
objCSVFile.Close
 
wscript.echo
 
Function UserNestedGroup(strGroup, ParentGroup, DistributionGroup)
On Error Resume Next
    Set objGroup = GetObject _
        ("LDAP://" & strGroup)
    objGroup.GetInfo
 
    select case objgroup.grouptype
        Case ADS_GROUP_TYPE_LOCAL_GROUP
            wscript.echo "Domain Local Distribution Group"
            DistributionGroup = TRUE
        case ADS_GROUP_TYPE_LOCAL_GROUP Or ADS_GROUP_TYPE_SECURITY_ENABLED
            wscript.echo "Domain Local Security Group"
        case ADS_GROUP_TYPE_GLOBAL_GROUP
            wscript.echo "Domain Global Distribution Group"
            DistributionGroup = TRUE
        case ADS_GROUP_TYPE_GLOBAL_GROUP Or ADS_GROUP_TYPE_SECURITY_ENABLED
            wscript.echo "Domain Global Security Group"
        case ADS_GROUP_TYPE_UNIVERSAL_GROUP
            wscript.echo "Domain Universal Distribution Group"
            DistributionGroup = TRUE
        case ADS_GROUP_TYPE_UNIVERSAL_GROUP Or ADS_GROUP_TYPE_SECURITY_ENABLED
            wscript.echo "Domain Universal Security Group"
    end select
 
    PGroup = objGroup.sAMAccountName
    arrGroupMembersOf = objGroup.GetEx("memberOf")
 
    If Err.Number <> E_ADS_PROPERTY_NOT_FOUND Then
        PG = ParentGroup
 
        For Each strMemberOf In arrGroupMembersOf
            ParentGroup = PG
 
            Set objMemberOf = GetObject _
                ("LDAP://" & strMemberOf)
            objMemberOf.GetInfo
 
            If Instr(1,LookForGroup,objMemberOf.CN,vbtextcompare) <> 0 Then DistributionGroup = TRUE
 
            IF instr(1, ParentGroup, PGroup, vbtextcompare) <> 0 then
                ParentGroup = UserNestedGroup(strMemberOf, ParentGroup, DistributionGroup)
            ELSE
                ParentGroup = UserNestedGroup(strMemberOf, ParentGroup + "," + Chr(34) & PGroup & Chr(34), DistributionGroup)
            End If
        Next
    Else
        IF instr(1, ParentGroup, PGroup, vbtextcompare) = 0 then ParentGroup = ParentGroup + "," + Chr(34) & PGroup & Chr(34)
        If DistributionGroup Then
            If objDict.Exists(strUserName) Then
                objDict.Item(strUserName) = ParentGroup & "#" & objDict.Item(strUserName)
            Else
                objDict.Add strUserName, ParentGroup
            End If
        End If
    End If
    UserNestedGroup = ParentGroup
 
End Function

Open in new window

AT isn't this script to check if each user in the group has a mailbox or not?
And get just names of users who are in the group or nested group?

Is this for some other Q... of mine?
AT isn't this script to check if each user in the group has a mailbox or not?
And get just names of users who are in the group or nested group?

Is this for some other Q... of mine?
Yours. I was checking for the E-mail attribute was blank. I'm not too sure how to check if they have an exchange box.

If objOUUser.Mail = "" Then
Ok in this case can we show a OU that has USers. As we can check an OU that has users with E-Mail attribute not a mailbox...

Any help with the other posts?
You know what I've been trying to do the other post for a couple days now. I've been trying to reuse some functions from other posts I've done for getting nesting groups and it's not working like I want to. I think I'm going to scrap trying to reuse the code and create the function from scratch because I'm getting so confused with it
Any view on this AT
Sorry with the MS KB958644 I've been really busy at work trying to patch everything.
Any help on this...
ASKER CERTIFIED SOLUTION
Avatar of AmazingTech
AmazingTech

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
Thanks a lot AT worked perfect...