Cleaning up AD script

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.

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

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.

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

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.

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)

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

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.

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

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.

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

Hi Rob,

Did you get a change to work on this?

Hi Rob,

Can you help me in giving a start ahead?

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.

I get this error

(59, 13) (null): 0x80005000

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.

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

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?

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.

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,"

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.

I made all the changes and i get the below error

(43, 1) Active Directory: An invalid directory pathname was passed

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.

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

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.

It is showing the results now as

Groups              Username

How can we put everything together with the last login details?

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.

Have a nice day. Enjoy!

See you tomorrow.....

regards
Chandru

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.

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

Rob.

I get this error rob.

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

Is this comibination of the distribution groups as well?

Hi Rob,

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

regards
Chandru

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.

Sorry to confuse you with compiling.

I meant to actually put everything together for the first question

regards
Chandru

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

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

Hi Rob,

How did you find the distribution groups?

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?

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.

Thanks Rob!

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

regards
Chandru

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.

Rob i have posted a Q related to the same Question

https://www.experts-exchange.com/questions/22913875/Need-changes-with-this-script.html

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

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

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.

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

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

Hi Chandru,

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

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

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

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

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

The groups are pasrt of Root Domain...

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

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

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

Sharath,

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

regards
Chandru

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

No.

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

regards
Chandru

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

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

Yes you are right!

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

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

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

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

Oh! the only issue is adding groups to the root domain. Got confused!

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.

Rob,

You are right.

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

regards
Chandru

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

Regards,

Rob.

Yes we are finishsed. Awesome!

thanks for all your help and nice working with you

regards
Chandru

Rob,

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

regards
Chandru

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/questions/22880472/Create-users-from-excel-Continuation.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.

Hi Rob,

Hope this helps.....

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

regards
Chandru