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

  • Thread starter Thread starter MikeD
  • Start date Start date
M

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
 
MikeD said:
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
Click to expand...

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"
 
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.
 
Sorry about that. Glad it worked.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
MikeD said:
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.
Click to expand...
 
Along the same lines as this, but I need to go back for the last 7 days. How would this change the script?
 
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.

Richard Mueller said:
Sorry about that. Glad it worked.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
MikeD said:
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.
Click to expand...
Click to expand...
 
JohnCee53 said:
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.
Click to expand...

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 said:
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.
Click to expand...

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.
 
Back
Top