PowerShell: An unexpected Monitoring Journey

PowerShell: An unexpected Monitoring Journey

This short blog (but with a nice idea I hope and some PowerShell scripts) will show you why you could monitor some settings inside your Microsoft 365 tenant and of course, how you could set up your own monitoring solution. Again it’s all about the idea!

I have been reading a lot about the Microsoft DSC. It’s a great tool for applying and monitoring your base configurations.

Microsoft365DSC – Configuration-as-Code for the Cloud

I really love the idea. I guess it’s because we were/are doing almost the same thing but then with Intune. I hope Microsoft will improve the DSC and Add Intune to it, but for now, we are still going to use our own monitoring tool. Monitoring only 1 tenant is not that hard, but if you need to monitor a lot of tenants it can be difficult to be 100% sure, you are notified when some settings are changed or deleted.

You need to make sure you are monitoring some tenant-based settings. Some examples:

*Apple MDM certificate expiration date

*If the Conditional Access rules are still enabled/available

It’s obvious you will need to replace the Apple MDM certificate each year. You don’t want to have the trouble which comes with it when you are too late (Of course you have some grace period) I guess you have some work today.

Mattias Melkersen made us aware of an MS Doc update

Afbeelding met tekst

Automatisch gegenereerde beschrijving

Of course, you could set up email monitoring, but it’s difficult to be 100% sure you will get all the emails from all the different tenants you manage. Microsoft is blocking forwarding external Emails by default so it can be blocking this email also.

I’m going to divide this blog into 2 parts. The first part is going to tell us what we need to do before we can start monitoring the Intune configuration aka the requirements. The second part will show us how to monitor it and the results

I will show you some parts of our scripting, it’s up to you to get the idea behind it and start creating your own free PowerShell monitoring solution


1.SQL Express install with 1 database and 2 tables

2.Microsoft Partner Account (speaks for itself, I am skipping the explanation)

3.Powershell Script to Sync contracts to SQL

4.SQL table with the global admin credentials for each tenant (encrypted)

Sql Express Instance with 1 Database and 2 Tables

Dbo.checkuser Column’s

Afbeelding met tafel

Automatisch gegenereerde beschrijving

Dbo.contract column’s

Afbeelding met tafel

Automatisch gegenereerde beschrijving

PowerShell Script to Sync contracts to SQL DBO.Contract

We have configured a PowerShell script that syncs our Microsoft 365 Partner contracts to an SQL database. The script itself is a little bit longer. But these are the 2 most important parts:

SubPart 1: Retrieving all the partner contracts

SubPart 2: Syncing the Contract to SQL

SubPart 1: Retrieving all the partner Contracts:

Afbeelding met tekst

Automatisch gegenereerde beschrijving

Looking at the script, first, you will need to fetch all the contracts with a simple command: get-msolpartnercontract. So you will need to have the MSOnline module installed. InstallModule MSOnline

When we retrieved the contract information we are going to convert the values to names so we can insert them into the proper SQL field names

SubPart 2: Syncing the contract to SQL

In this part, we are going to sync the information we got from the first part to an SQL database.

Of course, we need to specify the proper SQL server first. Of course, you will need to make sure the SQLServer module is installed. Install-module Sqlserver

After we specified the SQL server, let’s sync the data to SQL

Let’s take a look at how the database and the dbo.contract table are filled

SQL table with the global admin credentials

Now we have all the contracts, we need to make sure we can connect from a trusted location to each tenant.MFA wouldn’t be a problem with trusted locations. Please make sure you have MFA on all your Microsoft365 admin accounts (except the break glass account which you also monitor !).

You could create a PowerShell script to make sure the credentials are transferred to the database or you could do this manually. Of course, we also automated this part.

Please beware, there is a relationship between the Contract field and the Customer ID in the dbo.contract table and the dbo.checkuser table.

Part 2: Monitoring Script

Of course, we need to make sure you retrieve the correct admin credentials for each customer from the SQL database to be able to connect to Microsoft Graph Intune. I guess the #install-module -name Microsoft.graph.intune, just says it all. Of course, I have removed some sensitive information.

When we retrieved all the usernames and credentials for each customer it’s time to connect to each tenant. We are also filtering on which tenants we need to check because not all of our customers have the full Microsoft 365 solution deployed, some of them are only using Exchange Online. If you look closely we are using the co.MS365 = ‘true’ from the dbo.contract table.

Now we have all the credentials and we can connect to each tenant we can fire up the apple MDM certificate check itself. Like mentioned before, you can do the same to check all the conditional access rules.

Afbeelding met tekst

Automatisch gegenereerde beschrijving

Please don’t need to forget to schedule this Powershell script! You really want to make sure it will be checked each day


When you made sure you scheduled this PowerShell script to run each day, you can be 100% sure you will be notified when an Apple certificate is going to expire within 30 days. The great thing about this, this is done for all the tenants you manage.


It’s hard to show you the whole solution and the scripts and still be clear. I hope you get the idea of what I was trying to show you. Special thanks for this blog to my colleague Pieter Abresch. We are the PowerShell Bros. And beware of the modules, they need to be installed.

PowerShell Humor |

I hope Microsoft Lighthouse will continue to evolve so we can use Lighthouse as a sort of Monitoring solution for every setting!

Leave a Reply

Your email address will not be published. Required fields are marked *

15  +    =  16