We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Powershell Query

Medium Priority
53 Views
Last Modified: 2020-05-14
Hi Guys,

I have a csv which contains multiple users with their user User Principal Names, I would like to query all these users and get their description and company fields back.

The issue I have is I have the users within the multiple domains within our single forest.

Can someone help me with a script which will query the users in the csv using their UPN within our AD forest and return the values?

I imagine a combination of Get-ADUser and foreach loops will be needed.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018

Commented:
Not enough information.
Does the account you're logged with have permission to read all of the domains, or do you need to provide credentials?
Is the UPN suffix identical to the respective domain name, or are you using an alternative UPN suffix?
CERTIFIED EXPERT

Author

Commented:
I have the correct permissions. It’ll be different UPNs, I have managed to get the values, but now struggling to get it to export the results for each user in the csv into another csv which contains the users with their values using export-csv, but it doesn’t seem to be working 
CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018

Commented:
Please clarify "different UPNs".
If the UPN for a user is "user@abc.com", is it always the case that this user can be found in the AD domain "abc.com"?
CERTIFIED EXPERT

Author

Commented:
Sorry, we have a mixture of domains, the users in the domains have a mixture of UPNs, so to answer no, a users UPN doesn’t always exist in the same domain.
CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018

Commented:
Can you easily add domain information for the users to the csv, or is the UPN all you have to work with?
Technically, it's not a big deal to query all domains for each UPN, but it'll obviously take longer and put more stress on the DCs when the target domain can't be addressed directly.
CERTIFIED EXPERT

Author

Commented:
I have a csv with all the users with their relevant UPNs in
CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018

Commented:
Try this; adjust the $prop variable in line 3 to the actual column name if the column with the UPN isn't named "UPN".
If the DistinguishedName property in the result is empty, it means the UPN wasn't found.
$inFile = 'C:\Temp\upns.csv'
$outFile = 'C:\Temp\upns_out.csv'
$prop = 'UPN'

$csv = Import-Csv -Path $inFile
$domains = (Get-ADForest).Domains
$csv | ForEach-Object {
	$upn = $_.$prop
	Write-Host "Processing $($upn) ..."
	ForEach ($domain in $domains) {
		$adUser = $null
		If ($adUser = Get-ADUser -Filter "userPrincipalName -eq '$($upn)'" -Property company, description -Server $domain) {
			Break
		}
	}
	$_ | Select-Object -Property $prop, @{n='Description'; e={$adUser.description}},  @{n='Company'; e={$adUser.company}},  @{n='DistinguishedName'; e={$adUser.DistinguishedName}}
} | Export-Csv -NoTypeInformation -Path $outFile

Open in new window

CERTIFIED EXPERT

Author

Commented:
That looks amazing! Thanks for that.

What do I do with line 5 and 6?
CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018

Commented:
Test data, fixed above; just download again.
CERTIFIED EXPERT

Author

Commented:
Ok ran it, I get this:
Get-ADUser : The search filter cannot be recognized
At line:12 char:17
CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018

Commented:
Did you verify that the UPN column in your csv is actually named "UPN", and if not, change $prop variable as described?
CERTIFIED EXPERT

Author

Commented:
D'oh! Looks like it's largely working, one last issue by the looks of it.. Looks like it's error on users who have a their surname like REIT.O'Brian@company.com..

Get-ADUser : Error parsing query: 'userPrincipalName -eq 'John.O'Paul@company.com'' Error Message: 'syntax error' at position: '31'.
At line:12 char:17
+ ...  ($adUser = Get-ADUser -Filter "userPrincipalName -eq '$($upn)'" -Pro ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ParserError: (:) [Get-ADUser], ADFilterParsingException
    + FullyQualifiedErrorId : ActiveDirectoryCmdlet:Microsoft.ActiveDirectory.Management.ADFilterParsingException,Microsoft.ActiveDirectory.Management.Commands.GetADUser
CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018
Commented:
Try this then:
$inFile = 'C:\Temp\upns.csv'
$outFile = 'C:\Temp\upns_out.csv'
$prop = 'UPN'

$csv = Import-Csv -Path $inFile
$domains = (Get-ADForest).Domains
$csv | ForEach-Object {
	$upn = $_.$prop
	Write-Host "Processing $($upn) ..."
	ForEach ($domain in $domains) {
		$adUser = $null
		If ($adUser = Get-ADUser -Filter {userPrincipalName -eq $upn} -Property company, description -Server $domain) {
			Break
		}
	}
	$_ | Select-Object -Property $prop, @{n='Description'; e={$adUser.description}},  @{n='Company'; e={$adUser.company}},  @{n='DistinguishedName'; e={$adUser.DistinguishedName}}
} | Export-Csv -NoTypeInformation -Path $outFile

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT

Author

Commented:
Worked perfectly, thankyou for your help!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.