Active Directory Query Statement 1

[grebnetso]

I'm trying to run a custom query on my domain to find any user accounts that have their pw set to never expire. The field isn't listed as far as I can find so I'm assuming I need to do a custom option and an LDAP search. It looks like the attribute is listed as "expirationTime" in Adsi, but I'm not sure how I should phrase it. Does anyone know what syntax I need to use to run the query? Thanks.

 

[grebnetso]

My mistake I meant I need a query statement to look for account expiration not pw expiration.

 

[WhoKilledKenny]

Without getting to deep into this post here is a query that will check for all accounts that have expired since 1/1/2000

(&(objectClass=User)(accountExpires<=1259118360000000000)(accountExpires>=1))


If you want to change the date in accountExpires use the vbscript code at the bottom of this post. It will convert the date to the integer required. Should you need to pull the actual expiration date using a LDAP query I would like to know how. I have not been able to do this without using VBscript.


Code:
Simply copy and paste the following text into a file called DateToInteger8.vbs and execute from a command prompt.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' DateToInteger8.vbs
' VBScript program demonstrating how to convert a datetime value to
' the corresponding Integer8 (64-bit) value. The Integer8 value is the
' number of 100-nanosecond intervals since 12:00 AM January 1, 1601,
' in Coordinated Universal Time (UTC). The conversion is only accurate
' to the nearest second, so the Integer8 value will always end in at
' least 7 zeros.
'
' ----------------------------------------------------------------------
' Copyright (c) 2004 Richard L. Mueller
' Hilltop Lab web site -

http://www.rlmueller.net

' Version 1.0 - June 11, 2004
'
' You have a royalty-free right to use, modify, reproduce, and
' distribute this script file in any way you find useful, provided that
' you agree that the copyright owner above has no warranty, obligations,
' or liability for such use.

Option Explicit

Dim dtmDateValue, dtmAdjusted, lngSeconds, str64Bit
Dim objShell, lngBiasKey, lngBias, k

If Wscript.Arguments.Count <> 1 Then
Wscript.Echo "Required argument <DateTime> missing"
Wscript.Echo "For example:"
Wscript.Echo ""
Wscript.Echo "cscript DateToInteger8.vbs ""2/5/2004 4:58:58 PM"""
Wscript.Echo ""
Wscript.Echo "If the date/time value has spaces, enclose in quotes"
Wscript.Quit
End If

dtmDateValue = CDate(Wscript.Arguments(0))

' Obtain local Time Zone bias from machine registry.
Set objShell = CreateObject("Wscript.Shell")
lngBiasKey = objShell.RegRead("HKLM\System\CurrentControlSet\Control\" _
& "TimeZoneInformation\ActiveTimeBias")
If UCase(TypeName(lngBiasKey)) = "LONG" Then
lngBias = lngBiasKey
ElseIf UCase(TypeName(lngBiasKey)) = "VARIANT()" Then
lngBias = 0
For k = 0 To UBound(lngBiasKey)
lngBias = lngBias + (lngBiasKey(k) * 256^k)
Next
End If

' Convert datetime value to UTC.
dtmAdjusted = DateAdd("n", lngBias, dtmDateValue)

' Find number of seconds since 1/1/1601.
lngSeconds = DateDiff("s", #1/1/1601#, dtmAdjusted)

' Convert the number of seconds to a string
' and convert to 100-nanosecond intervals.
str64Bit = CStr(lngSeconds) & "0000000"
Wscript.Echo "Integer8 value: " & str64Bit

 

[PScottC]

Try this one... This will show only user accounts with the "Password Never Expires" flag set...

(&(objectClass=user)(objectCategory=person)(userAccountControl:1.2.840.113556.1.4.803:=65536))

Try my FAQ for additional fun stuff you can do...FAQ774-5667

PSC

Governments and corporations need people like you and me. We are samurai. The keyboard cowboys. And all those other people out there who have no idea what's going on are the cattle. Mooo! --Mr. The Plague, from the movie "Hackers

 

[grebnetso]

Thanks for the help. Where would I need to look if I wanted to find information like useraccountcontrol:** In case I wanted to query something else? Is there anywhere I can look in adsi or something or is it just a known thing?

 

[WhoKilledKenny]

PScottC has that info in his FAQ - check out the link, good info.

 

[grebnetso]

My question was more like where did he find the information that tells you that userAccountControl:1.2.840.113556.1.4.803 is the password attribute? Is there somewhere I can check in Adsi that informs me of this?

 

[WhoKilledKenny]

In ADSI you see the userAccountControl number which is the sum of all the bits assigned to the user.

http://support.microsoft.com/default.aspx?scid=kb;en-us;305144

Property flag Value - hexadecimal Value - decimal Value
SCRIPT 0x0001 1
ACCOUNTDISABLE 0x0002 2
HOMEDIR_REQUIRED 0x0008 8
LOCKOUT 0x0010 16
PASSWD_NOTREQD 0x0020 32
PASSWD_CANT_CHANGE
Note You cannot assign this permission by directly modifying the UserAccountControl attribute. For information about how to set the permission programmatically, see the "Property flag descriptions" section. 0x0040 64
ENCRYPTED_TEXT_PWD_ALLOWED 0x0080 128
TEMP_DUPLICATE_ACCOUNT 0x0100 256
NORMAL_ACCOUNT 0x0200 512
INTERDOMAIN_TRUST_ACCOUNT 0x0800 2048
WORKSTATION_TRUST_ACCOUNT 0x1000 4096
SERVER_TRUST_ACCOUNT 0x2000 8192
DONT_EXPIRE_PASSWORD 0x10000 65536
MNS_LOGON_ACCOUNT 0x20000 131072
SMARTCARD_REQUIRED 0x40000 262144
TRUSTED_FOR_DELEGATION 0x80000 524288
NOT_DELEGATED 0x100000 1048576
USE_DES_KEY_ONLY 0x200000 2097152
DONT_REQ_PREAUTH 0x400000 4194304
PASSWORD_EXPIRED 0x800000 8388608
TRUSTED_TO_AUTH_FOR_DELEGATION 0x1000000 16777216

 

[WhoKilledKenny]

FYI: 1.2.840.113556.1.4.803 means AND
1.2.840.113556.1.4.803:=65536 in this 65536 is the userAccountControl

hope this helps....

 

[grebnetso]

I'm trying to combine two statements, but my logicometer doesn't seem to be kicking in. I want to view all accounts that haven't logged in in over 90 days but omit any that have never logged in. The one for 90 days looked something like this (objectClass=user)(objectCategory=person)(lastLogon<=1278408688500000000). I've tried different variations of thisobjectClass=user)(objectCategory=person)(lastLogon<=1278408688500000000))(&(!(objectClass)(objectCategory=person)(lastLogon>0))). I've tried a few other's like removing the ((objectClass)(objectCategory=person )) portion but obviously my brain is hiccuping. Could someone give me example of how to set this up.

 

[PScottC]

Actually, the lastLogon attribute is not replicated in AD... So you would have to run that query against every DC and then eliminate the duplicates... Rather messy.

Instead, use lastLogonTimestamp. This attribute is updated every 7 days and IS replicated to all DC's. So your measurement should be for 97 days to ensure that you get all the right accounts. So for right now, your query would look like this...

[green](&(objectCategory=person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(lastLogonTimestamp<=127835100000000000)(!(lastLogonTimestamp=0)))[/green]

This shows users who have not logged in since between 2/4/06 and 2/11/06, are not disabled, and have logged in at least once.

The key you have to consider when building these queries is how you encapsulate the ANDs and the NOTs. For example, we have Filters A, B, and C (attribute=value is a filter)

So if I want to check for A AND B the format is:
[blue](&(A)(B))[/blue]
Notice that the (&) encapsulates the filters.

What about A AND B AND C?
[blue](&(A)(B)(C))[/blue]

How about A AND {NOT B} AND C?
[blue](&(A)(!(B))(C))[/blue]
Notice that B is encapsulated in (!), which negates that value, but all values must still be true because they are encapsulated in (&).

And a final example: A AND {NOT {B OR C}}
[blue](&(A)(!(|(B)(C))))[/blue]

I hope this helps.


PSC

Governments and corporations need people like you and me. We are samurai. The keyboard cowboys. And all those other people out there who have no idea what's going on are the cattle. Mooo! --Mr. The Plague, from the movie "Hackers

 

[grebnetso]

Thanks, I like how you broke down the encapsulation. Very helpful. Thanks again.