Operator mvexpand: expanded expression expected to have dynamic type

Microsoft has been preparing a fundamental change to the way sign-in logs are being displayed and stored for some time. This helps in the analysis of sign-in processes, as it distinguishes, for example, whether the user signs-in interactively or with a stored credential (non-interactive).

The different types are:

• User sign-ins (interactive)
• User sign-ins (non-interactive)
• Service principal sign-ins
• Managed identity sign-ins

As you may have already read in my blog series “Phase out Legacy Authentication”, I am a big fan of storing the sign-in information in a Log Analytics workspace. It makes it much easier to query the data and answer complex questions with easy, or even a bit more complex, queries.

Here, too, a distinction is made between the different sign-in methods when storing data. Depending on the type, the data is stored in one of the following tables:

• SignInLogs
• NonInteractiveUserSignInLogs
• ServicePrincipalSignInLogs
• ManagedIdentitySignInLogs

The problem

Thereby I encountered a problem; while the following query for the table SigninLogs outputs whether a certain Conditional Access Policy is applied or not…

1
2
3
4
5
6

let ConditionalAccessPolicyId = "f06e5e30-0c18-493f-9d21-b69aaa44248a";
SigninLogs
| where UserPrincipalName == "fabian@bader.cloud"
| mv-expand ConditionalAccessPolicies
| where ConditionalAccessPolicies.id == ConditionalAccessPolicyId
| project TimeGenerated, UserPrincipalName, AppDisplayName, ConditionalAccessStatus, ConditionalAccessPolicies.displayName, ConditionalAccessPolicies.result

…the same query fails, when the table is changed to AADNonInteractiveUserSignInLogs.

1
2
3
4
5
6

let ConditionalAccessPolicyId = "f06e5e30-0c18-493f-9d21-b69aaa44248a";
AADNonInteractiveUserSignInLogs
| where UserPrincipalName == "fabian@bader.cloud"
| mv-expand ConditionalAccessPolicies
| where ConditionalAccessPolicies.id == ConditionalAccessPolicyId
| project TimeGenerated, UserPrincipalName, AppDisplayName, ConditionalAccessStatus, ConditionalAccessPolicies.displayName, ConditionalAccessPolicies.result

The solution

The solution is as simple as annoying. Unfortunately, Microsoft has chosen different data types when implementing the tables.

While in the SignInLogs table the ConditionalAccessPolicies property is of type dynamic, for the AADNonInteractiveUserSignInLogs table the type string was chosen.

As a workaround the type must be altered at runtime. This can be done with a simple todynamic(ConditionalAccessPolicies) in line 4.
The original data type can simply be overwritten for this purpose. This makes it easier to develop queries for all tables.

1
2
3
4
5
6
7

let ConditionalAccessPolicyId = "f06e5e30-0c18-493f-9d21-b69aaa44248a";
AADNonInteractiveUserSignInLogs
| where UserPrincipalName == "fabian@bader.cloud"
| extend ConditionalAccessPolicies = todynamic(ConditionalAccessPolicies)
| mv-expand ConditionalAccessPolicies
| where ConditionalAccessPolicies.id == ConditionalAccessPolicyId
| project TimeGenerated, UserPrincipalName, AppDisplayName, ConditionalAccessStatus, ConditionalAccessPolicies.displayName, ConditionalAccessPolicies.result

Extended analysis

I analyzed the complete schema and all differences I found I documented here.