Check pw expiration date for all users in AD then email result

[Mike]

Hello all.

I need to check the password expiration dates for all users in the doman
preferably with a vb script then have the results sent in an email to me.
This script needs to be ran on the domain controller (server 2003 sp2). I've
tried to modify current scripts for my needs but I'm afraid I keep getting
errors within the scripts. Has anyone here done this?

Thanks
Mike

[Owen Gilmore]

This should be able to help you out.

http://www.microsoft.com/downloads/details.aspx?FamilyID=39044e17-2490-487d-9a92-ce5dcd311228&DisplayLang=en

[Mike]

Heres the script I'm trying to get working. Its error is line 36 char 1 table
does not exist.

Option Explicit

Dim objCommand, objConnection, objChild, objUserConnection, strBase,
strFilter, strAttributes, strPasswordChangeDate, intPassAge
Dim lngTZBias, objPwdLastSet, strEmailAddress, objMessage
Dim objShell, lngBiasKey, k, PasswordExpiry, strRootDomain
Dim strQuery, objRecordset, strName, strCN

PasswordExpiry=35
strRootDomain="dc=williamscas,dc=com"

Set objShell = CreateObject("Wscript.Shell")
lngBiasKey =
objShell.RegRead("HKLM\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias")
If UCase(TypeName(lngBiasKey)) = "LONG" Then
lngTZBias = lngBiasKey
ElseIf UCase(TypeName(lngBiasKey)) = "VARIANT()" Then
lngTZBias = 0
For k = 0 To UBound(lngBiasKey)
lngTZBias = lngTZBias + (lngBiasKey(k) * 256^k)
Next
End If

Set objCommand = CreateObject("ADODB.Command")
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
objCommand.ActiveConnection = objConnection
strBase = "<LDAP://" & strRootDomain & ">"

strFilter = "(&(objectCategory=person)(objectClass=user))"
strAttributes = "sAMAccountName,cn,mail,pwdLastSet,distinguishedName"
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
Set objRecordSet = objCommand.Execute

WScript.echo "Running at " & Date()

Do Until objRecordSet.EOF
strName = objRecordSet.Fields("sAMAccountName").Value
strCN = objRecordSet.Fields("cn").value
strEmailAddress = objRecordSet.Fields("mail").value
Wscript.Echo "NT Name: " & strName & ", Common Name: " & strCN

Set objUserConnection = GetObject("LDAP://" &
objRecordSet.Fields("distinguishedName").Value)
Set objPwdLastSet = objUserConnection.pwdLastSet
strPasswordChangeDate = Integer8Date(objPwdLastSet, lngTZBias)
WScript.Echo vbTab & "Password last changed at " & strPasswordChangeDate
intPassAge = DateDiff("d", strPasswordChangeDate, Now)
WScript.Echo vbTab & "Password changed " & intPassAge & " days ago"

If intPassAge = (PasswordExpiry-3) Then
WScript.echo vbTab & "Sending user notification to " & strEmailAddress &
" that password expires in 3 days"
Call SendEmailMessage(strEmailAddress, 3)
ElseIf intPassAge = (PasswordExpiry-6) Then
WScript.echo vbTab & "Sending user notification to " & strEmailAddress &
" that password expires in 6 days"
Call SendEmailMessage(strEmailAddress, 6)
ElseIf intPassAge = (PasswordExpiry-9) Then
WScript.echo vbTab & "Sending user notification to " & strEmailAddress &
" that password expires in 9 days"
Call SendEmailMessage(strEmailAddress, 9)
End If

objRecordSet.MoveNext
Loop

objConnection.Close

Function Integer8Date(objDate, lngBias)
Dim lngAdjust, lngDate, lngHigh, lngLow
lngAdjust = lngBias
lngHigh = objDate.HighPart
lngLow = objdate.LowPart
If lngLow < 0 Then
lngHigh = lngHigh + 1
End If
If (lngHigh = 0) And (lngLow = 0) Then
lngAdjust = 0
End If
lngDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _
+ lngLow) / 600000000 - lngAdjust) / 1440
On Error Resume Next
Integer8Date = CDate(lngDate)
If Err.Number <> 0 Then
On Error GoTo 0
Integer8Date = #1/1/1601#
End If
On Error GoTo 0
End Function

Sub SendEmailMessage(strDestEmail, strNoOfDays)
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Password Expires in " & strNoOfDays & " days"
objMessage.Sender = "michae...@d2ww.com"
objMessage.To = strDestEmail
objMessage.TextBody = "Your password expires in " & strNoOfDays & " days. "
objMessage.Send
End Sub

[Richard Mueller [MVP]]

Mike wrote:

I believe line 36 is:

Set objRecordset = objCommand.Execute

The error could mean that strRootDomain was not found. Other comments:

1. Variable objChild not used (minor point).
2. Rather than binding to user object, retrieve pwdLastSet from ADO
recordset.
3. Should Dim objMessage in Sub SendEmailMessage (rather than main program).
4. You send email to users even if their password never expires or if they
are not required to have a password.
5. You send email even if mail attribute has no value.

First, make sure value for strRootDomain is correct. Then I would suggest:
================
Option Explicit

Dim objCommand, objConnection, strBase
Dim strFilter, strAttributes, strPasswordChangeDate, intPassAge
Dim lngTZBias, objPwdLastSet, strEmailAddress

Dim objShell, lngBiasKey, k, PasswordExpiry, strRootDomain
Dim strQuery, objRecordset, strName, strCN

PasswordExpiry = 35
strRootDomain = "dc=williamscas,dc=com"

Set objShell = CreateObject("Wscript.Shell")
lngBiasKey =
objShell.RegRead("HKLM\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias")
If UCase(TypeName(lngBiasKey)) = "LONG" Then
lngTZBias = lngBiasKey
ElseIf UCase(TypeName(lngBiasKey)) = "VARIANT()" Then
lngTZBias = 0
For k = 0 To UBound(lngBiasKey)
lngTZBias = lngTZBias + (lngBiasKey(k) * 256^k)
Next
End If

Set objCommand = CreateObject("ADODB.Command")
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
objCommand.ActiveConnection = objConnection
strBase = "<LDAP://" & strRootDomain & ">"

' Filter on users do not have "password never expires"
' or "password not required" set.
strFilter = "(&(objectCategory=person)(objectClass=user)" _
& "(!userAccountControl:1.2.840.113556.1.4.803:=65536)" _
& "(!userAccountControl:1.2.840.113556.1.4.803:=32))"
strAttributes = "sAMAccountName,cn,mail,pwdLastSet"

strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
Set objRecordSet = objCommand.Execute

WScript.echo "Running at " & Date()

Do Until objRecordSet.EOF
strName = objRecordSet.Fields("sAMAccountName").Value
strCN = objRecordSet.Fields("cn").value
strEmailAddress = objRecordSet.Fields("mail").value
Wscript.Echo "NT Name: " & strName & ", Common Name: " & strCN

Set objPwdLastSet = objRecordset.Fields("pwdLastSet").Value

strPasswordChangeDate = Integer8Date(objPwdLastSet, lngTZBias)
WScript.Echo vbTab & "Password last changed at " & strPasswordChangeDate
intPassAge = DateDiff("d", strPasswordChangeDate, Now)
WScript.Echo vbTab & "Password changed " & intPassAge & " days ago"

If intPassAge = (PasswordExpiry - 3) Then
WScript.echo vbTab & "Sending user notification to " _

& strEmailAddress & " that password expires in 3 days"
Call SendEmailMessage(strEmailAddress, 3)

ElseIf intPassAge = (PasswordExpiry - 6) Then
WScript.echo vbTab & "Sending user notification to " _

& strEmailAddress & " that password expires in 6 days"
Call SendEmailMessage(strEmailAddress, 6)

ElseIf intPassAge = (PasswordExpiry - 9) Then
WScript.echo vbTab & "Sending user notification to " _

objRecordSet.MoveNext
Loop

objConnection.Close

Sub SendEmailMessage(strDestEmail, strNoOfDays)
Dim objMessage

If (strDestEmail = "") Then
Wscript.Echo "No email address, no message sent."
Exit Sub
End If

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Password Expires in " & strNoOfDays & " days"
objMessage.Sender = "michae...@d2ww.com"
objMessage.To = strDestEmail
objMessage.TextBody = "Your password expires in " & strNoOfDays & "
days. "
objMessage.Send
End Sub

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

[Gilliland, Gill]

Also note, pwdLastSet get set to 0 by AD if the flag "User must change
password at next logon" is set.

Gill

On 8/3/07 6:36 PM, in article #XyLZ7h1...@TK2MSFTNGP05.phx.gbl,

[Mike]

Thaks for the help so far Richard!

That almost worked. What its doing is sending the output information for
every user in the domain to a pop up message that I need to click OK to
instead of storing them then emailing them. Also I added mail server
information at the bottom from one of my other scriprs that look in the
system logs for errors and then email me when it finds certain event ID's.
Anyway, I'm thinking it is correct but I dont think I will know for sure
until the outputs are put into an email to send.

[Richard Mueller [MVP]]

If you are getting message boxes when the script runs, that means you are
running it with the Wscript host. If you click on the *.vbs file and Wscript
is the default host, that will happen. You should run administrative scripts
at a command prompt with the cscript host. Then the output from Wscript.Echo
statements is displayed in the command window and scrolls as the program
runs - no need to click OK on message boxes. Also, if the output is large,
it can be redirected to a text file. For example, to run the program
Example.vbs at a command prompt:

cscript Example.vbs

This assumes you have navigated to the directory where the file Example.vbs
is saved. If not, specify the full path and file name. To redirect any
output from the program to a text file I use syntax similar to:

cscript //nologo Example.vbs > report.txt

where the optional //nologo parameter suppresses WSH logo information. The
file report.txt is created in the same directory. Also, I would reduce the
number of Wscript.Echo statements. There seemed to be a lot.

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

"Mike" <Mi...@discussions.microsoft.com> wrote in message
news:16C77E9E-65E0-4DEC...@microsoft.com...

[Mike]

Thanks for all the help Richard. It works beautifully.

Have a great weekend!

Miike

[LakeGator]

On Aug 3, 9:06 pm, "Richard Mueller [MVP]" <rlmueller-

nos...@ameritech.nospam.net> wrote:
> If you are getting message boxes when the script runs, that means you are
> running it with the Wscript host. If you click on the *.vbs file and Wscript
> is the default host, that will happen. You should run administrative scripts
> at a command prompt with the cscript host. Then the output from Wscript.Echo
> statements is displayed in the command window and scrolls as the program
> runs - no need to click OK on message boxes. Also, if the output is large,
> it can be redirected to a text file. For example, to run the program
> Example.vbs at a command prompt:
>
> cscript Example.vbs
>
> This assumes you have navigated to the directory where the file Example.vbs
> is saved. If not, specify the full path and file name. To redirect any
> output from the program to a text file I use syntax similar to:
>
> cscript //nologo Example.vbs > report.txt
>
> where the optional //nologo parameter suppresses WSH logo information. The
> file report.txt is created in the same directory. Also, I would reduce the
> number of Wscript.Echo statements. There seemed to be a lot.
>
> --
> Richard Mueller
> Microsoft MVP Scripting and ADSI
> Hilltop Lab -http://www.rlmueller.net
> --
>

> "Mike" <M...@discussions.microsoft.com> wrote in message

> >> > objMessage.Sender = "michael.sm...@d2ww.com"

> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -

Is pwdLastSet a safe attribute to use in a script like this when there
are multiple domain controllers? This attribute is not replicated and
this script seems to use a simple LDAP query that will go to the
closest domain controller.

It was my understanding that all domain controllers must be queried to
find the most recent value of pwdLastSet. In Windows 2003 a new
attribute called lastLogonTimeStamp that is replicated but only if the
time between this and the previous login is 14 days or longer.

[Richard Mueller [MVP]]

"LakeGator" <Lake...@gmail.com> wrote in message
news:1186237649....@l70g2000hse.googlegroups.com...

--------
You are thinking of lastLogon. The pwdLastSet attribute is replicated to all
DC's, while lastLogon is not.