Powershell - Export AD Users to CSV

[MatthewL]

I have been looking at this script, also found here:

 

###########################################################
# AUTHOR  : Victor Ashiedu
# WEBSITE : iTechguides.com
# BLOG    : iTechguides.com/blog-2/
# CREATED : 08-08-2014 
# UPDATED : 19-09-2014 
# COMMENT : This script exports Active Directory users
#           to a a csv file. v2.1 adds the condition to 
#           ignore all users with the info (Notes) field
#           found on the Telephones tab containing the 
#           word 'Migrated'. 
###########################################################


#Define location of my script variable
#the -parent switch returns one directory lower from directory defined. 
#below will return up to ImportADUsers folder 
#and since my files are located here it will find it.
#It failes withpout appending "*.*" at the end

$path = Split-Path -parent "E:\powershell scripts\ExportADUsers\*.*"

#Create a variable for the date stamp in the log file

$LogDate = get-date -f yyyyMMddhhmm

#Define CSV and log file location variables
#they have to be on the same location as the script

$csvfile = $path + "\ALLADUsers_$logDate.csv"

#import the ActiveDirectory Module

Import-Module ActiveDirectory


#Sets the OU to do the base search for all user accounts, change as required.
#Simon discovered that some users were missing
#I decided to run the report from the root of the domain

$SearchBase = "OU=FromCSV,OU=TestUsers,DC=70411Lab,DC=com"

#Get Admin accountb credential

$GetAdminact = Get-Credential

#Define variable for a server with AD web services installed

$ADServer = '70411SRV'

#Find users that are not disabled
#To test, I moved the following users to the OU=ADMigration:
#Philip Steventon (kingston.gov.uk/RBK Users/ICT Staff/Philip Steventon) - Disabled account
#Joseph Martins (kingston.gov.uk/RBK Users/ICT Staff/Joseph Martins) - Disabled account
#may have to get accountb status with another AD object

#Define "Account Status" 
#Added the Where-Object clause on 23/07/2014
#Requested by the project team. This 'flag field' needs
#updated in the import script when users fields are updated
#The word 'Migrated' is added in the Notes field, on the Telephone tab.
#The LDAB object name for Notes is 'info'. 

$AllADUsers = Get-ADUser -server $ADServer `
-Credential $GetAdminact -searchbase $SearchBase `
-Filter * -Properties * | Where-Object {$_.info -NE 'Migrated'} #ensures that updated users are never exported.

$AllADUsers |
Select-Object @{Label = "First Name";Expression = {$_.GivenName}},
@{Label = "Last Name";Expression = {$_.Surname}},
@{Label = "Display Name";Expression = {$_.DisplayName}},
@{Label = "Logon Name";Expression = {$_.sAMAccountName}},
@{Label = "Full address";Expression = {$_.StreetAddress}},
@{Label = "City";Expression = {$_.City}},
@{Label = "State";Expression = {$_.st}},
@{Label = "Post Code";Expression = {$_.PostalCode}},
@{Label = "Country/Region";Expression = {if (($_.Country -eq 'GB')  ) {'United Kingdom'} Else {''}}},
@{Label = "Job Title";Expression = {$_.Title}},
@{Label = "Company";Expression = {$_.Company}},
@{Label = "Directorate";Expression = {$_.Description}},
@{Label = "Department";Expression = {$_.Department}},
@{Label = "Office";Expression = {$_.OfficeName}},
@{Label = "Phone";Expression = {$_.telephoneNumber}},
@{Label = "Email";Expression = {$_.Mail}},
@{Label = "Manager";Expression = {%{(Get-AdUser $_.Manager -server $ADServer -Properties DisplayName).DisplayName}}},
@{Label = "Account Status";Expression = {if (($_.Enabled -eq 'TRUE')  ) {'Enabled'} Else {'Disabled'}}}, # the 'if statement# replaces $_.Enabled
@{Label = "Last LogOn Date";Expression = {$_.lastlogondate}} | 

#Export CSV report

Export-Csv -Path $csvfile -NoTypeInformation

 

I want to remove a number of the fields no issue but I want it to export employeeID and employeeNumber which I add in and it fails. I've never used powershell before, I can get it to create the column but when it comes to pulling the data it doesn't want to no. Any recommendations or another way to get this data out?

[ThomL]

IF you tweak the part thats selecting your data for export to this does it help:

$AllADUsers |
Select-Object @{Label = "First Name";Expression = {$_.GivenName}},
@{Label = "Last Name";Expression = {$_.Surname}},
@{Label = "Employee ID";Expression = {$_.EmployeeID}},
@{Label = "Employee Number";Expression = {$_.EmployeeNumber}},
@{Label = "Display Name";Expression = {$_.DisplayName}},
@{Label = "Logon Name";Expression = {$_.sAMAccountName}},
@{Label = "Full address";Expression = {$_.StreetAddress}},
@{Label = "City";Expression = {$_.City}},
@{Label = "State";Expression = {$_.st}},
@{Label = "Post Code";Expression = {$_.PostalCode}},
@{Label = "Country/Region";Expression = {if (($_.Country -eq 'GB')  ) {'United Kingdom'} Else {''}}},
@{Label = "Job Title";Expression = {$_.Title}},
@{Label = "Company";Expression = {$_.Company}},
@{Label = "Directorate";Expression = {$_.Description}},
@{Label = "Department";Expression = {$_.Department}},
@{Label = "Office";Expression = {$_.OfficeName}},
@{Label = "Phone";Expression = {$_.telephoneNumber}},
@{Label = "Email";Expression = {$_.Mail}},
@{Label = "Manager";Expression = {%{(Get-AdUser $_.Manager -server $ADServer -Properties DisplayName).DisplayName}}},
@{Label = "Account Status";Expression = {if (($_.Enabled -eq 'TRUE')  ) {'Enabled'} Else {'Disabled'}}}, # the 'if statement# replaces $_.Enabled
@{Label = "Last LogOn Date";Expression = {$_.lastlogondate}} |

[MatthewL]

If I add them in it doesn't like them.

 

I'm struggling with this bit at the minute, its not liking it:

 

$AllADUsers = Get-ADUser -server $ADServer `
-Credential $GetAdminact -searchbase $SearchBase `
-Filter * -Properties * | Where-Object {$_.info -NE 'Migrated'} #ensures that updated users are never exported.

 

I want it to find all users in the OU not looking at the .info.

[HPlum78]

so just remove the everything from the |......

 

also l will not even get in it to one of my pet hates of -Filter * -Properties * (this should be refactored right away)

Edited June 12, 2020 by HPlum78

[MatthewL]

So if I change that bit to:

 

$AllADUsers = Get-ADUser -server $ADServer `
-Credential $GetAdminact -searchbase $SearchBase `
-Filter * -Properties *

 

It just seems to hang and nothing happens. When I run it is says supply values for the following parameters: and nothing listed so have to stop it running.

[chaplic]

On a big AD (say 50K accounts) it will take a while. However, it does seem a bit over-egged

 

get-aduser -properties samaccountname,employeeid,employeenumber -filter * | where-object {$_.distinguishedname -notlike "*migrated*"} | export-csv Info.csv -nti

 

 

Above will export a few properties includding employeeID and employeenumber from any DN that doesn't have migrated in it (caution as it will not export Dave Migrated either)

[HPlum78]

Sorry ment just on that line...

[MatthewL]

Thanks, will try this on Monday. Only looking at around 5k users. The migrated statement is just from the script before or is that built into AD?

[chaplic]

Yeah, I inferred you were wanting to exclude users from an OU

 

If not just exclude that part of the pipelint

 

get-aduser -properties samaccountname,employeeid,employeenumber -filter * | export-csv Info.csv -nti

[MatthewL]

I've got this working now, but once you have put get-aduser -filter * - Properties Surname, GivenName you then need to add | Select Surname, GivenName | and that gets what I want.

[MatthewL]

So following on from this post my working script has been:

 

# Define the location to export the CSV file to
$path = Split-Path -parent "C:\ADExport\*.*"

# Create a veriable for the date and time stamp for the file name
$LogDate = get-date -f yyyyMMdd-HHmm
$csvfile = $path + "\UserList_$logDate.csv"

# Searches Org OU and selects certain properties from all users including weather account is disabled
Get-ADUser -SearchBase "OU=Users,OU=Org,OU=Root,DC=domain,DC=org,DC=uk" -Filter * -Properties EmailAddress, EmployeeID, EmployeeNumber, Surname, GivenName, Title, sAMAccountName |

# Selects objects from above and renames them into a friendly format
Select-Object @{Label = "Account Status";Expression = {$_.Enabled}},
@{Label = "UserID";Expression = {$_.EmailAddress}},
@{Label = "Employee Number";Expression = {$_.EmployeeID}},
@{Label = "NI Number";Expression = {$_.EmployeeNumber}},
@{Label = "Last Name";Expression = {$_.Surname}},
@{Label = "First Name";Expression = {$_.GivenName}},
@{Label = "Title";Expression = {$_.Title}} |

# Exports to a CSV based on the location and variables above
Export-CSV -Path $csvfile -NoTypeInformation

 

I know need to tweak and struggling to get it working I need to exclude an OU nested with in the current "OU=Users,OU=Org,OU=Root,DC=domain,DC=org,DC=uk" for the purposes of this script. I've tried where-object distinguished name notlike but it doesn't like notlike, any suggestions?

[DaveTheTech]

DN cannot be used in the filter switch.

You could add this to the end of the call to get-aduser:

| ?{$_.DistinguishedName -notlike "*OU NAME*"}

Edited March 2, 2021 by DaveTheTech

[fordea]

-notlike should work, are you sure you're surrounding the OU name you want filtered with asterisks in Where-Object? You could also include a regex to make it more adaptable and exclude multiple sub-OUs if you need:

 

$OUsToExclude = 'Service', 'Temp', 'Supply'
$OUsToExcludeRegex = ($OUsToExclude | ForEach-Object {'(,OU={0},)' -f $_}) -join '|'

 

And then you should be able to filter down the Get-ADUser results with a Where-Object:

 

| Where {$_.DistinguishedName -notmatch $OUsToExcludeRegex}

[ThomL]

Something like this might work?

 

Get-ADUser -SearchBase "OU=Users,OU=Org,OU=Root,DC=domain,DC=org,DC=uk" -Filter * -Properties EmailAddress, EmployeeID, EmployeeNumber, Surname, GivenName, Title, sAMAccountName | where { $_.DistinguishedName -notlike "*OU=exclude,OU=Users,OU=Org,OU=Root,DC=domain,DC=org,DC=uk" } |

[MatthewL]

Spot on, cheers for that.