CSVDE export of users: how to make whenCreated query the curre
in [Active Directory]
|
From: JohnCee53 on 28 Aug 2009 14:14 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 30 Aug 2009 22:02 "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 30 Aug 2009 22:26 "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 --
|
Pages: 1 Prev: Upgrading from windows 2003 to windows 2008. Next: 92 Opinion Dsiconsolas.com |