From: JohnCee53 on
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 [MVP]" wrote:

> Sorry about that. Glad it worked.
>
> --
> Richard Mueller
> MVP Directory Services
> Hilltop Lab - http://www.rlmueller.net
> --
> "MikeD" <mike_dips(a)hotmail.com> wrote in message
> news:D3D85C9E-885F-42F6-9F30-A28D7E8946C8(a)microsoft.com...
> > 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]" wrote:
> >
> >>
> >> "MikeD" <mike_dips(a)hotmail.com> wrote in message
> >> news:5032B414-BCF1-4BE9-883E-8A3655209ACF(a)microsoft.com...
> >> > 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"
> >>
> >> --
> >> Richard Mueller
> >> MVP Directory Services
> >> Hilltop Lab - http://www.rlmueller.net
> >> --
> >>
> >>
> >>
>
>
>
From: Richard Mueller [MVP] on

"JohnCee53" <JohnCee53(a)discussions.microsoft.com> wrote in message
news:8D9060C4-2F17-42B0-9FF8-40AFF408F7E1(a)microsoft.com...
> 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 [MVP]" wrote:
>
>> Sorry about that. Glad it worked.
>>
>> --
>> Richard Mueller
>> MVP Directory Services
>> Hilltop Lab - http://www.rlmueller.net
>> --
>> "MikeD" <mike_dips(a)hotmail.com> wrote in message
>> news:D3D85C9E-885F-42F6-9F30-A28D7E8946C8(a)microsoft.com...
>> > 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.
>> >
>> snip...

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 Directory Services
Hilltop Lab - http://www.rlmueller.net
--


From: Richard Mueller [MVP] on

"Richard Mueller [MVP]" <rlmueller-nospam(a)ameritech.nospam.net> wrote in
message news:%23GpAj8dKKHA.4432(a)TK2MSFTNGP02.phx.gbl...
>
> "JohnCee53" <JohnCee53(a)discussions.microsoft.com> wrote in message
> news:8D9060C4-2F17-42B0-9FF8-40AFF408F7E1(a)microsoft.com...
>> 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 [MVP]" wrote:
>>
>>> Sorry about that. Glad it worked.
>>>
>>> --
>>> Richard Mueller
>>> MVP Directory Services
>>> Hilltop Lab - http://www.rlmueller.net
>>> --
>>> "MikeD" <mike_dips(a)hotmail.com> wrote in message
>>> news:D3D85C9E-885F-42F6-9F30-A28D7E8946C8(a)microsoft.com...
>>> > 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.
>>> >
>>> snip...
>
> 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 Directory Services
> Hilltop Lab - http://www.rlmueller.net
> --
>

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.

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