After being asked by a client to export details to Excel for their organisations users, I promptly turned to PowerShell for the task.
Certain caveats were included with this request such as Display name, Last Logon, all nothing out of the ordinary however, I hit a wall when it came down to only extracting data for specific OU’s. After a little trial and error modifying other scripts I had used I turned to Google for help and after a wasted afternoon trying other peoples scripts and butchering them I gave up.
New day same challenge. The only thing to do was to write it from scratch.
First off you will need the Management Snapin for AD from Quest http://www.quest.com/QuestWebPowershellCmdletDwnld64bit
For those of you not familiar with Quest tools, don’t worry it is just a set of powerful tools that integrates into PowerShell nicely.
The second thing to bear in mind is that I am running this on a Windows 7 machine but I have also tested this on 2012.
OK, so you’ve installed the Quest tools, now open up PowerShell ISE by right clicking the PowerSehll icon on the taskbar and Run ISE as Administrator. This opens up the ISE which will allow you to write and modify your script in the scripting pane before running it using the Play button found on the toolbar. You could if you wish type this direct into the PowerShell window but I think its nice to be able to see what your coding and tidy it before you run it and tie up resources on the DC’s.
Before you start coding the script you need to first register the PowerShell Snapin and add the Quest Snapin. You can do this direct into the PS Pane
Get-PSSnapin -Registered
Add-PSSnapin Quest.ActiveRoles.ADManagement
Now if you run the script as is you will hit a limit of objects returned which by default is 1000, so if your environment is likely to hold more users than that, add the below to remove the limit.
Set-QADPSSnapinSettings -DefaultSizeLimit 0
Now to write the script itself. In the scripting pane you can write the bones of your PowerShell Script
$Userlist = Get-QADUser -SearchRoot “OU=nameofou,OU=nameofou2,DC=domain,DC=name”
# If the users you require data on are in the OU “IT” which is nested in “Departments” and your domain is named “scripts.com” your search root would look like this -SearchRoot “OU=IT,OU=Departments,DC=Scripts,DC=com”
$Report = @()
Foreach($User in $Userlist){
$Userdata = Get-QADUser -Identity $User |
Select FirstName, LastName, DisplayName, SAMAccountName, LastLogon, Office
# Here you can define the attributes you wish to collect data from. The ones I was particularly interested was SAMAccountName and LastLogon but you can add what ever attribute you like, provided its supported of course! If you unsure of which attributes to select from have a look at the attributes of a user object using ADSI Edit.
$Report += $Userdata
}
$Report | Export-Csv -Path c:\logontimes.csv -NoTypeInformation
#Self explanatory path and file name of your choosing.
Hit the Play Button on the Command Toolbar to execute your script. Don’t worry if it takes a little time to complete, the first time I ran it with a user base of 3500+ users it took a good 10 minutes to complete.
Once complete it will create a nice little CSV with the extracted data you requested. Any user accounts that haven’t logged on will be blank. I know some of you wont trust this blank box especially when it comes to using the data to delete or disable accounts but you can check the account to be sure. Don’t trust the AD Users and Computers management console as when you open up a user properties and look at the object create and modified dates this is often incorrect. Instead use a simple PowerShell Script on a DC, no Quest tools required.
Import-Module ActiveDirectory
function Get-ADUserLastLogon([string]$userName)
{
$dcs = Get-ADDomainController -Filter {Name -like “*”}
$time = 0
foreach($dc in $dcs)
{
$hostname = $dc.HostName
$user = Get-ADUser $userName | Get-ADObject -Server $hostname -Properties lastLogon
if($user.LastLogon -gt $time)
{
$time = $user.LastLogon
}
}
if($time -ne 0)
{
$dt = [DateTime]::FromFileTime($time)
return $dt
}
Write-Host $username “last logged on at:” $dt }
Get-ADUserLastLogon -UserName myuser #myuser being the account name of the user you want to check
If the user has never logged on this should return a blank timestamp.
That’s it, hopefully this will help someone out but this isn’t the only way to extract data from AD and I welcome any suggestions on improving it.
WorldOfCrop 