Page 1 of 1
Combine the Get-ADUser Powershell script to produce single result
Posted: Tue Jun 18, 2019 8:04 pm
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.
Re: Combine the Get-ADUser Powershell script to produce single result
Posted: Wed Jun 19, 2019 4:54 am
by jvierra
Why do you need to queries. Just use the one that has all of the attributes you want.
Re: Combine the Get-ADUser Powershell script to produce single result
Posted: Wed Jun 19, 2019 4:59 am
by ITEngineer
Hi Mr. Vierra,
Can you show me an example of what you mean?
Re: Combine the Get-ADUser Powershell script to produce single result
Posted: Wed Jun 19, 2019 5:22 am
by jvierra
Just make sure all properties are included in the query and that all conditions are in the "where" filter.
Re: Combine the Get-ADUser Powershell script to produce single result
Posted: Thu Jun 27, 2019 6:37 pm
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
Re: Combine the Get-ADUser Powershell script to produce single result
Posted: Thu Jun 27, 2019 7:17 pm
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.
Re: Combine the Get-ADUser Powershell script to produce single result
Posted: Thu Jun 27, 2019 7:39 pm
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
Re: Combine the Get-ADUser Powershell script to produce single result
Posted: Thu Jun 27, 2019 9:11 pm
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
Re: Combine the Get-ADUser Powershell script to produce single result
Posted: Thu Jun 27, 2019 9:47 pm
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.