Monday, 16 March 2015

Export AD Users to CSV using PowerShell

We can generate and export Active Directory users report to CSV file using Powershell cmdlets Get-ADUser and Export-CSV. Get-ADUser cmdlet supports SQL like filter and LDAP filter to filter AD Users. By using these filter we can generate any kind of Active Directory Reports. You can select any user attribute that supported in Active Directory using Get-ADUser cmdlet and it also supports Extended Properties like AccountLockoutTime, Enabled,LockedOut (refer this article:Get-ADUser Default and Extended Properties to know more supported AD attributes).

Export AD Users to CSV using Powershell

The following powershell script exports the selected properties of all Active Directory users to CSV file.
Import-Module ActiveDirectory
Get-ADUser -Filter * -Properties * |
 Select -Property SamAccountName,Mail,Department | 
 Export-CSV "C:\\AllADUsers.csv" -NoTypeInformation -Encoding UTF8

Select Users from specific OU:

We can set target OU scope by using the parameter SearchBase. The following powershell script select all the AD users from the Organization Unit 'TestOU' and export it to CSV file.
Import-Module ActiveDirectory
Get-ADUser -Filter * -SearchBase "OU=TestOU,DC=TestDomain,DC=Local" -Properties * |
 Select -Property SamAccountName,Mail,Department | 
 Export-CSV "C:\\TestOUUsers.csv" -NoTypeInformation -Encoding UTF8

Add more Properties in export report:

You can add any extra attribute that supported in Active Directory in property list. If you want to add the attributes displayName and mobile with this script, you can simply add these attributes as comma separated values.
Import-Module ActiveDirectory
Get-ADUser -Filter * -SearchBase "OU=TestOU,DC=TestDomain,DC=Local" -Properties * |
 Select -Property SamAccountName,Mail,Department,displayName,mobile | 
 Export-CSV "C:\\ADUsers.csv" -NoTypeInformation -Encoding UTF8
Along with normal attributes, you can also add the Extended Properties like AccountLockoutTime, Enabled, LockedOut  (refer this article:Get-ADUser Default and Extended Properties to know more supported extended AD attributes).
Import-Module ActiveDirectory
Get-ADUser -Filter * -SearchBase "OU=TestOU,DC=TestDomain,DC=Local" -Properties * |
 Select -Property SamAccountName,AccountLockoutTime,Enabled,LockedOut | 
 Export-CSV "C:\\ADUsers.csv" -NoTypeInformation -Encoding UTF8

Apply SQL Like filter to get specific users:

Get-ADUser cmdlet supports SQL like filter, users who are not familiar with LDAP filter can easily use this filter to get only specific set of AD users. This following powershell script export the selected properties to CSV file of AD users whose City contains the text 'Austin'.
Import-Module ActiveDirectory
Get-ADUser -Filter 'City -like "*Austin*"' |
  Select -Property Name,City,Mail,Department,DistinguishedName | 
  Export-CSV "C:\\ADUsers.csv" -NoTypeInformation -Encoding UTF8
You can use both normal AD attribute and Extended Properties in this filter. The following powershell script export all the enabled Active Directory users whose PasswordNeverExpires flag value is equal to False by filtering with Extended Properties Enabled and PasswordNeverExpires.
Import-Module ActiveDirectory
Get-ADUser -Filter {Enabled -eq $True -and PasswordNeverExpires -eq $False} |
  Select -Property Name,Mail,Department,DistinguishedName | 
  Export-CSV "C:\\ADUsers.csv" -NoTypeInformation -Encoding UTF8

Apply LDAP Filter to get specific set of AD users:

If your are familiar with LDAP filter, instead of normal filter, you can also use LDAP filter with Get-ADUser powershell cmdlet with more flexibility to filter Active Directory users. The below script exports all the users who are belongs to Admin department.
Import-Module ActiveDirectory
Get-ADUser -LDAPFilter '(Department=*Admin*)' -Properties * |
  Select -Property Name,Mail,Department,DistinguishedName | 
  Export-CSV "C:\\AdminUsers.csv" -NoTypeInformation -Encoding UTF8
The below powershell script exports only enabled AD users with LDAP filter. Refer this article (AD LDAP Filter Examples) to get more LDAP filter examples.
Get-ADUser -LDAPFilter '(!userAccountControl:1.2.840.113556.1.4.803:=2)' -Properties * |
  Select -Property Name,Mail,Department,DistinguishedName | 
  Export-CSV "C:\\EnabledUsers.csv" -NoTypeInformation -Encoding UTF8

Powershell - Export AD Users CSV output:

Powershell - Export AD Users Report to CSV File

Advertisements
Advertisements

No comments:

Post a Comment