{"id":64313,"date":"2007-08-03T01:16:00","date_gmt":"2007-08-03T01:16:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/08\/03\/how-can-i-get-a-list-of-user-accounts-created-on-or-between-two-dates\/"},"modified":"2007-08-03T01:16:00","modified_gmt":"2007-08-03T01:16:00","slug":"how-can-i-get-a-list-of-user-accounts-created-on-or-between-two-dates","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-get-a-list-of-user-accounts-created-on-or-between-two-dates\/","title":{"rendered":"How Can I Get a List of User Accounts Created On Or Between Two Dates?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I query Active Directory for a list of all the accounts that were created on or between two dates?

— PG <\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, PG. You know, August is a propitious month for the Scripting Guys and this column: after all, today\u2019s edition marks the beginning of the fourth year of Hey, Scripting Guy!<\/I> Our very first column debuted on August 2, 2004, and \u2013 other than an occasional holiday or vacation day \u2013 we\u2019ve run a new column every weekday ever since. How many columns does that add up to? Well, you do the math.<\/P>\n

No, seriously: you\u2019ll have to do the math. We kind of lost track of things after the 500<\/B>th<\/SUP> column<\/B><\/A> appeared last year.<\/P>\n

Of course, that brings up an obvious question. \u201cGosh, after writing all those columns you must have some pretty interesting stories to tell, don\u2019t you?\u201d And you\u2019d think so, wouldn\u2019t you? As it turns out, however, we don\u2019t have any<\/I> stories to tell. Instead, all we have is a script that lists all the Active Directory user accounts that were created on or between two dates:<\/P>

On Error Resume Next<\/p>\n
Const ADS_SCOPE_SUBTREE = 2<\/p>\n
dtmCreationDate1 = “20070701000000.0Z”\ndtmCreationDate2 = “20070731000000.0Z”<\/p>\n
Set objConnection = CreateObject(“ADODB.Connection”)\nSet objCommand =   CreateObject(“ADODB.Command”)\nobjConnection.Provider = “ADsDSOObject”\nobjConnection.Open “Active Directory Provider”\nSet objCommand.ActiveConnection = objConnection<\/p>\n
objCommand.Properties(“Page Size”) = 1000\nobjCommand.Properties(“Searchscope”) = ADS_SCOPE_SUBTREE <\/p>\n
objCommand.CommandText = _\n    “SELECT Name, whenCreated FROM ‘LDAP:\/\/dc=fabrikam,dc=com’ WHERE objectClass=’user’ ”  & _\n        “AND whenCreated>='” & dtmCreationDate1 & “‘ AND whenCreated<='” & dtmCreationDate2 & “‘” \nSet objRecordSet = objCommand.Execute<\/p>\n
objRecordSet.MoveFirst<\/p>\n
Do Until objRecordSet.EOF\n    Wscript.Echo objRecordSet.Fields(“Name”).Value, objRecordSet.Fields(“whenCreated”).Value\n    objRecordSet.MoveNext\nLoop\n<\/PRE>\n
We know that many of you are concerned that, after four years, Hey, Scripting Guy!<\/I> might begin to stray from its original intention (which is entirely possible considering the fact that none of us can remember what its original intention even was). But take it from the Scripting Guys: you have nothing to worry about there. If you need proof, try this: as we\u2019ve consistently done for the last 3 years, today\u2019s column on searching Active Directory includes the following disclaimer:<\/P>\n\n<\/THEAD>\n\n\n
\n
We won\u2019t discuss the ins and outs of searching Active Directory; that goes beyond what we can do in this column. But don\u2019t worry: we would never leave you hanging like that, wondering how an Active Directory search script works. If you need (or want) more information on the principles behind Active Directory search scripts, take a look at our two-part Tales from the Script<\/I> series Dude: Where\u2019s My Printer?<\/B><\/A><\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
See? We\u2019re every bit as consistent \u2013 and lazy \u2013 as we\u2019ve always been.<\/P>\n
What we will<\/I> talk about today is the SQL query we use to retrieve a collection of all the user accounts that were created between two dates (in this case, July 1, 2007 and July 31, 2007). Before we do that, however, let\u2019s take a quick peek at the following two lines of code:<\/P>
dtmCreationDate1 = “20070701000000.0Z”\ndtmCreationDate2 = “20070731000000.0Z”\n<\/PRE>\n
Believe it or not, these two lines of code assign our start date (July 1, 2007) and our end date (July 31, 2007) to the variables dtmCreationDate1 and dtmCreationDate2. Granted, these don\u2019t really look<\/I> like dates; that\u2019s because of the date-time format (\u201cgeneralized time\u201d) required when doing an Active Directory search. Trust us, however; the value 20070701000000.0Z can be broken down like this:<\/P>\n\n\n\n\n\n\n\n
\u2022<\/TD>\n\n
The 2007<\/B> represents the year.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The 07<\/B> represents the month (July), in two-digit format.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The 01<\/B> represents the day (the first), also in two-digit format.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The 000000<\/B> represents, respectively, the hour, minutes, and seconds, all in two-digit, 24-hour format. (For example, 8:35 AM would be coded as 083500.)<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The .0Z<\/B> represents the time differential between local time and Greenwich Mean Time. Because Active Directory stores date-time values using Greenwich Mean Time, we\u2019ve left this offset at 0. If we wanted to specify an offset 8 hours later than Greenwich Mean Time we\u2019d do something like this: .0+0800. Eight hours earlier than Greenwich Mean Time would result in this: .0-0800 (note the minus sign). Etc.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
Now that we know what our two variables are for we can turn our attention to the SQL query that returns all the user accounts created on or between July 1, 2007 and July 31, 2007:<\/P>
objCommand.CommandText = _\n    “SELECT Name, whenCreated FROM ‘LDAP:\/\/dc=fabrikam,dc=com’ WHERE objectClass=’user’ ”  & _\n        “AND whenCreated>='” & dtmCreationDate1 & “‘ AND whenCreated<='” & dtmCreationDate2 & “‘” \nSet objRecordSet = objCommand.Execute\n<\/PRE>\n
What are we doing here? What we\u2019re doing is requesting two attribute values (Name<\/B> and whenCreated<\/B>) from the fabrikam.com domain. Of course, we don\u2019t want this information for all<\/I> the objects in the fabrikam.com domain; instead, we only want those objects that meet the following criteria:<\/P>\n\n\n\n\n\n
\u2022<\/TD>\n\n
The objectClass<\/B> is equal to \u201cuser\u201d. What if we wanted a list of computer accounts created during the month of July? No problem; just change \u201cuser\u201d to \u201ccomputer\u201d. What if we wanted a list of all the accounts (regardless of objectClass) created during the month of July? Again, no problem; in that case, just leave out the objectClass= clause altogether.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The value of the object\u2019s whenCreated attribute must be greater than or equal to July 1, 2007 (represented by the variable dtmCreationDate1).<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The value of the object\u2019s whenCreated attribute must be less than or equal to July 31, 2007 (represented by the variable dtmCreationDate2).<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
If you follow the logic (and, yes, this might be the first time in four years that one of these columns has actually had<\/I> some logic) you\u2019ll see that this limits the returned data to user accounts created during July, 2007. Which is exactly what we wanted the returned data to be limited to.<\/P>\n
In case you don\u2019t trust us, we added a Do Until loop that runs until we reach the end of our recordset (that is, the end of the returned data). Inside that loop we echo back the value of the first object\u2019s Name and whenCreated attributes, then use the MoveNext<\/B> method to move on and repeat the process with the next item in the recordset:<\/P>
Do Until objRecordSet.EOF\n    Wscript.Echo objRecordSet.Fields(“Name”).Value, objRecordSet.Fields(“whenCreated”).Value\n    objRecordSet.MoveNext\nLoop\n<\/PRE>\n
The net result? Something very similar to this:<\/P>
Ken Myer 7\/31\/2007 6:38:40 PM\nPilar Ackerman 7\/29\/2007 7:22:06 PM\nJonathan Haas 7\/26\/2007 7:28:42 PM\nGail Erickson 7\/24\/2007 7:45:14 PM\nCarol Phillips 7\/17\/2007 9:55:16 PM\nKim Abercrombie 7\/14\/2007 6:11:31 PM\nDylan Miller 7\/06\/2007 3 :29:44 PM\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. That\u2019s a good point: even though we had to use that crazy generalized time format to specify values for the whenCreated attribute we didn\u2019t have to do anything to get real<\/I> date-time values (e.g., 7\/26\/2007 7:28:42 PM) displayed on screen. As it turns out, we only have to use the generalized time format when querying Active Directory; when we echo back the value of the whenCreated attribute the value is automatically converted into something a little easier to read.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
And there you have it, PG: all the user accounts created on or between July 1, 2007 and July 31, 2007.<\/P>\n
Before we go, we\u2019d like to add that we are reasonably proud of Hey, Scripting Guy!<\/I> and its longevity; we daresay that no other daily column published on TechNet has been in business as long as we have. (Due, in large part, to the fact that no one but the Scripting Guys are dumb enough to publish a daily column on TechNet.) So then why aren\u2019t we holding a birthday party in honor of the occasion? That\u2019s easy: we had a big celebration a year ago when the historic 500<\/B>th<\/SUP> column<\/B><\/A> was published. For the Scripting Guys, one party every four years or so is about all we can handle.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Well, except for the Scripting Editor, of course, who parties pretty much non-stop; she doesn\u2019t even know the meaning<\/I> of the word \u201cquit.\u201d (Which is too bad, because we\u2019ve been trying to get her to quit for almost 3 years now, and with no such luck.)<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I query Active Directory for a list of all the accounts that were created on or between two dates?— PG Hey, PG. You know, August is a propitious month for the Scripting Guys and this column: after all, today\u2019s edition marks the beginning of the fourth year of Hey, Scripting […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[7,3,20,5],"class_list":["post-64313","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-active-directory","tag-scripting-guy","tag-user-accounts","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I query Active Directory for a list of all the accounts that were created on or between two dates?— PG Hey, PG. You know, August is a propitious month for the Scripting Guys and this column: after all, today\u2019s edition marks the beginning of the fourth year of Hey, Scripting […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64313","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=64313"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64313\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=64313"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64313"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64313"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}