Link to home
Start Free TrialLog in
Avatar of Butmir_Admin
Butmir_Admin

asked on

Active Directory - query for expired accounts

How can I create a query in the Active Directory for all the account (users) that have expired. In "Active Directory Users and computers" in the query the account expires property is not present?
How can I disable automatically the expired accounts?
Avatar of kamalgopi
kamalgopi

this is script to check for disable computers and to move to a OU called Disabaled
dsquery computer -inactive 30 | dsmove -newparent ou=Marketing,dc=testing,dc=com
Just change the OU Name and the DC name

once you have moved the diabled computers , then you can change the dsmove to dsdelte( i am not sure just put a ? and see the excat name for tht).

The inactive switch will only work if you are at 2003 Domain Functional Level/ Forest Functional Level, if you are running at a lower level you can use

dsquery computer -stalepwd 60 | dsmove .....

This checks when the computer account last changed it's password (normally every 30 days) so you just add this to the time you want to check for.

Or use oldcmp from www.joeware.net/freetools, which has switches that will automatically disable accounts, move them to another OU, and/or delete them once disabled.

Hope this helps
Cheers:)
Kamal
Hi!

What exactly do you men by expired accounts? Expired password, locked out accounts, disavled user accounts?

Toni
Avatar of Butmir_Admin

ASKER

An expired account is an expired user: open the user properties> account tab> see account expires: end of, and there is the expiration date.
I create user accounts valid for a couple of months using the expiration date
Hi!

When user account expires it's automatically disabled. Are you looking for query for disabled user accounts?
"dsquery user -disabled'"

HTH

Toni
When an user expires is NOT disabled automatically. The user gets only different error at logon.
This thing I need to do: to find the expired account and disable them.
Or

dsquery user | dsget user -samid -acctexpires

What is returned is the user account in the left column and in the right column the date the account expires. It will be a date or the word "never".
I'm allowed to use only Microsoft tools and scripts. Cannot install this on the network.
Second suggestion uses only MS tools which are part of operating system.
dsquery might work but will show the expiration date and need to import the list in excel, filter the expired accounts, create a csv of the expired accounts and then disable them with dsmod.
There is no other simpler solution?
No, you would have to use custom script. Good starting point would be this one:
http://www.microsoft.com/technet/scriptcenter/resources/qanda/sept05/hey0902.mspx

Unfortunately, I am not scripting expert and it takes time a long to modify scripts myself. Script has to be modified to check today's date and instead of print user names, disable accounts. I suggest that you open a pointer question in Scripting section of EE and I'm sure that someone will come up with the script.
ASKER CERTIFIED SOLUTION
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Is there any way to exclude some accounts from this script?

The simplest way is to modify the search base, or to pick on an attribute (or group membership) on the accounts which identifies those you want to avoid (or those you want to include). More complex methods involve generation of white list.

What did you want to exclude?

Chris
We have some temporary accounts that are opened when needed. Therefore they will expire at the end of the day. Can it be done by adding a OU for these temp accounts?

Potentially, yes.

How do you normally identify those temporary accounts? Do they have obvious names?

Chris
There are one with Temp as first name, and one Guest, but if they were in a spesific OU I don't have to worry about naming...

True, I only wondered if it were simpler to filter on a partial name (an LDAP filter like (!name=Temp*)).

The OU you're thinking off, will it be outside of your current structure? That would mean we could change the search base, excluding the OU with the temp accounts in completely.

Chris
I was thinking about placing an OU called Temporary Accounts on root level..

Fantastic :)

A somewhat modified script then, it's been a few years after all.

You'll need to set SEARCH_ROOT, ideally to something that will mean it misses your new OU with temp accounts in it. The action to disable the users is commented out so you can test it properly.

Any questions?

Chris
Option Explicit

' Things to modify:

Const SEARCH_ROOT = "OU=SomeOU,DC=yourdomain,DC=com"

Const ADS_UF_ACCOUNTDISABLE = 2

' Generate the LDAP Filter

Dim dblInt8 : dblInt8 = CDbl(DateDiff("s", CDate("01/01/1601 00:00:00"), Now))

' Ldap Filter:
'  - Include Users
'  - Include Account Expires prior to current date (has already expired)
'  - Exclude anything which does not have accountExpires set
'  - Exclude disabled accounts (userAccountControl AND 2)

Dim strLdapFilter
strLdapFilter = "(&(objectClass=user)(objectCategory=person)" & _
  "(accountExpires<=" & CStr(dblInt8) & "0000000)(!accountExpires=0)" & _
  "(!userAccountControl:1.2.840.113556.1.4.803:=2))"

Dim objConnection : Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

Dim objCommand : Set objCommand = Createobject("ADODB.Command")
objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000

objCommand.CommandText = "<LDAP://" & SEARCH_ROOT & ">;" & _
    strLdapFilter & ";name,distinguishedName,accountExpires;subtree"

Dim objRecordSet : Set objRecordSet = objCommand.Execute

While Not objRecordSet.EOF

  Dim objAccountExpires : Set objAccountExpires = objRecordSet.Fields("accountExpires").Value
  Dim dtmAccountExpires : dtmAccountExpires = _
    CDate(((objAccountExpires.HighPart * (2^32) + objAccountExpires.LowPart) / 864000000000) + #01/01/1601#)

  ' Echo details of the account the script found

  WScript.Echo "Disabling: " & objRecordSet.Fields("distinguishedName").Value & _
    vbTab & dtmAccountExpires

  ' Disable the Account:

  ' Dim objUser : Set objUser = GetObject("LDAP://" & objRecordSet.Fields("distinguishedName").Value)
  ' objUser.Put "userAccountControl", (objUser.Get("userAccountControl") OR ADS_UF_ACCOUNTDISABLE)
  ' objUser.SetInfo

  objRecordSet.MoveNext
Wend

Open in new window

Const SEARCH_ROOT = "OU=SomeOU,DC=yourdomain,DC=com" is the OU where it should search? This would be easy if my AD was flat, but it's quite full of OU's with users, and I can't think of any easy way to do this with the search root...
What if it's based on group membership? If I create a Temp accounts group will that be any easier?

If you had this kind of layout:

yourdomain.com
          | -- Temp Accounts
          | -- Departments
                        | -- Dept1
                        |        | -- Users
                        |
                        | -- Dept2
                                 | -- Users

Then it'll be fine and would set the base to OU=Departments,DC=yourdomain,DC=com.

If you want to go with excluding members of the group it changes the Ldap Filter section as below. With the LDAP filter the full path of the group must be used, it won't permit wildcards or partial distinguished names.

Otherwise, if you have many root OUs to search we need to have the loop check whether or not the directory path is permissible, so yell if that is the case.

Chris
' Ldap Filter:
'  - Include Users
'  - Include Account Expires prior to current date (has already expired)
'  - Exclude anything which does not have accountExpires set
'  - Exclude disabled accounts (userAccountControl AND 2)
'  - Exclude members of SomeGroup

Dim strLdapFilter
strLdapFilter = "(&(objectClass=user)(objectCategory=person)" & _
  "(accountExpires<=" & CStr(dblInt8) & "0000000)" & _
  "(!accountExpires=0)" & _
  "(!userAccountControl:1.2.840.113556.1.4.803:=2)" & _
  "(!memberOf=CN=SomeGroup,OU=somewhere,DC=yourdomain,DC=com))"

Open in new window

I'll give the group search a go. I do have many root OU's so the alternative is a loop check...

Cool okay, do come back if it doesn't pan out as you want :)

Chris
It seem's to have one small problem...
Dim objRecordSet : Set objRecordSet = objCommand.Execute  results in table does not exist...

It'll do that is the SEARCH_ROOT value doesn't exist. You're just passing it in the form DC=yourdomain,DC=com at the moment? That would apply if your AD Domain was yourdomain.com.

If your domain was ad.domain.net it would be DC=ad,DC=domain,DC=net.

If it's a problem, this version populates the value dynamically.

Chris
Option Explicit

' Things to modify:

' To manually define the search root use the Const value below
' Const SEARCH_ROOT = "OU=SomeOU,DC=yourdomain,DC=com"

' To dynamically set SEARCH_ROOT use these.
Dim objRootDSE : Set objRootDSE = GetObject("LDAP://RootDSE")
Dim SEARCH_ROOT : SEARCH_ROOT = objRootDSE.Get("defaultNamingContext")

Const ADS_UF_ACCOUNTDISABLE = 2

' Generate the LDAP Filter

Dim dblInt8 : dblInt8 = CDbl(DateDiff("s", CDate("01/01/1601 00:00:00"), Now))

' Ldap Filter:
'  - Include Users
'  - Include Account Expires prior to current date (has already expired)
'  - Exclude anything which does not have accountExpires set
'  - Exclude disabled accounts (userAccountControl AND 2)
'  - Exclude members of SomeGroup

Dim strLdapFilter
strLdapFilter = "(&(objectClass=user)(objectCategory=person)" & _
  "(accountExpires<=" & CStr(dblInt8) & "0000000)" & _
  "(!accountExpires=0)" & _
  "(!userAccountControl:1.2.840.113556.1.4.803:=2)" & _
  "(!memberOf=CN=SomeGroup,OU=somewhere,DC=yourdomain,DC=com))"

Dim objConnection : Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

Dim objCommand : Set objCommand = Createobject("ADODB.Command")
objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000

objCommand.CommandText = "<LDAP://" & SEARCH_ROOT & ">;" & _
    strLdapFilter & ";name,distinguishedName,accountExpires;subtree"

Dim objRecordSet : Set objRecordSet = objCommand.Execute

While Not objRecordSet.EOF

  Dim objAccountExpires : Set objAccountExpires = objRecordSet.Fields("accountExpires").Value
  Dim dtmAccountExpires : dtmAccountExpires = _
    CDate(((objAccountExpires.HighPart * (2^32) + objAccountExpires.LowPart) / 864000000000) + #01/01/1601#)

  ' Echo details of the account the script found

  WScript.Echo "Disabling: " & objRecordSet.Fields("distinguishedName").Value & _
    vbTab & dtmAccountExpires

  ' Disable the Account:

  ' Dim objUser : Set objUser = GetObject("LDAP://" & objRecordSet.Fields("distinguishedName").Value)
  ' objUser.Put "userAccountControl", (objUser.Get("userAccountControl") OR ADS_UF_ACCOUNTDISABLE)
  ' objUser.SetInfo

  objRecordSet.MoveNext
Wend

Open in new window

Aha! There it was.... I forgot to modify the first Const SEARCH_ROOT = "OU=SomeOU,DC=yourdomain,DC=com line... Now it seems to work... I'll do some more tests. Thanks for all your help so far....