Log Analytics – normalizing different data types for analytics

Disclaimer: No background is given for Azure Log Analytics, or KQL (Kusto Query Language in this blog) – This just a small ”brain dump” example. If you are interested for background context, start here

Recently I’ve been working on combining data tables in Log Analytics with either/both JOIN, or UNION – Especially when using UNION, its very beneficial to have common types of value for performing shared analytics on multiple tables.

Issue with similar data stored on differing data types

Here is example, when the table has same key (ConditionalAccessPolicies), but with different data type (string), whereas the other is dynamic. The problem, depending on your take here, is that the other data type is string, and can’t be iterable like the ConditionalAccess policies data in the SignInLogs table

Solution

Use the iff() function check if the type is String, and then return the parsed result from the value which was string type originally.

Example:

The following query ensures that Conditional Access Policy values in both data tables combined (AADNonInteractiveUserSignInLogs, SigninLogs ) with UNION are enumerable and searchable in same manner – Expected end result is to produce set of policy results by CaDetails[result] value across both data tables

// KQL example
union AADNonInteractiveUserSignInLogs, SigninLogs 
| extend StatusNonInteractive = parse_json(Status_string) 
| extend StatusInteractive = Status_dynamic
| extend errorCodeInterActive = toint(StatusInteractive.errorCode)
| extend errorCodeNonInterActive = toint(StatusNonInteractive.errorCode)
| extend SigninStatus = case(
errorCodeNonInterActive == 0, "Success",
errorCodeInterActive != 0, "Failure",      
isempty(errorCodeNonInterActive), "Success","Failure"
) 
| extend ClientAppUsed = iff(isempty(ClientAppUsed) == true, "Unknown", ClientAppUsed)  
| extend CaDetail = iff(isempty(ConditionalAccessPolicies_dynamic) == true, parse_json(ConditionalAccessPolicies_string), ConditionalAccessPolicies_dynamic)
| extend DeviceDetail = iff(isempty(DeviceDetail_dynamic) == true, parse_json(DeviceDetail_string), DeviceDetail_dynamic)
| extend LocationDetail = iff(isempty(LocationDetails_dynamic) == true, parse_json(LocationDetails_string), LocationDetails_dynamic)
| mv-expand CaDetail 
| where CaDetail.displayName == "blocklegacy"
| summarize make_set(ClientAppUsed) by tostring(CaDetail['result']), Type
// End of KQL example

This can also be used as basis to refactor the existing Azure AD book for Log Analytics:

Ending words

Bye!

Br Joosua