CSVDE export of users: how to make whenCreated query the currentda

[MikeD]

I'm exporting users in AD using CSVDE command line base on a manual query
where user is created Today.

QUESTION:
How to make the whenCreated query to use the current date without hardcoding
the day?

Sample Code:
CSVDE -f %date:~-4,4%%date:~-7,2%%date:~-10,2%.csv
"(&(objectClass=user)(whenCreated>=20090504000000.0Z)(whenCreated<=20090505000000.0Z))" -l "employeeID, sn, givenName, sAMAccountName"

where:
%date:~-4,4%%date:~-7,2%%date:~-10,2%.csv = today's date.csv (e.g.
20090504.csv)
(objectClass=user) = AD query will return user
(whenCreated>=20090504000000.0Z)(whenCreated<=20090505000000.0Z) = AD
query created today May 4, 2009
employeeID, sn, givenName, sAMAccountName = AD attributes that will be
exported in the .CSV file

 

[Richard Mueller [MVP]]

I'm exporting users in AD using CSVDE command line base on a manual query
where user is created Today.

QUESTION:
How to make the whenCreated query to use the current date without
hardcoding
the day?

Sample Code:
CSVDE -f %date:~-4,4%%date:~-7,2%%date:~-10,2%.csv
"(&(objectClass=user)(whenCreated>=20090504000000.0Z)(whenCreated<=20090505000000.0Z))"
-l "employeeID, sn, givenName, sAMAccountName"

where:
%date:~-4,4%%date:~-7,2%%date:~-10,2%.csv = today's date.csv (e.g.
20090504.csv)
(objectClass=user) = AD query will return user
(whenCreated>=20090504000000.0Z)(whenCreated<=20090505000000.0Z) = AD
query created today May 4, 2009
employeeID, sn, givenName, sAMAccountName = AD attributes that will be
exported in the .CSV file

How about this (watch line wrapping, this is one line):

CSVDE -f %date:~-4,4%%date:~-10,2%%date:~-7,2%.csv
"(&(objectClass=user)(whenCreated>=%date:~-4,4%%date:~-10,2%%date:~-7,2%000000.0Z)(whenCreated<=%date:~-4,4%%date:~-10,2%%date:~-7,2%235959.0Z))"
-l "employeeID, sn, givenName, sAMAccountName"

 

[MikeD]

Thanks Richard.
I updated your recommendation because you missed a switch. Here is the
corrected CSVDE command line which is awesomely working now.

CSVDE -f -%date:~-10,2%_%date:~-7,2%_%date:~-4,4%.csv -r
"(&(objectClass=user)(whenCreated>=%date:~-4,4%%date:~-10,2%%date:~-7,2%000000.0Z)(whenCreated<=%date:~-4,4%%date:~-10,2%%date:~-7,2%235959.0Z))"
-l "employeeID, sn, givenName, sAMAccountName"

I'm planning to use this on a batch file so that I can just make a schedule
tasks to run this command-line nightly.

 

[Richard Mueller [MVP]]

Sorry about that. Glad it worked.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

Thanks Richard.
I updated your recommendation because you missed a switch. Here is the
corrected CSVDE command line which is awesomely working now.

CSVDE -f -%date:~-10,2%_%date:~-7,2%_%date:~-4,4%.csv -r
"(&(objectClass=user)(whenCreated>=%date:~-4,4%%date:~-10,2%%date:~-7,2%000000.0Z)(whenCreated<=%date:~-4,4%%date:~-10,2%%date:~-7,2%235959.0Z))"
-l "employeeID, sn, givenName, sAMAccountName"

I'm planning to use this on a batch file so that I can just make a
schedule
tasks to run this command-line nightly.

 

[JohnCee53]

Along the same lines as this, but I need to go back for the last 7 days. How would this change the script?

 

[JohnCee53]

How would I get this to work using the last 7 days instead of just today? The
reason I ask is that my manager has asked me to come up with a way to run a
report on Friday mornings that would show users a) disabled; b) created, and
c) enabled during the last 7 days. I've already tested the above code and
tailored it for my use. If nothing else I will manually merge the last 7 days
reports in to at least two reports for created and disabled users for the
last 7 days.

Sorry about that. Glad it worked.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

Thanks Richard.
I updated your recommendation because you missed a switch. Here is the
corrected CSVDE command line which is awesomely working now.

CSVDE -f -%date:~-10,2%_%date:~-7,2%_%date:~-4,4%.csv -r
"(&(objectClass=user)(whenCreated>=%date:~-4,4%%date:~-10,2%%date:~-7,2%000000.0Z)(whenCreated<=%date:~-4,4%%date:~-10,2%%date:~-7,2%235959.0Z))"
-l "employeeID, sn, givenName, sAMAccountName"

I'm planning to use this on a batch file so that I can just make a
schedule
tasks to run this command-line nightly.

 

[Richard Mueller [MVP]]

How would I get this to work using the last 7 days instead of just today?
The
reason I ask is that my manager has asked me to come up with a way to run
a
report on Friday mornings that would show users a) disabled; b) created,
and
c) enabled during the last 7 days. I've already tested the above code and
tailored it for my use. If nothing else I will manually merge the last 7
days
reports in to at least two reports for created and disabled users for the
last 7 days.

I wouldn't know how to modify the command line for CSVDE to do what you
want, but you can get similar results from a VBScript program. The example
above spits out values of employeeID, sn, givenName, and sAMAccountName for
all user objects where the whenCreated attribute is between the beginning of
the day 7 days ago and the end of today:
=============
Option Explicit

Dim objRootDSE, strDNSDomain, adoConnection
Dim strBase, strFilter, strAttributes, strQuery, adoRecordset
Dim dtmStart, dtmEnd, strStart, strEnd
Dim strID, strFirst, strLast, strNTName

dtmEnd = Now()
dtmStart = DateAdd("d", -7, dtmEnd)
strStart = CStr(Year(dtmStart)) _
& Right("0" & CStr(Month(dtmStart)), 2) _
& Right("0" & CStr(Day(dtmStart)), 2) & "000000.0Z"
strEnd = CStr(Year(dtmEnd)) _
& Right("0" & CStr(Month(dtmEnd)), 2) _
& Right("0" & CStr(Day(dtmEnd)), 2) & "235959.0Z"

' Determine DNS domain name.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

' Use ADO to search Active Directory.
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"

Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.ActiveConnection = adoConnection

' Search entire domain.
strBase = "<LDAP://" & strDNSDomain & ">"

strFilter = "(&(objectCategory=person)(objectClass=user)" _
& "(whenCreated>=" & strStart & ")(whenCreated<=" & strEnd & "))"

' Comma delimited list of attribute values to retrieve.
strAttributes = "employeeID,sn,givenName,sAMAccountName"

' Construct the LDAP query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"

' Run the query.
adoRecordset.Source = strQuery
adoRecordset.Open

' Enumerate the resulting recordset.
Do Until adoRecordset.EOF
' Retrieve values.
strID = adoRecordset.Fields("employeeID").Value
strLast = adoRecordset.Fields("sn").Value
strFirst = adoRecordset.Fields("givenName").Value
strNTName = adoRecordset.Fields("sAMAccountName").Value
Wscript.Echo """" & strID & """,""" & strLast & """,""" & strFirst
& """,""" & strNTName & """"
adoRecordset.MoveNext
Loop

' Clean up.
adoRecordset.Close
adoConnection.Close
==========
The above should be run at a command prompt using the cscript host so the
output can be redirected to a text file. For example, if the VBScript
program is saved in the file FindUsers.vbs:

cscript //nologo FindUsers.vbs > users.csv

This assumes you are in the directory where the file FindUsers.vbs is saved.
Otherwise, you must specify the full path. Note this will not reveal cases
where users are disabled or enabled in the last 7 days, only created.

 

[Richard Mueller [MVP]]

I wouldn't know how to modify the command line for CSVDE to do what you
want, but you can get similar results from a VBScript program. The example
above spits out values of employeeID, sn, givenName, and sAMAccountName
for all user objects where the whenCreated attribute is between the
beginning of the day 7 days ago and the end of today:
=============
Option Explicit

Dim objRootDSE, strDNSDomain, adoConnection
Dim strBase, strFilter, strAttributes, strQuery, adoRecordset
Dim dtmStart, dtmEnd, strStart, strEnd
Dim strID, strFirst, strLast, strNTName

dtmEnd = Now()
dtmStart = DateAdd("d", -7, dtmEnd)
strStart = CStr(Year(dtmStart)) _
& Right("0" & CStr(Month(dtmStart)), 2) _
& Right("0" & CStr(Day(dtmStart)), 2) & "000000.0Z"
strEnd = CStr(Year(dtmEnd)) _
& Right("0" & CStr(Month(dtmEnd)), 2) _
& Right("0" & CStr(Day(dtmEnd)), 2) & "235959.0Z"

' Determine DNS domain name.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

' Use ADO to search Active Directory.
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"

Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.ActiveConnection = adoConnection

' Search entire domain.
strBase = "<LDAP://" & strDNSDomain & ">"

strFilter = "(&(objectCategory=person)(objectClass=user)" _
& "(whenCreated>=" & strStart & ")(whenCreated<=" & strEnd & "))"

' Comma delimited list of attribute values to retrieve.
strAttributes = "employeeID,sn,givenName,sAMAccountName"

' Construct the LDAP query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"

' Run the query.
adoRecordset.Source = strQuery
adoRecordset.Open

' Enumerate the resulting recordset.
Do Until adoRecordset.EOF
' Retrieve values.
strID = adoRecordset.Fields("employeeID").Value
strLast = adoRecordset.Fields("sn").Value
strFirst = adoRecordset.Fields("givenName").Value
strNTName = adoRecordset.Fields("sAMAccountName").Value
Wscript.Echo """" & strID & """,""" & strLast & """,""" & strFirst
& """,""" & strNTName & """"
adoRecordset.MoveNext
Loop

' Clean up.
adoRecordset.Close
adoConnection.Close
==========
The above should be run at a command prompt using the cscript host so the
output can be redirected to a text file. For example, if the VBScript
program is saved in the file FindUsers.vbs:

cscript //nologo FindUsers.vbs > users.csv

This assumes you are in the directory where the file FindUsers.vbs is
saved. Otherwise, you must specify the full path. Note this will not
reveal cases where users are disabled or enabled in the last 7 days, only
created.

To find all users created or modified in the last 7 days you can use the
modifyTimeStamp attribute. The whenChanged attribute doesn't work because it
is not replicated (you would need to query all DC's in the domain). The
modifyTimeStamp attribute is replicated, although it is also operational.
However ADO, as used in my example script, forces AD to calculate the value.
Substitute modifyTimeStamp for whenCreated in the script I posted.

 

[JohnCee53]

Thanks so much! That worked perfectly!