Create script to list the user whose accounts in excel sheet whose "account expires" in upcoming 2 months
- Hello All,
I am new to the VBscripting. I tried creating the script but got stuck in using the condition (Account expires in upcoming 2 months).I am pasting the script for the review. Please assist me in modifying the scripts to get the list of user accounts.
VBScript
====================================================
On Error Resume Next
Const ADS_SCOPE_SUBTREE = 2
Dim objConnection,objCommand,objUser
Dim strADSPath
Dim dtmAccountExpires
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.CommandText = _
"SELECT distinguishedName FROM'LDAP://dc=contoso,dc=com' WHERE objectCategory='user'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Set objFSO = CreateObject("scripting.filesystemobject")
Set logStream = objFSO.opentextfile("C:\accountexpires.csv", 3, True)
logStream.writeline("Name,AccountExpires,AccountExpirationDate")
Do Until objRecordSet.EOF
Set strADSPath = objRecordset.Fields("distinguishedName").value
Set objUser = GetObject(strADSPath)
dtmAccountExpires=CDate(objUser.AccountExpirationDate)
If Month(dtmAccountExpires)> 1 Then
If dtmAccountExpires < Date() Then
Text = "Yes"
Else
Text = "No"
End If
End If
logStream.writeline(objRecordset.Fields("Name").Value & ","_
& text & ","_
& objRecordset.Fields("accountExpires").Value & ","_
)
objRecordSet.MoveNext
logStream.Close
Loop
'logStream.Close
objConnection.Close
Set objRecordSet = Nothing
Set objCommand = Nothing
Set objConnection = Nothing
==================================================================
Help is appreciated
All Replies
Your solution requires binding to all user objects in AD. Instead you can query for just the user objects where the account expires in the next 60 days. My solution follows:
===========
' AcctExp.vbsOption Explicit
Dim adoCommand, adoConnection, strBase, strFilter, strAttributes
Dim objRootDSE, strDNSDomain, strQuery, adoRecordset
Dim dtmDate1, dtmDate2, intDays, strName, strDN
Dim lngSeconds1, str64Bit1, lngSeconds2, str64Bit2
Dim objShell, lngBiasKey, lngBias, k
Dim objDate, dtmExpires' Number of days in future.
intDays = 60' Find date this may days in the future.
dtmDate1 = Now()
dtmDate2 = DateAdd("d", intDays, dtmDate1)' Retrieve DNS name of domain.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("DefaultNamingContext")' 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 the datetime values to UTC.
dtmDate1 = DateAdd("n", lngBias, dtmDate1)
dtmDate2 = DateAdd("n", lngBias, dtmDate2)' Find number of seconds since 1/1/1601 for these dates.
lngSeconds1 = DateDiff("s", #1/1/1601#, dtmDate1)
lngSeconds2 = DateDiff("s", #1/1/1601#, dtmDate2)' Convert the number of seconds to a string
' and convert to 100-nanosecond intervals.
str64Bit1 = CStr(lngSeconds1) & "0000000"
str64Bit2 = CStr(lngSeconds2) & "0000000"' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = adoConnection' Search entire Active Directory domain.
strBase = "<LDAP://" & strDNSDomain & ">"' Filter on user objects where the account expires between the
' dates specified.
strFilter = "(&(objectCategory=person)(objectClass=user)" _
& "(accountExpires>=" & str64Bit1 & ")" _
& "(accountExpires<=" & str64Bit2 & "))"' Comma delimited list of attribute values to retrieve.
strAttributes = "sAMAccountName,distinguishedName,accountExpires"' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False' Run the query.
Set adoRecordset = adoCommand.Execute' Enumerate the resulting recordset.
Do Until adoRecordset.EOF
' Retrieve values.
strName = adoRecordset.Fields("sAMAccountName").Value
strDN = adoRecordset.Fields("distinguishedName").Value
' Determine when account expires.
Set objDate = adoRecordset.Fields("accountExpires").Value
dtmExpires = Integer8Date(objDate, lngBias)
Wscript.Echo strName & "," & strDN & "," & CStr(dtmExpires)
' Move to the next record in the recordset.
adoRecordset.MoveNext
Loop' Clean up.
adoRecordset.Close
adoConnection.CloseFunction Integer8Date(ByVal objDate, ByVal lngBias)
' Function to convert Integer8 (64-bit) value to a date, adjusted for
' local time zone bias.
Dim lngAdjust, lngDate, lngHigh, lngLow
lngAdjust = lngBias
lngHigh = objDate.HighPart
lngLow = objDate.LowPart
' Account for error in IADsLargeInteger property methods.
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
' Trap error if lngDate is ridiculously huge.
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 0End Function
=========
My version requires that you run the script at a command prompt and redirect the output to a text file. The values are comma delimited. You can revise to use the FileSystemObject, and to output any attributes of the users desired. Note that in your version you attempt to output the value of the accountExpires attribute. This attribute is data type Integer8, which is a large 64-bit number that must be converted into a date. I hope this helps. For more discussion on accountExpires and the AccountExpirationDate property method, see this link:http://www.rlmueller.net/AccountExpires.htm
Richard Mueller
MVP ADSI- Hello Richard, Thank you very much for assisting me. I tested the script,but the script list out those users as well whose accounts are going to epire in less than 2 months. Please help me in modifyoing the script to get the o/p of the users whose account gets expired in next 2 months regards Naresh
- I'm not sure I understand. If this month is November, perhaps you want all users whose accounts expire in December and January, but not in November. If so, one option would be to hardcode the critical dates in the script. For example:
dtmDate1 = #12/01/2009#
dtmDate2 = #01/31/2010#
The program will find all users whose accounts expire between the two dates. Note that date literals are conclosed in "#" characters in VBScript. Also, this assumes your computer regional settings define dates as mm/dd/yyyy. If you want the program to calculate the critical dates based on the current date, then perhaps you can use:
=======
dtmDate1 = DateSerial(Year(Now()), Month(Now()), 1)
dtmDate1 = DateAdd("m", 1, dtmDate1)
dtmDate2 = DateAdd("m", 2, dtmDate1)
dtmDate2 = DateAdd("d", -1, dtmDate2)
=====
If you run this code today, then dtmDate1 will be the first day of December and dtmDate2 will the the last day of January. I hope this helps.
Richard Mueller
MVP ADSI
