Summary: Microsoft PowerShell MVP, Sean Kearney, shows how to use Windows PowerShell to audit account creation in Active Directory. Microsoft Scripting Guy, Ed Wilson, is here. Now, with the exciting conclusion to Windows PowerShell Blueville, here is Microsoft PowerShell MVP, Sean Kearney. So hear me all now, and hear me all true,
The lessons that came, from the workers all Blue,
All five of them told, of that I am sure,
Too many to count, so many they were. First there was Stu, we saw in one line,
Unlocked whole divisions, in barely no time. Then we had Hugh, who as quick as can be,
Showed how to store users in one CSV. Don’t forget Sue, a spark in her eye,
Showed how to use strings right on the fly. Oh, then Boo and then Lou, right down on Tier 4,
Showed how to create en masse, and oh so much more. So now Mr. Finch was about to sit down,
And play with that PowerShell, such like a clown,
When a BAM came away upon his wood door,
A BAM so darn hard it rumbled the floor. He opened the door to go take a peek,
Pushing it slowly with barely a creak. When in burst a team, briefcases in hand,
Of auditors…nine from compliance land. “We come from Frank Orgen and Xylonite now,
For your quartlerly SOX,” as one took a bow.
”We need a report before we have lunch,
Of all your new users, and not in a bunch.
Please format it nice, please keep it neat,
CSV output cannot be beat,
All new staff you hired from April ‘til June,
Please hurry up, you have until noon. They marched out that door, with no moments to spare.
Those Nazguls of audit, leaving foul rancid air. “Oh no, can it be? At ten in the morn?
F.O.X here for SOX?” He felt so forlorn. But then Mr. Finch, had a thought like a light,
”Get them Blues up here! They’ll make this take flight!
If PowerShell could solve so much in a fly,
Perhaps it could be used for this, worth a try!” And so he rang down, to the Blues down below,
To call up his friends, all five don’t ya know? They arrived up in moments, like huns from Ben Hur,
To battle the task, they moved like a whir .
But they all had to pause, with a twitch and a quirk,
”This rhyming must stop before we can work!” And so a few moments were spent with a crate,
Filling rhyming machines, sealing their fate.
It was bolted right shut and topped with a bow,
”It should look nice!” said Sue, oh don’t ya know? “I’ll get those out of here by courier,” said Mr. Finch. “Maybe ship them to a company that makes pads from fruit.” “So Mr. Finch,” popped up Sue. “What’s the dilly?” “Auditors!” he burst out, exploding on the wall. “Auditors! They want a report of all the new staff in Active Directory!” “…and?” queried Stu, “where’s the challenge?” “You do know that’s piece of cake,” offered Lou. “….in Windows PowerShell,” completed Boo. “Yes, we can help you do that. That’s easier than you can imagine!” brightened Hugh. Mr. Finch paused and breathed a sigh. Somehow he suspected they would say something like that. He smiled. “So where do we start?” “First we need to see if there are any fields with the word Date available in Active Directory. Hopefully there should be one or a few. I’m pretty certain it stamps the date of object creation in everything in Active Directory. I’ll pick on Lou for this,” piped up Boo.

GET-QADUSER ‘Lou Blue’ | SELECT-Object *date* They looked and noticed two fields appearing from Active Directory: ModificationDate and CreationDate. “Is there any way to compare this with the current date easily?” queried Mr. Finch. “Sure is,” offered Stu. “In Windows PowerShell, there is a cmdlet called Get-Date, which by default, will give us the current date and time. But all we need to compare for you are two specific dates: April 1, 2011 and June 30, 2011. This is the range for your quarter. There are a lot of ways that we can set up this information, but we can use GET-DATE and Windows PowerShell variables to store that away. For April, we enter the following:”

$BeginDate=GET-DATE ‘4/1/2011’ Mr. Finch looked up “Oh! And would this work for the ending date?” He typed:

$EndDate=GET-DATE ‘6/30/2011’ They all smiled. Mr. Finch was getting the hang of this. “But we should set the ending date to July 1 so we can collect all new staff that were created on June 30 as well.”

$EndDate=GET-DATE ‘7/1/2011’ “Next we obtain a list of Users in your Active Directory. For that, we run the Get-QADUser cmdlet just like before, and we’ll store the information in a variable.”

$OurUsers=GET-QADUSER Hugh looked up. “There are many ways we can do this, but we can use the Where-Object cmdlet. It allows you to filter information that is coming from other cmdlets. To compare the CreationDate to see if it is later than or greater than the $BeginDate, we would type:

$OurUsers | WHERE-Object { $_.CreationDate –gt $BeginDate } A stream of all users that were created since April 1, 2011 flowed down the screen. “As you can see we can compare by using –gt which means ‘greater than.’ We can use –lt, which means ‘less than’ to compare with the other date,” offered Boo.

$OurUsers | WHERE-Object { $_.CreationDate –lt $EndDate} Mr. Finch scratched his head. “OK, I can almost see the picture, but how do we tie it together?” “We can use the operator –and like this:”

$OurUsers | WHERE-Object { $_.CreationDate –gt $BeginDate –and $_.CreationDate –lt $EndDate } A much smaller list appeared on the screen. It was all the staff from that quarter. “Waaaaait a minute. The auditors wanted this in a CSV format. Would this work the same way as when I exported the users from Contoso?” Mr. Finch Wondered aloud.

$OurUsers | WHERE-Object { $_.CreationDate –gt $BeginDate –and $_.CreationDate –lt $EndDate } | EXPORT-CSV C:AuditNewStaff.csv He was opening the CSV file to verify the data. He sorted on the CreationDate in Excel, and sure enough, it was all staff that was created between April 1, 2011 and July 1, 2011. He even noted that it showed the new user created on June 30, 2011 at 11:45pm. “So how could I save this for regular use? This seems like something I would love to have handy.” Sue jumped in, “Just key the lines into any text editor and save them with an extension of .PS1” Mr. Finch grouped all of the following lines together:

$BeginDate=GET-DATE ‘4/1/2011’
$EndDate=GET-DATE ‘7/1/2011’
$OurUsers=GET-QADUSER
$OurUsers | WHERE-Object { $_.CreationDate –gt $BeginDate –and $_.CreationDate –lt $EndDate } | EXPORT-CSV C:AuditNewStaff.csv He quickly saved them as a file called SOX.PS1. “This will be handy the next time they pop around,” he smiled with glee. No sooner had they finished the report and copied it to a USB key when the door opened and the nine auditors from F.O.X. walked in. “Do you have our SOX?” asked the auditors from F.O.X. Mr. Finch and the Blues smiled, handing them the USB key with the needed data. The nine spun about as quickly as they had entered. “And now my friends…the best way I can thank you all. Now that we are rid of F.O.X. and the SOX, would you please take this crate of RhymeOMatics and smash them on some rocks?” The five blues laughed with glee, hauling the box of irritating things away, patting Mr. Finch on the back as they did. Mr. Finch looked online quickly to purchase a book about Windows PowerShell. He quickly ordered “PowerShell Step-by-Step” to get him going. And so Mr. Finch was off on his way,
Learning more scripting each little day.
With the help of new friends, those wonderful Blues,
He learned to use PowerShell, without any dues.
It could do in one line, the work needed be,
Or possibly script in two or in three.
So on that day, Mr. Finch moved down unto
The far place below, with everyone Blue.
In a land full of screens of blue gleaming bright,
He worked long with the Blues into the night.
Learning and spreading the PowerShell way,
He went home with a smile each every day. Thank you, Sean, for such a great week at Blueville. Join us tomorrow as Gary Siepser starts the weekend as our guest blogger. It is cool. I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace. Ed Wilson, Microsoft Scripting Guy