We help IT Professionals succeed at work.

Cleaning up AD script

chandru_sol
chandru_sol asked
on
662 Views
Last Modified: 2008-01-09
Hi,

Can you please help with the script for the below?

1. Run on specific OU
2. Check for disabled accounts older than 6 months and log information with username, DL membership, Account hidden property, Receive email property, all to a csv file with Date and OU name as filename.
3. Remove all the disabled accounts from Distribution list
4. Set to receive mails only from Authenticated users
5. Enable Account hidden from GAL
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Wow, two very similar questions within 10 minutes.....

This will give you a bit of a start for Steps 1 and 2....I cannot do 3, 4, and 5 because I don't use Exchange.....I think you are more familiar with the Exchange scripting integration than I am....

Modify the strOU path (specifiying the path in reverse order), and then run it.

'=====================
If LCase(Right(Wscript.FullName, 11)) = "wscript.exe" Then
    strPath = Wscript.ScriptFullName
    strCommand = "%comspec% /k cscript  """ & strPath & """"
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run(strCommand), 1, True
    Wscript.Quit
End If

Const ADS_UF_ACCOUNTDISABLE = 2

strOUPath = "ou=users,ou=Civic Centre,ou=Sites,"

Set objRootDSE = GetObject("LDAP://RootDSE")
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
objCommand.CommandText = _
    "<GC://" & strOUPath & objRootDSE.Get("defaultNamingContext") & ">;(objectCategory=User)" & _
        ";userAccountControl,distinguishedName;subtree"  
Set objRecordSet = objCommand.Execute
 
intCounterDisabled = 0
intCounterEnabled = 0
intAlreadyExpired = 0
intExpiredWithin60Days = 0
intDisabledWithin60Days = 0
intNeverExpire = 0

Do Until objRecordset.EOF
    intUAC=objRecordset.Fields("userAccountControl")
    Set objUser = GetObject("LDAP://" & objRecordset.Fields("distinguishedName"))
    accountExpires = objUser.AccountExpirationDate
      If accountExpires = "1/1/1970" Or accountExpires = "1/01/1601 10:00:00 AM" Or Err.Number = -2147467259 Then
            intNeverExpire = intNeverExpire + 1
      ElseIf CDate(accountExpires) < Now Then
            If DateDiff("d",CDate(accountExpires),Now) < 60 Then
                  intExpiredWithin60Days = intExpiredWithin60Days + 1
            Else
                intAlreadyExpired = intAlreadyExpired + 1
            End IF
      End If
    If intUAC And ADS_UF_ACCOUNTDISABLE Then
        WScript.echo objRecordset.Fields("distinguishedName") & " is disabled"
            On Error Resume Next
            whenChanged = objUser.whenChanged
            If Err.Number = 0 Then
                  On Error GoTo 0
              If DateDiff("d",CDate(whenChanged),Now) < 60 Then
                  intDisabledWithin60Days = intDisabledWithin60Days + 1
              Else
                    intCounterDisabled = intCounterDisabled + 1
              End If
          Else
                Err.Clear
                On Error GoTo 0
                intCounterDisabled = intCounterDisabled + 1
          End If
      Else
      WScript.echo objRecordset.Fields("distinguishedName") & " is enabled"
       intCounterEnabled = intCounterEnabled + 1              
    End If
    objRecordset.MoveNext
Loop

 
WScript.Echo VbCrLf & "A total of " & intCounterDisabled + intDisabledWithin60Days & " accounts are disabled."
WScript.Echo VbCrLf & "A total of " & intDisabledWithin60Days & " accounts have been disabled within the last 60 days."
WScript.Echo VbCrLf & "A total of " & intCounterDisabled & " accounts were disabled over 60 days ago."
WScript.Echo VbCrLf & "A total of " & intCounterEnabled & " accounts are enabled."
WScript.Echo VbCrLf & "A total of " & intAlreadyExpired & " accounts have already expired."
WScript.Echo VbCrLf & "A total of " & intExpiredWithin60Days & " accounts have expired within the last 60 days."
WScript.Echo VbCrLf & "A total of " & intNeverExpire & " accounts are not set to expire."
'=====================

Regards,

Rob.

Author

Commented:
Hi Rob,

This is showing only the DN for the users who are disabled. Can we have the information like username, DL membership, Account hidden property, Receive email property put in the Csv file?

regards
Chandru
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Chandru, please advise where I could find the DL Membership in AD (or what that is), and where I find the "account hidden" property?

In the meantime, this will output to CSV:
'=====================
If LCase(Right(Wscript.FullName, 11)) = "wscript.exe" Then
    strPath = Wscript.ScriptFullName
    strCommand = "%comspec% /k cscript  """ & strPath & """"
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run(strCommand), 1, True
    Wscript.Quit
End If

Const ADS_UF_ACCOUNTDISABLE = 2
strOutputFile = "User_Disabled_Status.csv"

strOUPath = "ou=users,ou=Civic Centre,ou=Sites,"

Set objRootDSE = GetObject("LDAP://RootDSE")
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
objCommand.CommandText = _
    "<GC://" & strOUPath & objRootDSE.Get("defaultNamingContext") & ">;(objectCategory=User)" & _
        ";userAccountControl,distinguishedName;subtree"  
Set objRecordSet = objCommand.Execute
 
intCounterDisabled = 0
intCounterEnabled = 0
intAlreadyExpired = 0
intExpiredWithin60Days = 0
intDisabledWithin60Days = 0
intNeverExpire = 0

strDetails = """User Name"",""Expired <= 60 Days Ago"",""Expired > 60 Days Ago"",""Will Never Expire"",""Enabled"",""Disabled"",""Disabled <= 60 Days Ago"",""Disabled > 60 Days Ago"""
Do Until objRecordset.EOF
    intUAC=objRecordset.Fields("userAccountControl")
    Set objUser = GetObject("LDAP://" & objRecordset.Fields("distinguishedName"))
    strDetails = strDetails & VbCrLf & """" & objUser.DisplayName & ""","
    accountExpires = objUser.AccountExpirationDate
      If accountExpires = "1/1/1970" Or accountExpires = "1/01/1601 10:00:00 AM" Or Err.Number = -2147467259 Then
            intNeverExpire = intNeverExpire + 1
            strDetails = strDetails & """"","""",""YES"","
      ElseIf CDate(accountExpires) < Now Then
            If DateDiff("d",CDate(accountExpires),Now) < 60 Then
                        intExpiredWithin60Days = intExpiredWithin60Days + 1
                        strDetails = strDetails & """YES"","""","""","
            Else
                        intAlreadyExpired = intAlreadyExpired + 1
                        strDetails = strDetails & """"",""YES"","""","
            End IF
      End If
    If intUAC And ADS_UF_ACCOUNTDISABLE Then
        WScript.echo objRecordset.Fields("distinguishedName") & " is disabled"
        strDetails = strDetails & """"",""YES"","
            On Error Resume Next
            whenChanged = objUser.whenChanged
            If Err.Number = 0 Then
                  On Error GoTo 0
              If DateDiff("d",CDate(whenChanged),Now) < 60 Then
                  intDisabledWithin60Days = intDisabledWithin60Days + 1
                  strDetails = strDetails & """YES"","""""
              Else
                    intCounterDisabled = intCounterDisabled + 1
                    strDetails = strDetails & """"",""YES"""
              End If
          Else
                Err.Clear
                On Error GoTo 0
                intCounterDisabled = intCounterDisabled + 1
                strDetails = strDetails & """"",""YES"""
          End If
      Else
      WScript.echo objRecordset.Fields("distinguishedName") & " is enabled"
       intCounterEnabled = intCounterEnabled + 1
       strDetails = strDetails & """YES"","""","""","""""
    End If
    objRecordset.MoveNext
Loop

 
WScript.Echo VbCrLf & "A total of " & intCounterDisabled + intDisabledWithin60Days & " accounts are disabled."
WScript.Echo VbCrLf & "A total of " & intDisabledWithin60Days & " accounts have been disabled within the last 60 days."
WScript.Echo VbCrLf & "A total of " & intCounterDisabled & " accounts were disabled over 60 days ago."
WScript.Echo VbCrLf & "A total of " & intCounterEnabled & " accounts are enabled."
WScript.Echo VbCrLf & "A total of " & intAlreadyExpired & " accounts have already expired."
WScript.Echo VbCrLf & "A total of " & intExpiredWithin60Days & " accounts have expired within the last 60 days."
WScript.Echo VbCrLf & "A total of " & intNeverExpire & " accounts are not set to expire."

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)
objOutputFile.Write strDetails
objOutputFile.Close
Set objOutputFile = Nothing
Set objFSO = Nothing
'=====================

Regards,

Rob.

Author

Commented:
Hi Rob,

This is the attribute for the account hidden property - msExchHideFromAddressLists

I think you can use the member of to find the DL and the group type should be 8

regards
Chandru
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Chandru, I think this checks the Hidden property, in the last column, but I can't test it because I don't have exchange:
'=====================
If LCase(Right(Wscript.FullName, 11)) = "wscript.exe" Then
    strPath = Wscript.ScriptFullName
    strCommand = "%comspec% /k cscript  """ & strPath & """"
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run(strCommand), 1, True
    Wscript.Quit
End If

Const ADS_UF_ACCOUNTDISABLE = 2
strOutputFile = "User_Disabled_Status.csv"

strOUPath = "ou=users,ou=Civic Centre,ou=Sites,"

Set objRootDSE = GetObject("LDAP://RootDSE")
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
objCommand.CommandText = _
    "<GC://" & strOUPath & objRootDSE.Get("defaultNamingContext") & ">;(objectCategory=User)" & _
        ";userAccountControl,distinguishedName;subtree"  
Set objRecordSet = objCommand.Execute
 
intCounterDisabled = 0
intCounterEnabled = 0
intAlreadyExpired = 0
intExpiredWithin60Days = 0
intDisabledWithin60Days = 0
intNeverExpire = 0

strDetails = """User Name"",""Expired <= 60 Days Ago"",""Expired > 60 Days Ago"",""Will Never Expire"",""Enabled"",""Disabled"",""Disabled <= 60 Days Ago"",""Disabled > 60 Days Ago"",""Hidden From GAL"""
Do Until objRecordset.EOF
    intUAC=objRecordset.Fields("userAccountControl")
    Set objUser = GetObject("LDAP://" & objRecordset.Fields("distinguishedName"))
    strDetails = strDetails & VbCrLf & """" & objUser.DisplayName & ""","
    accountExpires = objUser.AccountExpirationDate
      If accountExpires = "1/1/1970" Or accountExpires = "1/01/1601 10:00:00 AM" Or Err.Number = -2147467259 Then
            intNeverExpire = intNeverExpire + 1
            strDetails = strDetails & """"","""",""YES"","
      ElseIf CDate(accountExpires) < Now Then
            If DateDiff("d",CDate(accountExpires),Now) < 60 Then
                        intExpiredWithin60Days = intExpiredWithin60Days + 1
                        strDetails = strDetails & """YES"","""","""","
            Else
                        intAlreadyExpired = intAlreadyExpired + 1
                        strDetails = strDetails & """"",""YES"","""","
            End IF
      End If
    If intUAC And ADS_UF_ACCOUNTDISABLE Then
        WScript.echo objRecordset.Fields("distinguishedName") & " is disabled"
        strDetails = strDetails & """"",""YES"","
            On Error Resume Next
            whenChanged = objUser.whenChanged
            If Err.Number = 0 Then
                  On Error GoTo 0
              If DateDiff("d",CDate(whenChanged),Now) < 60 Then
                  intDisabledWithin60Days = intDisabledWithin60Days + 1
                  strDetails = strDetails & """YES"","""""
              Else
                    intCounterDisabled = intCounterDisabled + 1
                    strDetails = strDetails & """"",""YES"""
              End If
          Else
                Err.Clear
                On Error GoTo 0
                intCounterDisabled = intCounterDisabled + 1
                strDetails = strDetails & """"",""YES"""
          End If
      Else
      WScript.echo objRecordset.Fields("distinguishedName") & " is enabled"
       intCounterEnabled = intCounterEnabled + 1
       strDetails = strDetails & """YES"","""","""","""""
    End If
    strDetails = strDetails & ",""" & objUser.msExchHideFromAddressLists & """"
    objRecordset.MoveNext
Loop

 
WScript.Echo VbCrLf & "A total of " & intCounterDisabled + intDisabledWithin60Days & " accounts are disabled."
WScript.Echo VbCrLf & "A total of " & intDisabledWithin60Days & " accounts have been disabled within the last 60 days."
WScript.Echo VbCrLf & "A total of " & intCounterDisabled & " accounts were disabled over 60 days ago."
WScript.Echo VbCrLf & "A total of " & intCounterEnabled & " accounts are enabled."
WScript.Echo VbCrLf & "A total of " & intAlreadyExpired & " accounts have already expired."
WScript.Echo VbCrLf & "A total of " & intExpiredWithin60Days & " accounts have expired within the last 60 days."
WScript.Echo VbCrLf & "A total of " & intNeverExpire & " accounts are not set to expire."

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)
objOutputFile.Write strDetails
objOutputFile.Close
Set objOutputFile = Nothing
Set objFSO = Nothing
'=====================

And I think the code here should get the DL Memberships.....could you test that, and try to fit it in to the script?

http://www.microsoft.com/technet/scriptcenter/resources/officetips/may05/tips0524.mspx

Regards,

Rob.

Author

Commented:
Thanks Rob! I will test and post the results.

Can the script modified to check the lastlogin time and check the disabled number of days?

Check this link for the last logon time..........

http://www.rlmueller.net/Last%20Logon.htm (Good link for scripts iin AD aswell)

Author

Commented:
Rob,

This link is for the distribution list in outlook not in AD
http://www.microsoft.com/technet/scriptcenter/resources/officetips/may05/tips0524.mspx

I will post the code for finding the distribution list in AD
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Chandru, unfortunately the disabled number of days is not possible.  The only other attribute that may give you such data is the lastLogoff attribute.

Regarding
whenChanged vs lastLogon

this would depend on how things work in your environment, and / or what you'd be happy using....

The whenChanged will be inaccurate if the account was modified or moved long after it was disabled, but the lastLogon will be inaccurate if the account was not used for a while before it was disabled......

Maybe it's best to leave it as is, but change the headers from:
""Disabled <= 60 Days Ago"",""Disabled > 60 Days Ago"""

to
""Last Changed <= 60 Days Ago"",""Last Changed > 60 Days Ago"""

Obviously though, yes, lastLogon can be put in.....I may as well wait to try to get the DL membership together when you post that....

Regards,

Rob.

Author

Commented:
Rob,
Hope this link below will help to remove users from Distribtuion group....

http://gsexdev.blogspot.com/2006/04/removing-disabled-users-from.html

regards
Chandru
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Yeah, that should help.  That script has a "Display" mode and a "Remove" mode.  We would only want to list those groups I think.....which can be done....

Regards,

Rob.

Author

Commented:
Thanks Rob! Can you please let me know which code should be modified to display the groups?

Author

Commented:
Hi Rob,

Did you get a change to work on this?

Author

Commented:
Hi Rob,

Can you help me in giving a start ahead?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi Chandru, run this and see if it lists the DL Group name and the disabled user name:

'===============
If LCase(Right(Wscript.FullName, 11)) = "wscript.exe" Then
    strPath = Wscript.ScriptFullName
    strCommand = "%comspec% /k cscript  """ & strPath & """"
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run(strCommand), 1, True
    Wscript.Quit
End If

'mode = "remove"
mode = "display"
wscript.echo
set conn = createobject("ADODB.Connection")
set com = createobject("ADODB.Command")
set conn1 = createobject("ADODB.Connection")
strConnString = "Data Provider=NONE; Provider=MSDataShape"
conn1.Open strConnString
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")
strDefaultNamingContext = iAdRootDSE.Get("defaultNamingContext")
set objParentRS = createobject("adodb.recordset")
set objChildRS = createobject("adodb.recordset")
strSQL = "SHAPE APPEND" & _
" NEW adVarChar(255) AS GRPDisplayName, " & _
" NEW adVarChar(255) AS GRPDN, " & _
" ((SHAPE APPEND " & _
" NEW adVarChar(255) AS USDisplayName, " & _
" NEW adVarChar(255) AS USDN, " & _
" NEW adVarChar(255) AS USGRPDisplayName, " & _
" NEW adVarChar(255) AS USGRPDN " & _
")" & _
" RELATE GRPDN TO USGRPDN) AS rsGRPUS " 
objParentRS.LockType = 3
objParentRS.Open strSQL, conn1
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
GALQueryFilter = "(&(mailnickname=*)(|(objectCategory=group)))"
strQuery = "<LDAP://" & strDefaultNamingContext & ">;" & GALQueryFilter & _
";distinguishedName,displayname,legacyExchangeDN,homemdb;subtree"
Com.ActiveConnection = Conn
Com.CommandText = strQuery
Set Rs = Com.Execute
while not rs.eof
      objParentRS.addnew
      objParentRS("GRPDisplayName") = rs.fields("displayname")
      objParentRS("GRPDN") = rs.fields("distinguishedName")
      objParentRS.update
      rs.movenext
wend
GALQueryFilter = _
"(&(&(mailnickname=*)(objectCategory=person)(userAccountControl:1.2.840.113556.1.4.803:=2)))"
strQuery = "<LDAP://" & strDefaultNamingContext & ">;" & GALQueryFilter & _
";distinguishedName,displayname,legacyExchangeDN,homemdb;subtree"
Com.ActiveConnection = Conn
Com.CommandText = strQuery
Set Rs1 = Com.Execute
Set objChildRS = objParentRS("rsGRPUS").Value
while not rs1.eof
      if instr(rs1.fields("displayname"),"SystemMailbox{") = 0 then
            set objuser = getobject("LDAP://" & rs1.fields("distinguishedName"))
            For each objgroup in objuser.groups
                  objChildRS.addnew
                  objChildRS("USDisplayName") = rs1.fields("displayname")
                  objChildRS("USDN") = rs1.fields("distinguishedName")
                  objChildRS("USGRPDisplayName") = objgroup.name
                  objChildRS("USGRPDN") = objgroup.distinguishedName
                  objChildRS.update
            next
      end if
      rs1.movenext
wend
objParentRS.MoveFirst
wscript.echo "GroupName,Disabled User's Name"
wscript.echo
Do While Not objParentRS.EOF
      Set objChildRS = objParentRS("rsGRPUS").Value
      if objChildRS.recordCount <> 0 then
            Do While Not objChildRS.EOF
                  Wscript.echo objParentRS.fields("GRPDisplayName") & "," & _
                  objChildRS.fields("USDisplayName")
                  if mode = "remove" then
                        set objgroup = getobject("LDAP://" & objChildRS.fields("USGRPDN"))
                        Set objUser = getobject("LDAP://" & objChildRS.fields("USDN"))
                        objGroup.Remove(objUser.AdsPath)
                        objgroup.setinfo
                        wscript.echo "User-Removed"
                  end if
                  objChildRS.MoveNext
            loop
      end if
      objParentRS.MoveNext
Loop
'===============

Regards,

Rob.

Author

Commented:
I get this error

(59, 13) (null): 0x80005000
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi, this is either because displayName, or distinguishedName is Null....so, to find out which one, above this line:
If instr(rs1.fields("displayname"),"SystemMailbox{") = 0 Then

Please put:
WScript.Echo "Display Name of current record: " & rs1.fields("displayname") & vbCrLf & "Distinguished Name of current record: " & rs1.fields("distinguishedName")

and then see what the output is.

Regards,

Rob.

Author

Commented:
Can we add on error resume next to stop showing these errors and carry on with the script if it is empty?

Author

Commented:
I checked the echo and it is displaying all the records and throws an error when there is not distinguished name.

Can you help me with the on error resume next or is there any other way we could avoid this?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
OK, so where you have:
            set objuser = getobject("LDAP://" & rs1.fields("distinguishedName"))
            For each objgroup in objuser.groups
                  objChildRS.addnew
                  objChildRS("USDisplayName") = rs1.fields("displayname")
                  objChildRS("USDN") = rs1.fields("distinguishedName")
                  objChildRS("USGRPDisplayName") = objgroup.name
                  objChildRS("USGRPDN") = objgroup.distinguishedName
                  objChildRS.update
            next


use:
         If rs1.fields("distinguishedName") <> "" And IsNull(rs1.fields("distinguishedName")) = False Then
            set objuser = getobject("LDAP://" & rs1.fields("distinguishedName"))
            For each objgroup in objuser.groups
                  objChildRS.addnew
                  objChildRS("USDisplayName") = rs1.fields("displayname")
                  objChildRS("USDN") = rs1.fields("distinguishedName")
                  objChildRS("USGRPDisplayName") = objgroup.name
                  objChildRS("USGRPDN") = objgroup.distinguishedName
                  objChildRS.update
            next
         End If


and it should skip that record if Distinguished name is null or empty.

Regards,

Rob.

Author

Commented:
Rob,

Can this line be changed to look into specific OU?
Set iAdRootDSE = GetObject("LDAP://RootDSE")

changed to
Set iAdRootDSE = GetObject("LDAP://RootDSE & StrOU")
Where we define the StrOU = "OU=Test,"
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Yes, where you have two instances of these lines:
strQuery = "<LDAP://" & strDefaultNamingContext & ">;" & GALQueryFilter & _
";distinguishedName,displayname,legacyExchangeDN,homemdb;subtree"

change those two instances to this:
strOU = "OU=Test,"
If Right(strOU, 1) <> "" Then strOU = strOU & ","
strQuery = "<LDAP://" & strOU & strDefaultNamingContext & ">;" & GALQueryFilter & _
";distinguishedName,displayname,legacyExchangeDN,homemdb;subtree"


Regards,

Rob.

Author

Commented:
I made all the changes and i get the below error

(43, 1) Active Directory: An invalid directory pathname was passed
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
After
strQuery = "<LDAP://" & strOU & strDefaultNamingContext & ">;" & GALQueryFilter & _
";distinguishedName,displayname,legacyExchangeDN,homemdb;subtree"

please put
MsgBox strQuery

and make sure that looks correct, with the commas in the correct spot.

Regards,

Rob.

Author

Commented:
This is the code below I am trying.....

If LCase(Right(Wscript.FullName, 11)) = "wscript.exe" Then
    strPath = Wscript.ScriptFullName
    strCommand = "%comspec% /k cscript  """ & strPath & """"
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run(strCommand), 1, True
    Wscript.Quit
End If

'mode = "remove"
mode = "display"
wscript.echo
set conn = createobject("ADODB.Connection")
set com = createobject("ADODB.Command")
set conn1 = createobject("ADODB.Connection")
strConnString = "Data Provider=NONE; Provider=MSDataShape"
conn1.Open strConnString
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")
strDefaultNamingContext = iAdRootDSE.Get("defaultNamingContext")
set objParentRS = createobject("adodb.recordset")
set objChildRS = createobject("adodb.recordset")
strSQL = "SHAPE APPEND" & _
" NEW adVarChar(255) AS GRPDisplayName, " & _
" NEW adVarChar(255) AS GRPDN, " & _
" ((SHAPE APPEND " & _
" NEW adVarChar(255) AS USDisplayName, " & _
" NEW adVarChar(255) AS USDN, " & _
" NEW adVarChar(255) AS USGRPDisplayName, " & _
" NEW adVarChar(255) AS USGRPDN " & _
")" & _
" RELATE GRPDN TO USGRPDN) AS rsGRPUS " 
objParentRS.LockType = 3
objParentRS.Open strSQL, conn1
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
GALQueryFilter = "(&(mailnickname=*)(|(objectCategory=group)))"
strOU = "OU=test,"
If Right(strOU, 1) <> "" Then strOU = strOU & ","
strQuery = "<LDAP://" & strOU & strDefaultNamingContext & ">;" & GALQueryFilter & _
";distinguishedName,displayname,legacyExchangeDN,homemdb;subtree"
Com.ActiveConnection = Conn
Com.CommandText = strQuery
Set Rs = Com.Execute
while not rs.eof
      objParentRS.addnew
      objParentRS("GRPDisplayName") = rs.fields("displayname")
      objParentRS("GRPDN") = rs.fields("distinguishedName")
      objParentRS.update
      rs.movenext
wend
GALQueryFilter = _
"(&(&(mailnickname=*)(objectCategory=person)(userAccountControl:1.2.840.113556.1.4.803:=2)))"
strQuery = "<LDAP://" & strOU & strDefaultNamingContext & ">;" & GALQueryFilter & _
";distinguishedName,displayname,legacyExchangeDN,homemdb;subtree"
Com.ActiveConnection = Conn
Com.CommandText = strQuery
Set Rs1 = Com.Execute
Set objChildRS = objParentRS("rsGRPUS").Value
while not rs1.eof
WScript.Echo "Display Name of current record: " & rs1.fields("displayname") & vbCrLf & "Distinguished Name of current record: " & rs1.fields("distinguishedName")
         If rs1.fields("distinguishedName") <> "" And IsNull(rs1.fields("distinguishedName")) = False Then
            set objuser = getobject("LDAP://" & rs1.fields("distinguishedName"))
            For each objgroup in objuser.groups
                  objChildRS.addnew
                  objChildRS("USDisplayName") = rs1.fields("displayname")
                  objChildRS("USDN") = rs1.fields("distinguishedName")
                  objChildRS("USGRPDisplayName") = objgroup.name
                  objChildRS("USGRPDN") = objgroup.distinguishedName
                  objChildRS.update
            next
      end if
      rs1.movenext
wend
objParentRS.MoveFirst
wscript.echo "GroupName,Disabled User's Name"
wscript.echo
Do While Not objParentRS.EOF
      Set objChildRS = objParentRS("rsGRPUS").Value
      if objChildRS.recordCount <> 0 then
            Do While Not objChildRS.EOF
                  Wscript.echo objParentRS.fields("GRPDisplayName") & "," & _
                  objChildRS.fields("USDisplayName")
                  if mode = "remove" then
                        set objgroup = getobject("LDAP://" & objChildRS.fields("USGRPDN"))
                        Set objUser = getobject("LDAP://" & objChildRS.fields("USDN"))
                        objGroup.Remove(objUser.AdsPath)
                        objgroup.setinfo
                        wscript.echo "User-Removed"
                  end if
                  objChildRS.MoveNext
            loop
      end if
      objParentRS.MoveNext
Loop
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Whoops, this line:
If Right(strOU, 1) <> "" Then strOU = strOU & ","

should be
If Right(strOU, 1) <> "," Then strOU = strOU & ","

but also, just before that put
WScript.Echo strQuery

and make sure the LDAP path is correct.

Regards,

Rob.

Author

Commented:
It is showing the results now as

Groups              Username

How can we put everything together with the last login details?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
OK great.  I've been reading some difficulty in getting LastLogon to work, but I'll look at this when I'm at work tomorrow.  I need to stuff at home for the weekend now.

See you then.

Regards,

Rob.

Author

Commented:
Have a nice day. Enjoy!

See you tomorrow.....

regards
Chandru
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
OK, this should add your Group lists as well:

'==============
If LCase(Right(Wscript.FullName, 11)) = "wscript.exe" Then
      strPath = Wscript.ScriptFullName
      strCommand = "%comspec% /k cscript  """ & strPath & """"
      Set objShell = CreateObject("Wscript.Shell")
      objShell.Run(strCommand), 1, True
      Wscript.Quit
End If

Const ADS_UF_ACCOUNTDISABLE = 2
strOutputFile = "User_Disabled_Status.csv"

strOUPath = ""

Set objRootDSE = GetObject("LDAP://RootDSE")
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
objCommand.CommandText = _
      "<GC://" & strOUPath & objRootDSE.Get("defaultNamingContext") & ">;(objectCategory=User)" & _
            ";userAccountControl,distinguishedName;subtree"  
Set objRecordSet = objCommand.Execute
 
intCounterDisabled = 0
intCounterEnabled = 0
intAlreadyExpired = 0
intExpiredWithin60Days = 0
intDisabledWithin60Days = 0
intNeverExpire = 0

strDetails = """User Name"",""Expired <= 60 Days Ago"",""Expired > 60 Days Ago"",""Will Never Expire"",""Enabled"",""Disabled"",""Disabled <= 60 Days Ago"",""Disabled > 60 Days Ago"",""Hidden From GAL"",""DL Memberships"""
Do Until objRecordset.EOF
      intUAC=objRecordset.Fields("userAccountControl")
      Set objUser = GetObject("LDAP://" & objRecordset.Fields("distinguishedName"))
      strDetails = strDetails & VbCrLf & """" & objUser.DisplayName & ""","
      On Error Resume Next
      accountExpires = objUser.AccountExpirationDate
      If accountExpires = "1/1/1970" Or accountExpires = "1/01/1601 10:00:00 AM" Or Err.Number = -2147467259 Then
            Err.Clear
            On Error Goto 0
            intNeverExpire = intNeverExpire + 1
            strDetails = strDetails & """"","""",""YES"","
      ElseIf CDate(accountExpires) < Now Then
            Err.Clear
            On Error GoTo 0
            If DateDiff("d",CDate(accountExpires),Now) < 60 Then
                  intExpiredWithin60Days = intExpiredWithin60Days + 1
                  strDetails = strDetails & """YES"","""","""","
            Else
                  intAlreadyExpired = intAlreadyExpired + 1
                  strDetails = strDetails & """"",""YES"","""","
            End If
      End If
      If intUAC And ADS_UF_ACCOUNTDISABLE Then
            WScript.echo objRecordset.Fields("distinguishedName") & " is disabled"
            strDetails = strDetails & """"",""YES"","
            On Error Resume Next
            whenChanged = objUser.whenChanged
            If Err.Number = 0 Then
                  On Error GoTo 0
                  If DateDiff("d",CDate(whenChanged),Now) < 60 Then
                        intDisabledWithin60Days = intDisabledWithin60Days + 1
                        strDetails = strDetails & """YES"","""""
                  Else
                        intCounterDisabled = intCounterDisabled + 1
                        strDetails = strDetails & """"",""YES"""
                  End If
            Else
                  Err.Clear
                  On Error GoTo 0
                  intCounterDisabled = intCounterDisabled + 1
                  strDetails = strDetails & """"",""YES"""
            End If
      Else
            WScript.echo objRecordset.Fields("distinguishedName") & " is enabled"
            intCounterEnabled = intCounterEnabled + 1
            strDetails = strDetails & """YES"","""","""","""""
      End If
      strDetails = strDetails & ",""" & objUser.msExchHideFromAddressLists & """"
      strDLGroups = ""
      For Each objGroup In objUser.Groups
            If objGroup.mailNickname <> "" Then
                  If Left(objGroup.Name, 3) = "CN=" Then
                        strDLName = Right(objGroup.Name, Len(objGroup.Name) - 3)
                  Else
                        strDLName = objGroup.Name
                  End If
                  If strDLGroups = "" Then
                        strDLGroups = strDLName
                  Else
                        strDLGroups = strDLGroups & ";" & strDLName
                  End If
                  'objGroup.distinguishedName
            End If
      Next
      strDetails = strDetails & ",""" & strDLGroups & """"
      objRecordset.MoveNext
Loop

WScript.Echo VbCrLf & "A total of " & intCounterDisabled + intDisabledWithin60Days & " accounts are disabled."
WScript.Echo VbCrLf & "A total of " & intDisabledWithin60Days & " accounts have been disabled within the last 60 days."
WScript.Echo VbCrLf & "A total of " & intCounterDisabled & " accounts were disabled over 60 days ago."
WScript.Echo VbCrLf & "A total of " & intCounterEnabled & " accounts are enabled."
WScript.Echo VbCrLf & "A total of " & intAlreadyExpired & " accounts have already expired."
WScript.Echo VbCrLf & "A total of " & intExpiredWithin60Days & " accounts have expired within the last 60 days."
WScript.Echo VbCrLf & "A total of " & intNeverExpire & " accounts are not set to expire."

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)
objOutputFile.Write strDetails
objOutputFile.Close
Set objOutputFile = Nothing
Set objFSO = Nothing
'==============

Regards,

Rob.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Oh, and I blanked out strOU, so fill that in for your testing too.

Rob.

Author

Commented:
I get this error rob.

(23, 1) Provider: Table does not exist.

Is this comibination of the distribution groups as well?

Author

Commented:
Hi Rob,

It works fine. The OU information was wrong. Can we compile the whole script?

regards
Chandru
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi Chandru,

So it's working now?  You can't *really* compile a VBScript.....there are a couple of VBS2EXE programs around (just search for VBS to EXE), but I don't use any....and if I have to, PrimalScript Professional (which you have to pay for) does come with a VBS compiler built in......

Regards,

Rob.

Author

Commented:
Sorry to confuse you with compiling.

I meant to actually put everything together for the first question

regards
Chandru
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
OK, so what's left......I can work on a bit more tomorrow.....

Author

Commented:
1.It should log the information for only the disabled users.
2.Lastlogin time to compared.
3. Remove all the disabled accounts from Distribution list
4. Set to receive mails only from Authenticated users
5. Enable Account hidden from GAL
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Rob,

How did you find the distribution groups?

Author

Commented:
Can you also help me with the additional code for moving the disabled accounts to a particular OU after this is being done and to log the same information with all the users moved?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Chandru, after studying the code you provded, I realised the only thing that seemed to define a group as a Distribution List was the fact that it had a mailNickname.

So, this statement, when applied to any AD user object:
For Each objGroup In objUser.Groups

would give you all of the groups they were a member of, including normal AD security groups, but if you filter those groups with this line:
            If objGroup.mailNickname <> "" Then

then you get only Mail groups, which appear to be distribution groups, as far as I can tell.

These two lines would move the user:
Set objNewOU = GetObject("LDAP://ou=finance," & objRootDSE.Get("defaultNamingContext"))
objNewOU.MoveHere objUser.ADsPath, vbNullString

so specify the correct OU, and put that after this line:
WScript.Echo "Removing " & objUser.AdsPath & VbCrLf & "from group: " & objGroup.AdsPath

Regards,

Rob.

Author

Commented:
Thanks Rob!

I find that it doesn't remove the Distribution groups that are part of a different child domain

regards
Chandru
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Yeah, that doesn't surpise me....I've been having trouble with Sharath in working with other domain groups....they can be tricky......you need to connect to a specific domain controller from that domain on which that group belongs, then remove the member......but I don't know how to get the names of the domain controllers from other domains......

Regards,

Rob.

Commented:
Rob i have posted a Q related to the same Question

https://www.experts-exchange.com/Programming/Languages/Scripting/Q_22913875.html

I am amazed by the way i get the details...
Need some tweking with it  according to my requirment.Can you please look

Commented:
Rob after disabling the user.What's the point in "Set to receive mails only from Authenticated users"
As anyway the user will not be able to see the mails?
Chandru can you comment please...
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Chandru,
>> I find that it doesn't remove the Distribution groups that are part of a different child domain

Can you tell me if the group name, when in the Excel sheet, has the name of the child domain in it?  Or is there an easy way to find out which child domain it belongs to?

Regards,

Rob.

Author

Commented:
Hi Rob,

The group in the excel sheet doesn't have the child domain name as it is not the distinguished name

will the distinguised name of the groups will help as it will have the domain name information.

For example the domain a user exit dc=test,dc=com
LDAP://cn=test,OU=Groups,dc=test,dc=com

another child domain dc=test1,dc=com
LDAP://cn=child,OU=Groups,dc=test1,dc=com

Hops this helps.....

Author

Commented:
Hi Rob,

For removing the groups in a different domain we can use the script below
'script starts here
dim groupPath
dim userPath
groupPath = "LDAP://CN=user,OU=Groups,OU=test,OU=WC,DC=test,DC=development,DC=com"
userPath = "LDAP://CN=User_test,OU=Disabled Accounts,OU=DD,DC=test1,DC=development,DC=com"

wscript.echo grouppath
wscript.echo grouppath

removeFromGroup userPath,groupPath

sub removeFromGroup(userPath, groupPath)

      dim objGroup
      set objGroup = getobject(groupPath)
      
      for each member in objGroup.members
            if lcase(member.adspath) = lcase(userPath) then
                  objGroup.Remove(userPath)
                  exit sub
            end if
      next
      
End sub

But the user who is running the script should be a member of Enterprise admin who have access to remove the groups.

If you have two domains of the same scenario and you are part of Enterprise admin can you just go to a user and trying removing the group from a different domain and see if that is getting removed.

If you are member of domain admin and you are trying to remove the group you will get an error as you don't have rights to that domain.

May be sharath can help us with this to test, and we can include the script which will remove the groups

regards
Chandru

Commented:
Hi Chandru,

If you can give me the full code i can test it for you...

Author

Commented:
Sharath,

I think you had some issues removing groups from some users. Can you rightclick the user and then remove the group and see if you are getting from the ADUC console? The groups should be part of the different domain

regards
Chandru

Commented:
Yes chandru i am able to remove users from groups. And even groups from users...Manually from the ADUC console i am able to do...

Author

Commented:
Are the groups part n?of the child domain or the root domain?

Whoops! you should be removing groups from users from "memberof" and the groups should be part of the child domain. Hope this is clear

regards
Chandru

Commented:
ohhh i thought from the members ...tab..

The groups are pasrt of Root Domain...

Author

Commented:
Did you get any error removing the groups from the users and the user you are using are part of the enterprise admin or equal rigts or it has only domain admin rights in the child domain

regards
Chandru

Commented:
Yes the user i am using is a part of enterprise admin .I dont get any error.

Author

Commented:
So the script should also work.

Try this..... Change the Grouppath and the userpath to match the distinguished name of the Group and user respectively. Group should be part of the root domain and user should be part of the child domain

'script starts here
dim groupPath
dim userPath
groupPath = "LDAP://CN=user,OU=Groups,OU=test,OU=WC,DC=test,DC=development,DC=com"
userPath = "LDAP://CN=User_test,OU=Disabled Accounts,OU=DD,DC=test1,DC=development,DC=com"

wscript.echo grouppath
wscript.echo grouppath

removeFromGroup userPath,groupPath

sub removeFromGroup(userPath, groupPath)

      dim objGroup
      set objGroup = getobject(groupPath)
     
      for each member in objGroup.members
            if lcase(member.adspath) = lcase(userPath) then
                  objGroup.Remove(userPath)
                  exit sub
            end if
      next
     
End sub

Author

Commented:
Sharath,

Did you get any error or the script successfully removed the group from the memberof tab?

regards
Chandru

Commented:
Chandru

Should both of these be groups
Root group
groupPath = "LDAP://CN=user,OU=Groups,OU=test,OU=WC,DC=test,DC=development,DC=com"
Local group
userPath = "LDAP://CN=User_test,OU=Disabled Accounts,OU=DD,DC=test1,DC=development,DC=com"

Root group should be removed from the member of the local group.Am i right

Author

Commented:
No.

Grouppath = should be group in the root domain
Userpath = User in the childdomain

regards
Chandru

Commented:
So that means that the user should be removed from the root domain group.Am i right

Commented:
Rob has given me a script that can remove all users in a file from the mentioned group in the script.Even from the root groups.It works great.

'================
strInputFile = "Users_to_Remove.txt"
strLogFile = "Remove_Users_Log"
strGroupPath = "CN=NewJoiners,OU=Distribution Groups,"
If Right(strGroupPath, 1) <> "," Then strGroupPath = strGroupPath & ","

Set objFSO = CreateObject("Scripting.FileSystemObject")
Const intForReading = 1

Set objRootDSE = GetObject("LDAP://RootDSE")
' Use this line to use the default domain name
'strDNSName = objRootDSE.Get("defaultNamingContext")
' Or this line to use a custom domain name
strDNSName = "DC=iSOFTGroup,DC=co,DC=uk"

Set objOutputFile = objFSO.CreateTextFile(strLogFile, True)
On Error Resume Next
Set objGroup = GetObject("LDAP://" & strGroupPath & strDNSName)
If Err.Number = 0 Then
      On Error GoTo 0
      objOutputFile.WriteLine "Removing users from LDAP://" & strGroupPath & strDNSName
      Set objInputFile = objFSO.OpenTextFile(strInputFile, intForReading, False)
      While Not objInputFile.AtEndOfStream
            strNTLogin = objInputFile.ReadLine
            boolFound = False
            For Each objMember In objGroup.Members
                  If LCase(strNTLogin) = LCase(objMember.samAccountName) Then
                        boolFound = True
                        On Error Resume Next
                        objGroup.Remove objMember.ADsPath
                        If Err.Number = 0 Then
                              On Error GoTo 0
                              objOutputFile.WriteLine strNTLogin & " was successfully removed."
                        Else
                              Err.Clear
                              On Error GoTo 0
                              objOutputFile.WriteLine "Failed to remove " & strNTLogin
                        End If
                        Exit For
                  End If
            Next
            If boolFound = False Then objOutputFile.WriteLine strNTLogin & " is not a member."
      Wend
Else
      objOutputFile.WriteLine "Could not connect the group at: LDAP://" & strGroupPath & strDNSName
End If

objOutputFile.Close
Set objOutputFile = Nothing

MsgBox "Finished"
'================
Hope this helps...

Author

Commented:
Yes you are right!

I think that was the problem you had and Rob was working on it. am i right

Author

Commented:
Can you look at the post  ID: 20136417 and confirm?

Commented:
Chandru...

I have this group on the root domain which is in US.
I am running the above script given by rob on an India DC.Which removes all users from the group which is in the Root DC

Commented:
The problem we had was with the excel user creation which is pending and waiting for your confirmation.

In the excel script we were not able to add the creted users to the root domain group while creating.

The groups were mentioned in the excel but was not getting added ...

Author

Commented:
Oh! the only issue is adding groups to the root domain. Got confused!
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Chandru, back to this question, I think you and I were up to the point where we need to remove the user from the group that belongs in the child domain, is that right?

I am confused though, with the code I posted at ID 20136196, there is this line:
WScript.Echo "Removing " & objUser.AdsPath & VbCrLf & "from group: " & objGroup.AdsPath

Don't both of those ADsPath values show something similar to what you've shown Sharath in:
groupPath = "LDAP://CN=user,OU=Groups,OU=test,OU=WC,DC=test,DC=development,DC=com"
userPath = "LDAP://CN=User_test,OU=Disabled Accounts,OU=DD,DC=test1,DC=development,DC=com"

Which would mean it should remove the user....or do we need to "massage" those ADsPath bindings?  Can you please provide an example of the output provided by my script?

Regards,

Rob.

Author

Commented:
Rob,

You are right.

I found the issue. I was running with the account which doesn't have Enterprise admin rights.

regards
Chandru
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Fantastic, does that mean that we are finished with the original question of this thread?

Regards,

Rob.

Author

Commented:
Yes we are finishsed. Awesome!

thanks for all your help and nice working with you

regards
Chandru

Author

Commented:
Rob,

What was the issue with Sharath script in adding users to groups in root domain?

regards
Chandru
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Thanks Chandru.  The problem that we had with "adding" users to groups, was with the code I posted at ID: 20054590 from the Excel user creation question at:
https://www.experts-exchange.com/Programming/Languages/Scripting/Q_22880472.html

I wonder though, after what I've read from this one, if Sharath was using an Enterprise Admin user account.....

Anyway, within that code, in column E from the Excel file, if you specify something like
OTHERDC.OTHERDOMAIN.COM\GroupName

then it will connect to the other domain, using the other DC, and try to add the user......but it didn't seem to work.  We did find out, however, that you can use certain types of Security groups, and not others..... do you know any more about that?

Regards,

Rob.

Author

Commented:
Hi Rob,

Hope this helps.....

http://www.tek-tips.com/viewthread.cfm?qid=1413875&page=5

regards
Chandru

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.