Parsing Unify Sign In Logs
Query
T
| extend
ConditionalAccessPolicies = coalesce(column_ifexists("ConditionalAccessPolicies_string", ""), tostring(column_ifexists("ConditionalAccessPolicies_dynamic", dynamic(null)))),
DeviceDetail = coalesce(column_ifexists("DeviceDetail_string", ""), tostring(column_ifexists("DeviceDetail_dynamic", dynamic(null)))),
LocationDetails = coalesce(column_ifexists("LocationDetails_string", ""), tostring(column_ifexists("LocationDetails_dynamic", dynamic(null)))),
MfaDetail = coalesce(column_ifexists("MfaDetail_string", ""), tostring(column_ifexists("MfaDetail_dynamic", dynamic(null)))),
Status = coalesce(column_ifexists("Status_string", ""), tostring(column_ifexists("Status_dynamic", dynamic(null)))),
TokenProtectionStatusDetails = coalesce(column_ifexists("TokenProtectionStatusDetails_string", ""), tostring(column_ifexists("TokenProtectionStatusDetails_dynamic", dynamic(null)))),
Agent = coalesce(column_ifexists("Agent_string", ""), tostring(column_ifexists("Agent_dynamic", dynamic(null))))
| project-away
ConditionalAccessPolicies_*,
DeviceDetail_*,
LocationDetails_*,
MfaDetail_*,
Status_*,
TokenProtectionStatusDetails_*,
Agent_*Explanation
This KQL (Kusto Query Language) query is processing a table T and performing the following operations:
-
Extend Columns: It creates new columns by combining data from existing columns. For each specified field (e.g.,
ConditionalAccessPolicies,DeviceDetail, etc.), it checks if a string or dynamic version of the column exists. It then uses thecoalescefunction to select the first non-null value among these options. If neither exists, it defaults to an empty string or a null dynamic value. -
Project-away: After extending the table with these new columns, it removes (or projects away) any columns that start with the specified prefixes (e.g.,
ConditionalAccessPolicies_*,DeviceDetail_*, etc.). This means any columns with names that start with these prefixes will be excluded from the final output.
In simple terms, the query is consolidating information from different potential formats (string or dynamic) for several fields into unified columns and then cleaning up the table by removing the original columns that were used to create these unified columns.
Details
Jose Sebastián Canós
Released: May 8, 2025
Tables
Keywords
Operators