Azure Search statistics Power BI

Question

I'm using storage account to gather Azure Search statistics with current settings by more than one week

And Power BI to view reports. The issue is that even when I'm importing data for 20 days I can't see it in "Search over time report", but only for one day regardless on period I'm selecting.

What is right way of data import or/and date range selecting on this kind of report? Thank's

Answer 1

The problem is caused by some queries with search terms that contain invalid escape sequences (\^ and \~), which cause the JSON parser to fail.

This is a limitation of the Power BI content pack. The options to make it work are the following:

• Remove the backslash before sending a query
• Escape the backslash (\) before sending a query
• Remove the offending characters from the JSON blob itself

If those options don't work for you, you won't be able to use the Power BI content pack, but you can still analyze the data in Power BI Desktop. Below is a query that add error handling for the JSON parser. The limitation of this approach, is that it will ignore full files (1 hour of data) even if there's only one problematic query

Power BI Desktop query:

• ACCOUNTNAME: name of your storage account
• DAYS number of days of data

    let Source = AzureStorage.Blobs("https://ACCOUNTNAME.blob.core.windows.net"),
    #"insights-logs-operationlogs" = Source{[Name="insights-logs-operationlogs"]}[Data],
    #"Sorted Rows" = Table.Sort(#"insights-logs-operationlogs",{{"Date modified", Order.Descending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each Date.IsInPreviousNDays([Date modified], DAYS) or DateTime.Date([Date modified]) = DateTime.Date(DateTimeZone.UtcNow())),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"Parsed JSON" = Table.TransformColumns(#"Removed Columns",{},Json.Document),
    #"Replaced Errors1" = Table.ReplaceErrorValues(#"Parsed JSON", {{"Content", ""}}),
    #"Filtered Rows1" = Table.SelectRows(#"Replaced Errors1", each [Content] <> null and [Content] <> ""),
    #"Expanded Content" = Table.ExpandRecordColumn(#"Filtered Rows1", "Content", {"records"}, {"records"}),
    #"Expanded records" = Table.ExpandListColumn(#"Expanded Content", "records"),
    #"Expanded records1" = Table.ExpandRecordColumn(#"Expanded records", "records", {"time", "resourceId", "operationName", "operationVersion", "category", "resultType", "resultSignature", "durationMS", "properties"}, {"time", "resourceId", "operationName", "operationVersion", "category", "resultType", "resultSignature", "durationMS", "properties"}),
    #"Expanded properties" = Table.ExpandRecordColumn(#"Expanded records1", "properties", {"Description", "Query", "IndexName", "Documents"}, {"Description", "Query", "IndexName", "Documents"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded properties",{{"time", "Datetime"}, {"resourceId", "ResourceId"}, {"operationName", "OperationName"}, {"operationVersion", "OperationVersion"}, {"category", "Category"}, {"resultType", "ResultType"}, {"resultSignature", "ResultSignature"}, {"durationMS", "Duration"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns", "QueryParameters", each Uri.Parts("http://tmp" & [Query])),
    #"Expanded QueryParameters" = Table.ExpandRecordColumn(#"Added Custom2", "QueryParameters", {"Query"}, {"Query.1"}),
    #"Expanded Query.1" = Table.ExpandRecordColumn(#"Expanded QueryParameters", "Query.1", {"search", "$skip", "$top", "$count", "api-version", "searchMode", "$filter"}, {"search", "$skip", "$top", "$count", "api-version", "searchMode", "$filter"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Query.1",{"OperationVersion"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Datetime", type datetimezone}, {"ResourceId", type text}, {"OperationName", type text}, {"Category", type text}, {"ResultType", type text}, {"ResultSignature", type text}, {"Duration", Int64.Type}, {"Description", type text}, {"Query", type text}, {"IndexName", type text}, {"Documents", Int64.Type}, {"search", type text}, {"$skip", Int64.Type}, {"$top", Int64.Type}, {"$count", type logical}, {"api-version", type text}, {"searchMode", type text}, {"$filter", type text}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Datetime]), type date),
    #"Duplicated Column" = Table.DuplicateColumn(#"Inserted Date", "ResourceId", "Copy of ResourceId"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column","Copy of ResourceId",Splitter.SplitTextByEachDelimiter({"/"}, null, true),{"Copy of ResourceId.1", "Copy of ResourceId.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Copy of ResourceId.1", type text}, {"Copy of ResourceId.2", type text}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"Copy of ResourceId.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"Copy of ResourceId.2", "ServiceName"}}),
    #"Lowercased Text" = Table.TransformColumns(#"Renamed Columns1",{{"ServiceName", Text.Lower}}),
    #"Added Custom" = Table.AddColumn(#"Lowercased Text", "DaysFromToday", each Duration.Days(DateTimeZone.UtcNow() - [Datetime])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"DaysFromToday", Int64.Type}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type2", {{"search", null}, {"$skip", null}, {"$top", null}, {"$count", null}, {"api-version", null}, {"searchMode", null}, {"$filter", null}}),
    #"Filtered Rows2" = Table.SelectRows(#"Replaced Errors", each true)
in
    #"Filtered Rows2"