Quickly retrieving data from Office 365 mailboxes 

Often we need to perform searches over many mailboxes to find just those few which match certain criteria.

One that I recently had was to find users which have a forward on their mailbox (set on the mailbox attribute) in Office 365.

One option I had was I could run:

get-mailbox -resultsize unlimited | where ($_.ForwardingSmtpAddress -like '*')

This will work.  However, if you’ve got a lot of users (e.g. over 100,000 users) it can be very slow.  But why?

As Office 365 is a shared platform, Microsoft throttle PowerShell commands.  They throttle PowerShell in a number of ways.  In this instance, by the amount of data that is sent back to your local PowerShell session.

But if I’ve only got 5 matching users out of thousands, surely that can’t be much data.. why is it throttled?

This is where it becomes important to know where the work for the PowerShell command is being done and when the data is being transferred.  As you are connecting to Office 365, some parts of the PowerShell command execute on remote server (Office 365 in our case), some is executed locally where you lauched PowerShell from and they pass data between them.

In the above example, the get-mailbox -resultsize unlimited retrieves that data from every mailbox.  This is done on Office 365 remote server and then it is all sent back to your local PowerShell session.  Once it arrives at your local PowerShell, it will execute the where ($_.ForwardingSmtpAddress -like '*') on the data.  The data is being transferred between the remote Office 365 and local server contains all the mailbox data, and of course due to the size of the data it gets throttled and slows down the command, taking longer to get the results.

We can speed this up!

The where executes locally, so we really want this to be executed on the remote server, decreasing the amount of data transferred back to your PowerShell session.  So how do we do that?  We can filter the results of the get-mailbox command using the -filter parameter and the command will only return what matches the filter and it performs it all on the remote server.

So we could change this example

get-mailbox -resultsize unlimited | where ($_.ForwardingSmtpAddress -like '*')

to

get-mailbox -resultsize unlimited -filter "ForwardingSmtpAddress -like '*'"

Prove it..

Unfortunately due to way these commands work, we can’t use -resultsize parameter to prove that these commands are faster. If we did use the -resultssize parameter e.g. -resultsize 100, the first example the command would only search the first 100 mailboxes while the updated version above would search mailboxes and return only first 100 forward results.

So in order to show the results, I’ll run this on a tenancy which contains over 300,000 mailboxes.  The original PowerShell command took over 2.5 hours while the new command took just over 10 minutes.

PS_Forwarding

As you can see, the larger the number of mailboxes being searched the more benefit using -filter will give you.

If you have a small amount of users in your tenancy, then you probably won’t see very much difference in using the -filter parameter, however if you’re company expands or your scripts are used on larger Office 365 tenancies, using -filter may help your scripts get quicker results.

Advertisements

AD Connect Filtering

If you’ve installed Azure AD Connect to sync objects from your local Active Directory to Office 365, you may have seen that you can use filtering to stop objects being sync.  Yeah Yeah I hear you say, you can filter objects by the OU they’re in.. Yes you can, but you can also filter by attributes on objects, which as you can imagine can be very handy.

Check out the below link for some Microsoft doco on how to do this.

https://docs.microsoft.com/en-us/azure/active-directory/connect/active-directory-aadconnectsync-configure-filtering#attribute-based-filtering

The filtering examples in the above link can be used to filter in/out users from being sync’d to Office 365 Azure AD from your local AD. It uses the Extension Attributes (on the user objects) to perform the filtering. Once you AD Connect has been setup to do this filtering, the below PowerShell examples can be used to populate the relevant ExtensionAttribute with values which will be filtered to stop users being synced to Office 365.

The Microsoft Example uses ExtensionAttribute15 being set to ‘NoSync’. In my examples, I’ve used ExtensionAttribute8 and setting to ‘DoNotSync’ as that’s how was the ExtensionAttribute and value selected in our Azure AD Connect.

To find any users within your AD which have ExtensionAttribute8 set to ‘DoNotSync’

Import-Module ActiveDirectory
$users = Get-ADUser -Filter "ExtensionAttribute8 -eq 'DoNotSync'" -properties ExtensionAttribute8

 

To find any users within your AD which have ExtensionAttribute8 set to 'DoNotSync' within a specific OU

$users = Get-ADUser -Filter "ExtensionAttribute8 -eq 'DoNotSync'" -properties ExtensionAttribute8 -SearchBase "OU=UserAccounts,DC=FABRIKAM,DC=COM"

 

Or a specific user

get-aduser -Identity username -Properties ExtensionAttribute8

To Add 'DoNotSync' in ExtensionAttribute8 to a specific user

set-aduser -Identity username -Replace @{ExtensionAttribute8='DoNotSync'}

 

Connecting to an Oracle Db from PowerShell

Sometimes you need to be able to connect to databases from PowerShell, in this case an I needed to connect to an Oracle database. First, you’ll need to download and install the Oracle client. In my case, I used the win64 11gR2 client. Make sure you get the appropriate version to suit the version of Oracle (e.g. Oracle 11g) and the Windows machine you are installing it on (win64). You can download clients from here.

After that, all you need is the PowerShell code and knowledge of the database you’re going to connect to. The PowerShell code below can be used to connect by either an Oracle SID or Service Name – choose which works best for you.

## To connect by Service Name
$ora_server = "hostname"
$ora_user = "username"
$ora_pass = "password"
$ora_servicename = "servicename"

## To connect by SID
$ora_server = "hostname"
$ora_user = "username"
$ora_pass = "password"
$ora_sid = "sid"

## by SID
$connection = new-object system.data.oracleclient.oracleconnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ora_server)(PORT=1521)) (CONNECT_DATA=(SID=$ora_sid)));User Id=$ora_user;Password=$ora_pass;")

## by ServiceName
$connection = new-object system.data.oracleclient.oracleconnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ora_server)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=$ora_servicename)));User Id=$ora_user;Password=$ora_pass;")

$connection.open()

$query = "select attribute from table where (attribute = 'value') order by attribute"

$list_set = new-object system.data.dataset
$list_adapter = new-object system.data.oracleclient.oracledataadapter($query, $connection)
$list_adapter.Fill($list_set) | Out-Null
$list_table = new-object system.data.datatable
$list_table = $list_set.Tables[0]
foreach ($entry in $list_table) {
}

$connection.close()