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

Save Log Analytics API results to Azure Table Storage

I start learning the Azure Logic Apps and my first tasks is to store the result of a specific Kusto query from calling the log analytics of azure https://api.loganalytics.io/v1/workspaces/{guid}/query.

Currently, I can successfully call the log analytics api using Http in Logic App and this is the sample return.

{
  "tables": [
    {
      "name": "PrimaryResult",
      "columns": [
        {
          "name": "UserPrincipalName",
          "type": "string"
        },
        {
          "name": "OperationName",
          "type": "string"
        },
        {
          "name": "ResultDescription",
          "type": "string"
        },
        {
          "name": "AuthMethod",
          "type": "string"
        },
        {
          "name": "TimeGenerated",
          "type": "string"
        }
      ],
      "rows": [
        [
          "[email protected]",
          "Fraud reported - no action taken",
          "Successfully reported fraud",
          "Phone call approval (Authentication phone)",
          "22-01-03 [09:01:03 AM]"
        ],
        [
          "[email protected]",
          "Fraud reported - no action taken",
          "Successfully reported fraud",
          "Phone call approval (Authentication phone)",
          "22-02-19 [01:28:29 AM]"
        ]
      ]
    }
  ]
}

From this result, I’m stuck on how should iterate the rows property of the json result and save those data to Azure Table Storage which correspond to the columns property in the json result.

E.g.,

| UserPrincipalName    | OperationName                    | ResultDescription           | AuthMethod                              | TimeGenerated |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| [email protected] | Fraud reported - no action taken | Successfully reported fraud | Phone call approval (Authentication phone) | 22-01-03 [09:01:03 AM] |
| [email protected] | Fraud reported - no action taken | Successfully reported fraud | Phone call approval (Authentication phone) | 22-02-19 [01:28:29 AM] |

Hope someone can guide me on how to achieve this.

TIA!

Tags:

2

Answers


  1. 0

    Firstly, use a Parse JSON action and load your JSON in as sample to generate the schema.

    Then use a For each (rename them accordingly) to traverse the rows, this will then automatically generate an outer For each for the tables.

    This is the trickier part, you need to generate a payload that contains your data with some specific keys that you can then identify in your storage table.

    This is my test flow with your data …

    Flow

    … and this is the end result in storage explorer …

    Storage

    The JSON within the entity field in the Insert Entity action looks like this …

    {
  "Data": "@{items('For_Each_Row')}",
  "PartitionKey": "@{guid()}",
  "RowKey": "@{guid()}"
}

    I simply used GUID’s to make it work but you’d want to come up with some kind of key from your data to make it much more rational. Maybe the date field or something.

    Login or Signup to reply.
  2. 0

    You can use Parse_JSON to extract the inner data of the output provided and then can use Insert or Replace Entity action inside a for_each loop. Here is the screenshot of my logic app

    enter image description here

    In my storage account

    enter image description here

    UPDATED ANSWER

    Instead of directly using Insert or Replace Entity I have Initialised 2 variables and then used Insert or Merge Entity. One variable is to iterate inside rows and other is to iterate inside columns using until loop and fetched the required values from tables. Here is the screenshot of my logic app.

    enter image description here

    enter image description here

    In the first until loop, The iteration continues until rows variable is equal to no of rows. Below is the expression:

    length(body('Parse_JSON')?['tables']?[0]?['rows'])

    In the second until loop, The iteration continues until columns variable is equal to no of columns. Below is the expression:

    length(body('Parse_JSON')?['tables']?[0]?['rows'])

    Below is the expression I’m using in Insert or Merge Entity‘s entity

    {
  "@{body('Parse_JSON')?['tables']?[0]?['columns']?[variables('columns')]?['name']}": "@{body('Parse_JSON')?['tables']?[0]?['rows']?[variables('rows')]?[variables('columns')]}"
}

    RESULTS:

    enter image description here

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

Back To Top
Search