Azure Question posted in
The official documentation to get you started can be found here.

Trouble importing Json into Azure Log Analytics

I’m trying to ingest some Microsoft Flow API data into Azure Log Analytics.
Goal is that a Power Automate send a JSON with the Flows details to Log Analytics.

Here is the sample JSON :

{
    "body": [
        {
            "NAME": "XXXXX",
            "ID": "/providers/Microsoft.ProcessSimple/environments/XXXXXXX/flows/XXXXXXX/runs/XXXXX",
            "TYPE": "Microsoft.ProcessSimple/environments/flows/runs",
            "START": "2024-04-23T21:59:59.8317555Z",
            "END": "2024-04-23T22:23:08.8817048Z",
            "STATUS": "Succeeded"
        },
        {
            "NAME": "XXXXX",
            "ID": "/providers/Microsoft.ProcessSimple/environments/XXXXXXX/flows/XXXXXXX/runs/XXXXX",
            "TYPE": "Microsoft.ProcessSimple/environments/flows/runs",
            "START": "2024-04-22T21:59:59.6368987Z",
            "END": "2024-04-22T22:25:59.2561963Z",
            "STATUS": "Succeeded"
        },
        {
            "NAME": "XXXXX",
            "ID": "/providers/Microsoft.ProcessSimple/environments/XXXXXXX/flows/XXXXXXX/runs/XXXXX",
            "TYPE": "Microsoft.ProcessSimple/environments/flows/runs",
            "START": "2024-04-21T22:00:00.4246672Z",
            "END": "2024-04-21T22:24:54.7721214Z",
            "STATUS": "Succeeded"
        },
        {
            "NAME": "XXXXX",
            "ID": "/providers/Microsoft.ProcessSimple/environments/XXXXXXX/flows/XXXXXXX/runs/XXXXXX",
            "TYPE": "Microsoft.ProcessSimple/environments/flows/runs",
            "START": "2024-04-17T09:49:45.8327243Z",
            "END": "2024-04-17T09:50:46.3459275Z",
            "STATUS": "Succeeded"
        }
    ]
}

First time using KQL, i asked GPT a lot but nothing really work
My last attempt was to go with mv-apply instead of mv-expand :

source
| extend parsedJson = parse_json(body)
| mv-apply parsedItem = parsedJson on 
(
    project 
        TimeGenerated = todatetime(parsedItem['START']), // Convert 'START' to DateTime
        Name = tostring(parsedItem['NAME']),
        ID = tostring(parsedItem['ID']),
        Type = tostring(parsedItem['TYPE']),
        StartTime = tostring(parsedItem['START']),
        EndTime = tostring(parsedItem['END']),
        Status = tostring(parsedItem['STATUS'])
)

Still no luck, throwing me some mismatch error :

Error occurred while compiling query in query: SyntaxError:0x00000003 at 3:11 : mismatched input 'parsedItem' expecting {<EOF>, ';', '|', '.', '*', '[', '=~', '!~', 'notcontains', 'containscs', 'notcontainscs', '!contains', 'contains_cs', '!contains_cs', 'nothas', 'hascs', 'nothascs', '!has', 'has_cs', '!has_cs', 'startswith', '!startswith', 'startswith_cs', '!startswith_cs', 'endswith', '!endswith', 'endswith_cs', '!endswith_cs', 'matches regex', '/', '%', '+', '-', '<', '>', '<=', '>=', '==', '<>', '!=', 'and', 'between', 'contains', 'has', 'in', '!between', '!in', 'or'}

it seems that inside ‘Body’ element, each segment is a number, and i believe this is why it’s hurting me so much !

Tags:

2

Answers


  1. 0

    I guess that you try to write this a ingestion-time transformation. The query language in this case is limited and doesn’t support full KQL as it has to be executed in very large scale with no delay on the data. Specifically, mv-apply isn’t supported. See supported KQL here:
    https://learn.microsoft.com/en-us/azure/azure-monitor/essentials/data-collection-transformations-structure

    Meir

    Login or Signup to reply.
  2. 0

    You can use below design in Logic Apps to send data and create a table with custom json:

    Taken your input in compose:

    enter image description here

    Then:

    enter image description here

    Parse_json:

    {
    "type": "object",
    "properties": {
        "body": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "NAME": {
                        "type": "string"
                    },
                    "ID": {
                        "type": "string"
                    },
                    "TYPE": {
                        "type": "string"
                    },
                    "START": {
                        "type": "string"
                    },
                    "END": {
                        "type": "string"
                    },
                    "STATUS": {
                        "type": "string"
                    }
                },
                "required": [
                    "NAME",
                    "ID",
                    "TYPE",
                    "START",
                    "END",
                    "STATUS"
                ]
            }
        }
    }
}

    Connection of Azure Log Analytics Data Collector:

    enter image description here

    Taken values from below:

    enter image description here

    Output:

    enter image description here

    Table got created:

    enter image description here

    Logic App:

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search