For hybrid/federated environments, Azure AD Connect is a crucial service. Azure AD Connect Health provides invaluable information such as alerts, performance monitoring, usage analytics, and other information, but sometimes we need some flexibility on what gets alerted, how, and when.

By default, Azure AD Connect performs a delta sync every 30 minutes. However, what if something happens and it hasn’t been able to perform a sync for 5h? For large organisations, this can be a huge issue as it will impact a variety of services, such as the onboarding of new staff, changes made to groups, changes made to Office 365 services, etc.

In this post, I will show a way of using Graph API to monitor the time Azure AD Connect last performed a sync so we can get an alert when this goes above a specified threshold.

Since we are using Graph API, you will need an Azure App Registration with Organization.Read.All application permissions (check here for other permissions that also work). Once we have our app registration in place, we use the get organization method to retrieve the properties and relationships of the currently authenticated organisation.

If you want to use PowerShell, this is extremely easy with the new SDK. All you have to do is run the following (simplified for brevity reasons):

Import-Module Microsoft.Graph.Identity.DirectoryManagement

Connect-MgGraph

(Get-MgOrganization -OrganizationId "xxxxx-xxxx-xxxxx” -Property OnPremisesLastSyncDateTime).OnPremisesLastSyncDateTime

In this post, however, I’m going to show how to do this, including the alerting, using Power Automate. The first step, after creating a new flow of course, is to schedule it to run at a frequency we desire. In my case, I am running it every 2h because I want to be alerted whenever a sync hasn’t happened in over 2h:

 

Next, we need to be able to query Graph API, and for that, we need an OAuth token. There are multiple ways of doing this in Power Automate, so feel free to use whatever method you prefer if you already have one. For the method I have been using lately, first we need to initialise three variables that will contain our Azure tenant ID, the ID of our Azure app registration, and its secret:

Now we send an HTTP POST request to https://login.microsoftonline.com/$TenantID/oauth2/token in order to retrieve our token. In the request, we need to pass our app registration details. Again, there are multiple ways to achieve the same, below is the method I’ve been using:

 

If all goes well, we should now have a valid OAuth token that we can use to query Graph API. Save your flow, test it, and make sure you don’t get any errors. You should see the following in the run history: a status code of 200, and the token details in the OUTPUTS section.

 

Now that we know our flow works successfully in retrieving an OAuth token, we create a new step where we parse the JSON that gets returned by the previous step. This is because we only need the access_token information (listed at the bottom of the previous screenshot). To do this, we use the Parse JSON action of the Data Operation connector. Under Content, we use the Body from the previous step, and under Schema, you can use the following:

{
    "type": "object",
    "properties": {
        "token_type": {
            "type": "string"
        },
        "expires_in": {
            "type": "string"
        },
        "ext_expires_in": {
            "type": "string"
        },
        "expires_on": {
            "type": "string"
        },
        "not_before": {
            "type": "string"
        },
        "resource": {
            "type": "string"
        },
        "access_token": {
            "type": "string"
        }
    }
}

We can now retrieve the information we want! At the most basic level, we issue a GET request to the following URL: https://graph.microsoft.com/v1.0/organization/our_tenant_id

However, this will return a lot of information we don’t need, so we ask only for the onPremisesLastSyncDateTime by using $select:

Like before, we need to parse the JSON that gets returned so we can more easily use the information retrieved:

 

For the Schema, you can use Graph Explorer to run the same GET request. Then, copy the Response preview and use it as your sample in Generate from sample.

 That will generate the following Schema you can use to parse the JSON:

{
    "type": "object",
    "properties": {
        "@@odata.context": {
            "type": "string"
        },
        "onPremisesLastSyncDateTime": {
            "type": "string"
        }
    }
}

 

We now have all the information we need. I suggest you test your flow once more to make sure everything is working as expected. If it is, you should get the following:

Although INPUTS and OUTPUTS seem identical, by parsing the JSON we now have an onPremisesLastSyncDateTime dynamic property we can use in our flow, something we don’t get without parsing the JSON:

The next step is to check when the last sync happened. Keeping in mind that the returned date/time is in UTC format, we can use the following formula to check if onPremisesLastSyncDateTime is less than (aka older) the current UTC time minus 2h. If it is, then we know the last successful sync happened over 2h ago and we send an alert.

Simply copy-paste the following formula in the first field of you Condition (unfortunately it’s no longer possible to edit conditions in advanced mode):

less(body('Parse_JSON_-_onPremisesLastSyncDateTime')?['onPremisesLastSyncDateTime'],
addminutes(utcnow(), -120))

 

If the result is no, then a sync happened less than 2h ago, so we can successfully terminate the flow. Otherwise, we can send a Teams notification (or email, or whatever method you prefer):

 In this example, I am posting a Teams message to a group chat. For some reason, Power Automate keeps adding unnecessary HTML code even when I write the code myself… Here is the code I am using:

<p>Last Azure AD Connect Sync was <span style="color: rgb(226,80,65)"><strong>@{div(sub(ticks(utcNow()),ticks(body('Parse_JSON_-_onPremisesLastSyncDateTime')?['onPremisesLastSyncDateTime'])),600000000)}</strong></span> minutes / <span style="color: rgb(226,80,65)"><strong>@{ div(sub(ticks(utcNow()),ticks(body('Parse_JSON_-_onPremisesLastSyncDateTime')?['onPremisesLastSyncDateTime'])),36000000000)}</strong></span> hours ago (@{body('Parse_JSON')?['onPremisesLastSyncDateTime']} UTC)!</p>

 

This code produces the following message:

But what are those two weird formulas? That’s how we calculate the difference between two dates and times:

div(sub(ticks(utcNow()),ticks(body('Parse_JSON_-_onPremisesLastSyncDateTime')?['onPremisesLastSyncDateTime'])),600000000)

 

First, we get the current date/time in ticks by using utcNow():

ticks(utcNow())

 

A tick is a 100-nanosecond interval. By converting a date/time to ticks, we get the number of 100-nanosecond intervals since January 1, 0001 00:00:00 (midnight). By doing this, we can easily calculate the difference between two dates/times. Might sound a bit strange, but a lot of programming languages use ticks.

Then, we subtract the number of ticks for our onPremisesLastSyncDateTime property, which tells us how many ticks it has been since the last sync:

sub(ticks(utcNow()),ticks(body('Parse_JSON_-_onPremisesLastSyncDateTime')?['onPremisesLastSyncDateTime']))

 

Lastly, because we don’t want the result in ticks but in minutes or hours, we divide the result by 864000000000 so we get the time difference in minutes, or by 36000000000 to get the result in hours.

 

And there you have it! Now, whenever Azure AD Connect takes over 2h to perform a sync, you will be notified!   😊

The other day I was asked by our HR department if it was possible to create a calendar event on all user mailboxes. They didn’t want to send a “normal” meeting invite to dozens of thousands of users that people would have to accept, reject, or ignore. All they wanted was a simple all-day calendar event that would notify users about this particular event.

In my opinion, this has always been one of those features I don’t know why Microsoft never added to Exchange. I can think of so many cases where this would be so useful for so many organisations, but here we are. I remembered reading about something like this a few years back, but it turned out I was thinking about the Remove-CalendarEvents cmdlet introduced in Exchange 2019 and Online. This cmdlet allows admins to cancel future meetings in user or resource mailboxes, which is great when someone leaves the organisation for example.

So that was not an option. I thought about using Exchange Web Services (EWS). I’ve written quite a few EWS scripts and that was an option. However, it is all about Graph API nowadays, so that was by far the best option. But can this be done using Graph API? Of course it can! For that, we use the Create Event method:

POST /users/{id | userPrincipalName}/events

POST /users/{id | userPrincipalName}/calendar/events

POST /users/{id | userPrincipalName}/calendars/{id}/events

 

I’ve also written many Graph API scripts and they work great! However, I’ve had to use lengthy functions to get a token, query Graph API, etc., which made these scripts long and complex... However, with the Graph API SDK, this is far from the case! Now it is extremely easy for admins and developers to write PowerShell Graph API scripts! It is really, really straightforward, and no need to rely on HTTP Post requests!

 

Requirements

You will need to have, or create, an app registration in Azure and use a digital certificate for authentication. This link explains how to easily set this up: Use app-only authentication with the Microsoft GraphPowerShell SDK.

The Graph API permissions required for the script to work are 'Calendars.ReadWrite' and 'User.Read.All' (again, if using the -AllUsers switch). Both of type Application.

With the Graph API SDK, you will need the 'Microsoft.Graph.Calendar' and 'Microsoft.Graph.Users' (if using the -AllUsers switch, more on this later) modules. For more information on the SDK and how to start using it with PowerShell, please visit this link.

  

Script Parameters

-UsersFile

TXT file containing the email addresses or UPNs of the mailboxes to create a calendar event on.

 

-ExcludeUsersFile

TXT file containing the email addresses of the mailboxes NOT to create a calendar event on.

Whenever the script successfully creates an event on a user’s mailbox, it saves the user’s SMTP/UPN to a file named 'CreateCalendarEvent_Processed.txt'. This is so the file can be used to re-run the script for any remaining users (in case of a timeout or any other issues) without the risk of duplicating calendar entries.

 

-AllUsers

Creates a calendar event on all Exchange Online mailboxes of enabled users that have an EmployeeID. This can, and should, be adapted to your specific environment or requirement.

The script does not use Exchange Online to retrieve the list of mailboxes. It retrieves all users from Azure AD that have the Mail and EmployeeID attributes populated.

 

Script Outputs

1. The script prints to the screen any errors, as well as all successful calendar entries created.
2. It also generates a log file named ‘CreateCalendarEvent_Log_date’ with the same information.
3. Whenever it successfully creates an event on a user's mailbox, it outputs the user's SMTP/UPN to a file named ‘CreateCalendarEvent_Processed.txt’. This is so the file can be used to re-run the script for any remaining users (in case of a timeout or any other issues) without the risk of duplicating calendar entries.
4. For any failures when creating a calendar event, the script writes the user's SMTP/UPN to a file named ‘CreateCalendarEvent_Failed.txt’ so admins can easily analyse failures (the same is written to the main log file).

 

Notes

When using the -AllUsers parameter, the script uses the Get-MgUser cmdlet to retrieve Azure Active Directory user objects. I decided not to use Exchange Online cmdlets to keep things simple and because I wanted only mailboxes linked to users that have an EmployeeID. Obviously, every scenario is going to be different, but it should be easy to adapt the script to your specific requirements.

One thing I found, was that running Get-MgUser against a large number of users (in my case, 25000+ users), PowerShell 5.1 was crashing for no apparent reason. Other users on the internet were having the exact same problem when running for a few thousand users. It turns out PowerShell 5.1 default memory allocation will cause the script to crash when fetching large data sets… The good news is that it works great with PowerShell 7+!

The script is slow... When I ran it for 37000+ users, it took approximately 1 second per user. Need to look into JSON batching to create multiple events in one single request.

The script will throw errors in an Hybrid environment with mailboxes on-prem (as they are returned by Get-MgUser). If this is your case, you might want to use an Exchange Online cmdlet instead of Get-MgUser (or get all your mailboxes and then use the -UsersFile parameter).

 

Script

You can get the script on GitHub here.

 

Examples

C:\PS> .\CreateCalendarEvent.ps1 -AllUsers

This command will:

1. Retrieve all users from Azure AD that have the Mail and EmployeeID attributes populated;
2. Create a calendar event on their mailboxes. The properties of the calendar event are detailed and configurable within the script.

 

C:\PS> .\CreateCalendarEvent.ps1 -AllUsers -ExcludeUsersFile .\CreateCalendarEvent_Processed.txt

This command will:

1. Retrieve all users from Azure AD that have the Mail and EmployeeID attributes populated;
2. Create a calendar event on their mailboxes, unless they are in the 'CreateCalendarEvent_Processed.txt' file.

This script uses Graph API to gather statistics regarding Microsoft Teams.


IMPORTANT:

• You will need to have, or create, an 'app registration' in Azure and create a 'client secret';
• The app registration will need the following API permissions to Graph API: 'Group.Read.All' and 'User.Read.All', both of type 'Application'.

The script gathers and exports the following stats for each team:

• Team: the display name of the team;
• Description: the team's description as set by its owner;
• Email: the team's email address;
• CreatedOn: the date and time the team was created;
• RenewedOn: the date and time when the team was renewed;
• Visibility: if the team is 'Private' or 'Public';
• Owners: the number of owners;
• OwnerNames: the display name of all the team's owners;
• Members: the number of members;
• Channels: the number of channels for the team;
• ChannelNames: the display name of all the team's channels.

To call the script, simply run:

.\GetTeamsDetails.ps1