Operator mvexpand: expanded expression expected to have dynamic type
TIL is a blog series in which I document (for me) interesting insights.
This knowledge is possibly already documented a hundred times on the Internet. But so i can find it again i wrote it down here.
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:
Thereby I encountered a problem; while the following query for the table
SigninLogs outputs whether a certain Conditional Access Policy is applied or not…
…the same query fails, when the table is changed to
Falls das Problem weiterhin besteht, öffnen Sie ein Supportticket.
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.
I analyzed the complete schema and all differences I found I documented here.
|AADTenantId||string||Missing in AADNonInteractiveUserSignInLogs|
|ConditionalAccessPolicies||dynamic||ConditionalAccessPolicies||string||Missing in AADNonInteractiveUserSignInLogs|
|DeviceDetail||dynamic||DeviceDetail||string||Missing in AADNonInteractiveUserSignInLogs|
|FlaggedForReview||bool||Missing in AADNonInteractiveUserSignInLogs|
|HomeTenantId||string||Missing in AADNonInteractiveUserSignInLogs|
|IPAddressFromResourceProvider||string||Missing in AADNonInteractiveUserSignInLogs|
|ProcessingTimeInMilliseconds||string||ProcessingTimeInMs||string||Different property name|
|Resource||string||Missing in AADNonInteractiveUserSignInLogs|
|ResourceId||string||Missing in AADNonInteractiveUserSignInLogs|
|ResourceProvider||string||Missing in AADNonInteractiveUserSignInLogs|
|ServicePrincipalId||string||Missing in AADNonInteractiveUserSignInLogs|
|ServicePrincipalName||string||Missing in AADNonInteractiveUserSignInLogs|
|SignInIdentifier||string||Missing in AADNonInteractiveUserSignInLogs|
|SignInEventTypes||string||Missing in SigninLogs|
|SignInIdentifierType||string||Missing in AADNonInteractiveUserSignInLogs|
|UserType||string||Missing in AADNonInteractiveUserSignInLogs|