Running Exchange Scripts as a Scheduled Task

Quite often I run into times when I want to automate a task in Exchange.  I usually use Task Scheduler to schedule any PowerShell script I want to run, but it can sometimes be a bit tricky.  There are many ways to do this and here is a couple of ways which work well for me.

I’m assuming you’ve got your Exchange script written and have tested it running from the Exchange Management Shell.  If it has issues running in the Exchange Management Shell, fix those issues first before you schedule the script.

In Task Scheduled, Right click and Create a New Task.

schtask1
Enter details in the name and description to match your script and ensure you have “Run wheather user is logged on or not” is selected and “Run with highest privileges” is ticked.

schtask2

Go to the Triggers Tab and Click the New Button.

schtask3

In here, we can setup a trigger based on a scheduled time.  There are other triggers available as well, but we’re just scheduling the script to be run at a certain time so we’ll be sticking to the trigger “On a schedule”.

schtask4

Fill in the time/s you want to run the script and press OK

Note: You create multiple triggers in case you want to run you script more frequently or on different schedules changing the time the script executes.
schtask5

Go to the Actions Tab and Click the New Button

schtask6

In this area we will enter the details to run the script.

During my testing, I’ve found two ways which this can be done.

The 1st method below is more convenient, but it does spawn additional PowerShell processes.  If you schedule a lot of PowerShell scripts you may want to take this into account for any overheads on the host you’re running the scripts on.

The 2nd method runs scripts with the Exchange environment, but it’s usually necessary to have extra PowerShell in your script to connect to Exchange.

Both methods assume that you’re script will run in PowerShell versions 1-4 and that you know the version and install path of Exchange on your the host running this script.

1st Method

In the Program/script: text box we need to enter the full path to PowerShell on this host.

By default, it is:

You can check this is correct by pressing the Browse button and selecting it.

In the Add arguments (optional): text box we need to enter arguments specific to your script.

For example, this is what would be used for Exchange 2013 installed on Drive C.

If you are using Exchange 2010, it would be slightly different.

Note: in the above examples, you’ll need to replace C:\Scripts\MyScript.ps1 with the full path to where the script is stored on this host.

Once you’ve entered this, press OK

2nd Method

In the Program/script: text box we need to enter the full path to PowerShell on this host.

By default, it is:

You can check this is correct by pressing the Browse button and selecting it.

In the Add arguments (optional): text box we need to enter arguments specific to your script.

For example, this is what would be used for Exchange 2013 installed on Drive C.  If you are using Exchange 2010, you’ll need to update the path to the PSConsoleFile to the Exchange 2010 version.

 

Note: in the above examples, you’ll need to replace C:\Scripts\MyScript.ps1 with the full path to where the script is stored on this host.

Once you’ve entered this, press OK

The press OK again to create the scheduled task.

When you do this, you will be prompted for credentials.  Make sure that you use credentials which have sufficient Exchange privileges to run the cmdlets used in your script.

Note: Consider running scripts from a server or workstation with the Exchange Management Tools installed which is not providing Exchange services (Not an Exchange server).  This will help protect the security of your Exchange servers by not performing tasks as the local administrator.

Tips

Tip #1

Check the installation path for Exchange on the host.  If it was installed on Drive D, the arguments in the scheduled task would look like this.

Tip #2

Use logging in your scripts and don’t rely on the scheduled task logs.  The scheduled task logs simply tell you if task started/completed properly and don’t tell you anything about how your script ran.  Using transcript in your script can be a quick way to add logging. You just add

to the start of your script and

to the end of your script.

Check out more info about start-transcript here.

Sending Notifications to Teams using PowerShell

I thought that as so many of us are now using teams, why not send more information to a channel with results from automated PowerShell scripts.

We can do this via a webhook.

So lets setup a webhook on the Teams Channel.  We’ll need to do this via a connector.

Open the Channel and click the More Options button (which appears as three dots at the top right of the window) and Select Connectors.

Teams-Scripts-02

Scroll down to Incoming Webhook and click the Add button.

Teams-Scripts-03

Give the connector a name and image of your choosing and finally click Create.

Teams-Scripts-04

A new unique URI is automatically generated. Copy this URI string to your clipboard.

Teams-Scripts-05

Note: These details are available if you lose the url in the future.  Select the Connector and then click the Manage button to you can copy the URI string again.

Now we just want some PowerShell code to insert into the webhook, so this needs to be in JSON format.

This worked great.

Teams-Scripts-07a

But, I’d like to add more details to the notifications, like script identification, errors, etc that the scripts encountered. Message cards looked good, so I decided to try a basic message card.

Message cards can do a lot more than notification too. If you’re interested you can check out more about message cards in the references below.

Teams-Scripts-07b

I personally find this format is much better as a Teams notification as it could contain potentially a lot of information from scripts.

Reference

Message Cards
https://docs.microsoft.com/en-us/outlook/actionable-messages/card-reference
https://messagecardplayground.azurewebsites.net/

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:

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

to

 

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.

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’

 

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

 

Or a specific user

To Add ‘DoNotSync’ in ExtensionAttribute8 to a specific user

 

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.