josheinstein · March 23, 2022 18:47 · Mar 23, 2022 · Mar 23, 2022
diff --git a/Example b/Example
@@ -7,6 +7,7 @@ let
     Container = Source{[Name="insights-logs-userriskevents"]}[Data],
 
     // Get only blobs for a specific day (parameter ReportDate must be defined)
+    // The tenantId below is a random example -- use your own tenantId, or add a parameter
     FilteredRows = Table.SelectRows(Container, each Text.StartsWith([Name], "tenantId=63cc8934-5723-42fe-ab52-0bc17d946172/y=" & DateTime.ToText(ReportDate, [Format="yyyy"]) & "/m=" & DateTime.ToText(ReportDate, [Format="MM"]) & "/d=" & DateTime.ToText(ReportDate, [Format="dd"]) & "/")),
 
     // Call the ParseJsonBlob function

diff --git a/Example b/Example
@@ -0,0 +1,19 @@
+let
+
+    // Storage Account containing the blobs
+    Source = AzureStorage.Blobs("mystorageaccount"),
+
+    // Select the container containing azuread user risk events 
+    Container = Source{[Name="insights-logs-userriskevents"]}[Data],
+
+    // Get only blobs for a specific day (parameter ReportDate must be defined)
+    FilteredRows = Table.SelectRows(Container, each Text.StartsWith([Name], "tenantId=63cc8934-5723-42fe-ab52-0bc17d946172/y=" & DateTime.ToText(ReportDate, [Format="yyyy"]) & "/m=" & DateTime.ToText(ReportDate, [Format="MM"]) & "/d=" & DateTime.ToText(ReportDate, [Format="dd"]) & "/")),
+
+    // Call the ParseJsonBlob function
+    ExpandedTable = ParseJsonBlob(FilteredRows[Content]),
+
+    // Expand the "properties" nested record
+    ExpandedProperties = Table.ExpandRecordColumn(ExpandedTable, "properties", {"id", "requestId", "correlationId", "riskType", "riskEventType", "riskState", "riskLevel", "riskDetail", "source", "detectionTimingType", "activity", "ipAddress", "location", "activityDateTime", "detectedDateTime", "lastUpdatedDateTime", "userId", "userDisplayName", "userPrincipalName", "additionalInfo", "tokenIssuerType", "resourceTenantId", "homeTenantId", "userType", "crossTenantAccessType"}, {"properties.id", "properties.requestId", "properties.correlationId", "properties.riskType", "properties.riskEventType", "properties.riskState", "properties.riskLevel", "properties.riskDetail", "properties.source", "properties.detectionTimingType", "properties.activity", "properties.ipAddress", "properties.location", "properties.activityDateTime", "properties.detectedDateTime", "properties.lastUpdatedDateTime", "properties.userId", "properties.userDisplayName", "properties.userPrincipalName", "properties.additionalInfo", "properties.tokenIssuerType", "properties.resourceTenantId", "properties.homeTenantId", "properties.userType", "properties.crossTenantAccessType"})
+
+in
+    ExpandedProperties
diff --git a/ParseJsonBlob b/ParseJsonBlob
@@ -0,0 +1,20 @@
+(Binaries as list) as table => let 
+
+    // Merge the blobs into one large Binary
+    CombinedBinary = Binary.Combine(Binaries),
+
+    // Extract each line of text from the blob as a list
+    Lines = Lines.FromBinary(CombinedBinary),
+
+    // Each line contains a complete JSON document -- parse it
+    JsonList = List.Transform(Lines, Json.Document),
+
+    // Turn the list of JSON objects into a table
+    JsonTable = Table.FromList(JsonList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
+
+    // The resulting table has just one column.
+    // Get the first row and extract its field names (assume all blobs have the same schema)
+    // Expand the properties of the JSON objects
+    ExpandedTable = Table.ExpandRecordColumn(JsonTable, "Column1", Record.FieldNames(List.First(JsonTable[Column1])))
+
+in ExpandedTable
No results found