Advanced Workbooks for Conditional Access

Christopher Brumm
5 min readApr 22, 2022


Advancing the builtin Workbooks for Conditional Access

In my recent projects, I have made extensive use of the Conditional Access insights and reporting workbook to prepare for the rollout of a defined conditional access ruleset.

A Workbook is effectively a Configurable Dashboard and only has the requirement that the AAD logs are sent to a Log Analytics Workspace.

However, if you try to rollout as quietly as possible in a larger environment — as I’ve been doing a lot lately — you’ll find a few optimization possibilities. I would like to share some of them here.

Please note that all dashboards only contains users / sessions for which the policy was already in effect at the time.

A silent rollout

As a starting situation, imagine the following scenario:

  • You have defined, tested and piloted your new waterproof CA rule set that requires Hybrid Joined or Compliant Devices.
  • Now you want to do a rollout in waves across, for example, different departments or sites, while generating as little support effort as possible.
  • In order to detect problems early on, you have configured a report-only policy in addition to your productive policy that precedes the rollout by a few weeks.

Interactive vs. non-interactive Sign-Ins

Lately I see more and more users without (or with only very little) sign-in logs. This is a really good sign, because no interactive sign-ins means that we have a very stable state and users rarely have to type in their password.

However, related to a conditional access rollout and the conditional access insights and reporting workbook described above, this is tricky:

By default, the workbook only works with the AAD sign-in logs, meaning: just because you don’t see any errors in the logs doesn’t mean that there wouldn’t be any if all devices had to log in again.

Especially tricky here are devices like older Skype / Teams Phones that are not (can not) be managed via Intune. After a single login, the devices can renew their token literally forever. One of the few reasons why a device needs to re-enroll is if the CA policies for the enrolled user change.

Unfortunately, this is a recipe for disaster: In the Sign-In Logs area of the user you can see the Non-Interactive Sign-Ins of the user but not in the Workbook.

For this reason, I set to work rebuilding the Conditional Access Workbook to work with the Non-Interactive Sign-In as well.

The CAInsightsNonInteractive.workbook is 1:1 the normal CA Insights workbook but shows Non-Interactive sign-ins instead.

You should be careful with the time period here, because there is quite a lot of data in the Non-Interactive Sign-Ins.

Combined Sign-Ins

The next logical step was to include a KQL query created by Fabian Bader that combines and homogenizes both logs.

The workbook CAInsightsCombined.workbook is 1:1 the normal CA Insights workbook but combines both sign-in logs

Here is a comparison of the three workbooks at the same time for a policy for browser access. We see clear differences in the numbers of users in this example.

The final step to All Users

With the help of the new workbooks, we have now been able to convert all departments / sites one by one and are now preparing for the last big leap to All Users.

  • But how far do we have to jump?
  • Who has fallen through the cracks?
  • How many function users log on to the AAD?

To be able to answer these questions, I have now created another workbook, which only gives us the missing users. For each policy in our scenario there is now an additional report-only policy that is scoped to All Users.

Production vs. Report-Policy

The workbook CompareConditionalAccessPolicies.workbook allows a comparison between the current and a report-only target policy (then already scoped to All Users) and outputs only the users that are not yet included in the active policy.

Here is the Workbook in action. I select the current Prod Policy (1) and the desired Report Policy (2) and see in the report only the users that would have been covered only by the Report Policy.

Also for Non-Interactive Sign-Ins

The CompareCAPolicyNonInteractive.workbook does the same but with the non-interactive logs.

Due to the fact that this workbook queries both logs for each widget and joined I recommend to work only with very short periods, otherwise the loading times are very long.

Importing the workbooks

For an import

  • you need write permissions in a resource group
  • just press New in the Workbooks section of the AAD
  • use the Advanced Editor (1) and insert as Gallery Template (2) the content from GitHub 1:1 (3)

After that you will see the workbook in the “Recently modified workbook” section (sometimes you have to select Subscription+RessourceGroup)

More Workbooks?

Check out the work of Daniel Chronlund and Claus Jesperson — I’m a big fan 🤗