I am a frequent visitor of Experts-Exchange.com, I have seen many questions people are asking which are related to bulk management, for example; Exporting User details from AD and they need some specific data only, Exporting Exchange 2010 mailbox details.
Mostly this kind of questions comes up when administrators need to make some kind of report or analyze the infrastructure usage.
So I thought of putting simple 1 liner scripts together as per my knowledge.
Here we will try to get a report on some specific user details from AD. I am using Windows Server 2012 for the purpose of this demonstration and its going to very brief from very basics.
Lets start by Launching ISE by Right click on PowerShell icon and select “Run ISE as Administrator”. Yes, its important to run ISE as Administrator.
You will be presented with a window similar to the one shown below. You can have a visual confirmation that you are using the console as Administrator by looking at the Title Bar which should say “Administrator”
As you can see ISE is divided into 3 panes, Scripting Pane, PowerShell Pane and Commands Pane. Scripting and Powershell is self explanatory, Commands pane is for cmdlet reference where you get list of all the commands available to use in ISE.
To achieve our goal we will use Quest AD management snapin, you can download it from http://www.quest.com/QuestWebPowershellCmdletDwnld64bit
Install the downloaded exe and restart ISE as well. The setup will allow us to add Quest AD Management Snap-In to powershell.
Run, Get-PSSnapin –Registered
You will get the name of Snap-in, once you have the name in front, to add the Snap-In
Run, Add-PSSnapin Quest.ActiveRoles.ADManagement
Now lets start using new Snap-In, Try Get-QADComputer in Powershell Pane. You will see list of all cmdlets that begins with Get Verb and QAD Noun, this is helpful to see what cmdlets you have in hand to use!.
As we will try to generate a report on users, we will use Get-QADUser cmdlet.
Try, Get-QADUser –identity tusera
tusera is a test account that I have created in AD for purpose of this demonstration.
We get some information, but we need more details about this user, lets try using Format-List at the end.
Run, Get-QADUser –Identity tusera | Format-List
We got lots of data related to one user i.e., tusera, now we will select the data that we are interested in.
Lets say we are interested in First Name, Last Name, Display Name, UserName (aka SamAccountName),Telephone Number, Last Logon Date and Time, and the list can go on.
Lets Try to retrive these specific properties only for tusera.
Get-QADUser –Identity tusera | Select FirstName, LastName, DisplayName, SAMAccountName, PhoneNumber, LastLogon
Now, this time I used Script pane to write down our 1 liner, and then click the Green Play button to run the complete script, this will give the output in Powershell pane.
Ok, so what we did is, the data we got using Get-QADUser –Identity tusera will go into Pineline ( | symbol ) and will be passed Select-Object (Select is alias for Select-Object) and we are selecting the data that we need, so its like filtering out the data we are interested in.
Note: I have used second line after | and there is no space after it, if you space and then go to second line, for sure you will get an error, and this works only when u break the line after | symbol.
If you see the output in powershell pane we got what we were looking for tusera (single user)
If we run Get-QADUser cmdlet, we get all the users in AD, but I am interested in only few users from a specific OU.
The users are located in vhlusers OU.
We will now filter out the users we are interested in.
Run, Get-QADUser –SearchRoot “OU=vhlusers,DC=vhl,DC=com”
This gives us only the users that are available in vhlusers OU.
Lets store this data in a variable, we can define a variable by using $variablename.
This time I am writing in script pane,
$Userlist = Get-QADUser –SearchRoot “OU=vhlusers,DC=vhl,DC=com”
Select the line and click on the Small Play Button which is for running the selected script.
What the 1 liner has done is, store the data into a variable named “Userlist”
Next, we will use the data stored in the variable and will find the details we are interested in for each user by using ForEach cmdlet.
$Userlist = Get-QADUser -SearchRoot “OU=vhlusers,DC=vhl,DC=com”
$Report = @()
Foreach($user in $Userlist){
$Userdata = Get-QADUser –Identity $user |
Select FirstName, LastName, DisplayName, SAMAccountName, PhoneNumber, LastLogon
$Report += $Userdata
}
$Report | FT
What we did here!.
$Report = @() – is an empty array, it’s a place holder where all the data will be collected.
Foreach($user in $Userlist) – $user is a new variable which will hold just a single user from the collection which we have in $Userlist.
This foreach loop will run for every user which is in Userlist, we know that we have 3 users in it, so it will run 3 times.
$Userdata – is a new variable that will hold the data we are interested in for the specific user which is in $user, which also means in every loop $user will have a new user and $Userdata will have data associated with that user.
It’s a bit confusing at 1st to understand :-)
$Report += $Userdata – when the data is available in $Userdata we are adding it to $Report, if we don’t then everytime the loop repeats, the $Userdata will be over written with new data, hence to avoid this we are adding it to $Report.
$Report | FT – After all the users data is collected in $Report, we are calling the variable and Piping its data to FT (Format-Table)
We got a nicely formatted data in tabular form, but still its in powershell window!.
What if we want it in Excel file?
Instead of Piping $Report to FT we will Pipe it to Export-Csv cmdlet.
Replace the last line to,
$Report | Export-Csv –Path c:\UserReport.csv –NoTypeInformation
You can run only that specific Line, as we have the data already available in $report, we don’t need to run complete script again.
We get nicely formatted data in Excel file with Headers for each column, its in .csv format, you can save As.. xlsx
Note: This is not the only way to get reporting done, you might find many different ways.
vHomeLab 
How come nobody else has commented on this!
Fantastic work Wasim. I followed your guide, and tweaked it for the attributes i needed, and it worked like a charm. Many Thanks :)
Thanks for this.. as a complete begginer this is stunning..
Agreed – this is EXTREMELY helpful to us beginners…
Hi Vaseem,
This was a very helpful script….and was able to get the report till the FT (Format-Table). But once we try to extract the same data to csv format, the file gets saved but the data size of the file shows 0kb with no data available. Not sure what would the reason be for no data available.
Hi Dinesh,
All the data is stored in a variable i.e., $report.
If $report | FT id giving displaying the required data in powershell console then $report | Export-csv -path c:\filename.cav -notypeinformation should work.
Please post what exactly you are running. Hope this helps.
Hi Vaseem,
Thanks for the reply.
Right now i am able to export the data in the csv format. The only problem faced now is i am not able to get some of the data which i am not sure if it relates to extended exchange attributes.
Example 1
Directory Name Display Name Division Region Delivery Office employeeid
Maria Galvez Galvez, Maria Canada CA Toronto 185175
Example 2
Name DisplayName Division Region physicalDeliveryOfficeName employeeid
Maria Galvez Galvez, Maria Toronto
Note:- Example 1 is the data which is manually been pulled from AD.
Example 2 is the data which is generated by powershell commands but some of the data with header of Division, Region and EmployeeId comes as blank.
Would be helpful to know if there is any way to get these data available from the powershell commands as well.
Also please let me know if there is any other exchange attributes to be used to get this details.
This helped me …….!! thanks a Lot :)
Thank you for the guide, I found it extremely helpful.
I just have two questions.
1) What command would you use to add a last computer name used or most frequently used computer? I have tried a few combinations to Select FirstName, LastName, DisplayName, SAMAccountName, PhoneNumber, LastLogon but nothing worked.
2) I am trying to export the users from a specific security group in AD. For argument’s sake lets say the group is called Internet_Expand. What tweaks will I need to do in order to get the user information exported to a csv?
Hi,
The cmdlet used in this script is Get-QADUser which does not give any details related to last logon on computer by the specific user.
To export the user details which are member of specific group has to be addressed in a bit different way.
$report=@()
$group = Read-Host "Enter Group Name"
$GroupMem = Get-ADGroupMember -Identity $group
foreach($member in $GroupMem){
$UserDetails = Get-ADUser -Identity $member.SamAccountName -Properties * | select Name, GivenName, SurName, SamAccountname, mail, @{N="LastLogon";E={[DateTime]::FromFileTime($_.lastLogon)}}
$report += $UserDetails
}
$report | FT
$report | Export-Csv -Path "i:\$group-MemberDetails.csv" -NoTypeInformation
Note that in the script above I am using Get-ADUser cmdlet.
Thank you Vaseem Mohammed, that helped allot!