Combine the Get-ADUser Powershell script to produce single result

Ask your Windows 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.
Locked
User avatar
ITEngineer
Posts: 145
Joined: Wed Oct 12, 2011 10:52 am

Combine the Get-ADUser Powershell script to produce single result

Post by ITEngineer » Tue Jun 18, 2019 8:04 pm

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 */

User avatar
jvierra
Posts: 13730
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra » Wed Jun 19, 2019 4:54 am

Why do you need to queries. Just use the one that has all of the attributes you want.

User avatar
ITEngineer
Posts: 145
Joined: Wed Oct 12, 2011 10:52 am

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

Post by ITEngineer » Wed Jun 19, 2019 4:59 am

Hi Mr. Vierra,

Can you show me an example of what you mean?
/* IT Engineer */

User avatar
jvierra
Posts: 13730
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra » 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.

User avatar
ITEngineer
Posts: 145
Joined: Wed Oct 12, 2011 10:52 am

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

Post by ITEngineer » Thu Jun 27, 2019 6:37 pm

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 */

User avatar
jvierra
Posts: 13730
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra » Thu Jun 27, 2019 7:17 pm

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.

User avatar
jvierra
Posts: 13730
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra » 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

User avatar
ITEngineer
Posts: 145
Joined: Wed Oct 12, 2011 10:52 am

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

Post by ITEngineer » Thu Jun 27, 2019 9:11 pm

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 */

User avatar
jvierra
Posts: 13730
Joined: Tue May 22, 2007 9:57 am
Contact:

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

Post by jvierra » Thu Jun 27, 2019 9:47 pm

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.

Locked