Combine the Get-ADUser Powershell script to produce single result

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 4 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

Combine the Get-ADUser Powershell script to produce single result

Post by ITEngineer »

Hi People,

I need some help in combining the two Power-Shell command to generate the CSV result with the column as follows:

Code: Select all

Mailbox Location, Name, PrimarySmtpAddress, OU
OnPremise, OnPremise User1, OnPremise.User1@domain.com, domain.com/Users/Exchange 2013
OnPremise, OnPremise User2, OnPremise.User2@domain.com, domain.com/Users/Exchange 2013
…
Office365, Office365 User1, Office365.User1@domain.com, domain.com/Users/Exchange Online
Office365, Office365 User2, Office365.User2@domain.com, domain.com/Users/Exchange Online
The below two script works fine when I executed manually, but not sure how to combine it together so I can get the definitive list of who still On-premise and who is in Office 365 already.

Script to get the On-premise Users based on HomeMDB AD attributes:

Code: Select all

Get-ADUser -ResultSetSize 99999 -Filter * -Properties homeMDB, msExchRemoteRecipientType, msExchRecipientDisplayType, msExchRecipientTypeDetails,
 proxyAddresses |
Where-Object { $_.homeMDB -ne $null }
Script to get the Migrated Users based on Multiple AD attributes:

Code: Select all

Get-ADUser -ResultSetSize 99999 -Filter * -Properties homeMDB, msExchRemoteRecipientType, msExchRecipientDisplayType, msExchRecipientTypeDetails,
 proxyAddresses |
Where-Object {
(($_.msExchRemoteRecipientType -eq 4) -or ($_.msExchRemoteRecipientType -eq 20) -or ($_.msExchRemoteRecipientType -eq 21) -or ($_.msExchRemoteRecipientType
 -eq 100)) -and
#( $_.msExchRecipientDisplayType -eq '-2147483642') -and
(($_.msExchRecipientTypeDetails -eq '2147483648') -or (($_.msExchRecipientTypeDetails -eq '34359738368'))) -and
( $_.proxyAddresses -match "onmicrosoft.com")
}
Thank you in advance.
/* IT Engineer */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Combine the Get-ADUser Powershell script to produce single result

Post by jvierra »

Why do you need to queries. Just use the one that has all of the attributes you want.
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

Re: Combine the Get-ADUser Powershell script to produce single result

Post by ITEngineer »

Hi Mr. Vierra,

Can you show me an example of what you mean?
/* IT Engineer */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Combine the Get-ADUser Powershell script to produce single result

Post by jvierra »

Just make sure all properties are included in the query and that all conditions are in the "where" filter.
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

Re: Combine the Get-ADUser Powershell script to produce single result

Post by ITEngineer »

jvierra wrote: Wed Jun 19, 2019 5:22 am Just make sure all properties are included in the query and that all conditions are in the "where" filter.
When I modify the script into below, it does not give the result in .CSV file?

Code: Select all

$properties = 'homeMDB,msExchRemoteRecipientType,msExchRecipientDisplayType,msExchRecipientTypeDetails,proxyAddresses' -split ','
$CSVPath = 'C:\Results.CSV'

Get-ADUser -Filter * -Properties $properties |
    Where-Object { $_.homeDB } |
    Select-Object $properties | 
    Export-Csv $CSVPath -NoTypeInformation

Get-ADUser -ResultSetSize -Filter * -Properties $properties |
Where-Object {
    $_.proxyAddresses -match 'onmicrosoft.com' -and
    $_.msExchRemoteRecipientType -in @(4, 20, 21, 100) -and 
    $_.msExchRecipientTypeDetails -in @(2147483648, 34359738368)
} | 
Select-Object $properties |
Export-Csv $CSVPath -Append -NoTypeInformation
/* IT Engineer */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Combine the Get-ADUser Powershell script to produce single result

Post by jvierra »

You need to remove the export and see if you are getting any results. The where is what you wrote but that does not mean that there are any records that meet those criteria.

To debug a where clause just run each element one at a time to see if it gives results then ad elements back one at a time to find the element that is not doing what you expect.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Combine the Get-ADUser Powershell script to produce single result

Post by jvierra »

I did find one error in the code you copied and pasted. Also there is an issue with HomeDB property which the following will overcome.

Code: Select all

$properties = 'homeMDB,msExchRemoteRecipientType,msExchRecipientDisplayType,msExchRecipientTypeDetails,proxyAddresses' -split ','
$CSVPath = 'C:\Results.CSV'

Get-ADUser -Filter * -Properties $properties |
    Where-Object {$_.homeDB.Count} |
    Select-Object $properties |
    Export-Csv $CSVPath -NoTypeInformation

Get-ADUser -Filter * -Properties $properties |
    Where-Object {
        $_.proxyAddresses -match 'onmicrosoft.com' -and
        $_.msExchRemoteRecipientType -in @(4, 20, 21, 100) -and
        $_.msExchRecipientTypeDetails -in @(2147483648, 34359738368)
    } |
    Select-Object $properties |
    Export-Csv $CSVPath -Append -NoTypeInformation
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

Re: Combine the Get-ADUser Powershell script to produce single result

Post by ITEngineer »

jvierra wrote: Thu Jun 27, 2019 7:39 pm I did find one error in the code you copied and pasted. Also there is an issue with HomeDB property which the following will overcome.

Code: Select all

$properties = 'homeMDB,msExchRemoteRecipientType,msExchRecipientDisplayType,msExchRecipientTypeDetails,proxyAddresses' -split ','
$CSVPath = 'C:\Results.CSV'

Get-ADUser -Filter * -Properties $properties |
    Where-Object {$_.homeDB.Count} |
    Select-Object $properties |
    Export-Csv $CSVPath -NoTypeInformation

Get-ADUser -Filter * -Properties $properties |
    Where-Object {
        $_.proxyAddresses -match 'onmicrosoft.com' -and
        $_.msExchRemoteRecipientType -in @(4, 20, 21, 100) -and
        $_.msExchRecipientTypeDetails -in @(2147483648, 34359738368)
    } |
    Select-Object $properties |
    Export-Csv $CSVPath -Append -NoTypeInformation
Thanks for the help Mr. Vierra,

The .CSV result is still unusable due to no meaningful column:

Code: Select all

homeMDB	msExchRemoteRecipientType	msExchRecipientDisplayType	msExchRecipientTypeDetails	proxyAddresses
	4	-2147483642	2147483648	Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
	4	-1073741818	2147483648	Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
	4	-1073741818	2147483648	Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
	4	-1073741818	2147483648	Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
	4	-2147483642	2147483648	Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
	4	-1073741818	2147483648	Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
	4	-1073741818	2147483648	Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
	4	-1073741818	2147483648	Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
	4	-1073741818	2147483648	Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
	4	-1073741818	2147483648	Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
	4	-1073741818	2147483648	Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
	4	-1073741818	2147483648	Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
	4	-1073741818	2147483648	Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
	4	-1073741818	2147483648	Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
	100	-2147483642	34359738368	Microsoft.ActiveDirectory.Management.ADPropertyValueCollection

Expected:
Mailbox Location, Name, PrimarySmtpAddress, OU
OnPremise, OnPremise User1, OnPremise.User1@domain.com, domain.com/Users/Exchange 2013
OnPremise, OnPremise User2, OnPremise.User2@domain.com, domain.com/Users/Exchange 2013

Office365, Office365 User1, Office365.User1@domain.com, domain.com/Users/Exchange Online
Office365, Office365 User2, Office365.User2@domain.com, domain.com/Users/Exchange Online
Rgds,

IT
/* IT Engineer */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Combine the Get-ADUser Powershell script to produce single result

Post by jvierra »

You are trying to output collection objects. You will have to write a computed select to convert them to delimited strings.

Note that I didn't attempt to fix anything but what you first asked about. To get the format of the objects in a format you want you will have to write the code to do that. Just convert them in a computed select.
This topic is 4 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked