Handle null values in a logic app with JSON table and compose object

Question

Azure Alert triggers an Logic App for output to for example Microsoft Teams Common Alert Schema JSON input to the logic app.

The JSON table has columns and rows

Its easy enough to compose an object with fixed value in the array see example below.

However this fails when any value is null. Which brings me to the crux. I should be able to dynamically create the table with the information available. I feel limited in what i can do with a logic app. With powershell i would've probably solved this with a hashtable.

I've tried iterating through both row and column and appending to an array. However the data gets goes all over the place which is logical.

I suppose I need to iterate through and compose and object somehow but then I just overwrite the object with each iteration

Working example with fixed values

Variable Math
item()[2]

Variable Number
if(contains('56789', substring(string(variables('math')),4,1)),substring(string(add(variables('math'),0.01)),0,4),substring(string(variables('math')),0,4))

Compose Object
{

"AggregatedValue": @{variables('Number')},

"Computer": "@{item()[0]}    ",

"TimeGenerated": "@{item()[1]}"

}

I get the following table to work with


[
  {
    "name": "PrimaryResult",
    "columns": [
      {
        "name": "Computer",
        "type": "string"
      },
      {
        "name": "TimeGenerated",
        "type": "datetime"
      },
      {
        "name": "AggregatedValue",
        "type": "real"
      }
    ],
    "rows": [
      [
        "server.example.org",
        "2019-10-30T08:44:48Z",
        2.409970182797006
      ]
    ]
  }
]

JSON Schema

{
  "properties": {
    "data": {
      "properties": {
        "alertContext": {
          "properties": {
            "AffectedConfigurationItems": {
              "items": {
                "type": "string"
              },
              "type": "array"
            },
            "AlertType": {
              "type": "string"
            },
            "IncludedSearchResults": {
              "type": "string"
            },
            "LinkToSearchResults": {
              "type": "string"
            },
            "Operator": {
              "type": "string"
            },
            "ResultCount": {
              "type": "integer"
            },
            "SearchIntervalDurationMin": {
              "type": "string"
            },
            "SearchIntervalEndtimeUtc": {
              "type": "string"
            },
            "SearchIntervalInMinutes": {
              "type": "string"
            },
            "SearchIntervalStartTimeUtc": {
              "type": "string"
            },
            "SearchQuery": {
              "type": "string"
            },
            "SearchResults": {
              "properties": {
                "dataSources": {
                  "items": {
                    "properties": {
                      "resourceId": {
                        "type": "string"
                      },
                      "tables": {
                        "items": {
                          "type": "string"
                        },
                        "type": "array"
                      }
                    },
                    "required": [
                      "resourceId",
                      "tables"
                    ],
                    "type": "object"
                  },
                  "type": "array"
                },
                "tables": {
                  "items": {
                    "properties": {
                      "columns": {
                        "items": {
                          "properties": {
                            "name": {
                              "type": "string"
                            },
                            "type": {
                              "type": "string"
                            }
                          },
                          "required": [
                            "name",
                            "type"
                          ],
                          "type": "object"
                        },
                        "type": "array"
                      },
                      "name": {
                        "type": "string"
                      },
                      "rows": {
                        "items": {
                          "type": "array"
                        },
                        "type": "array"
                      }
                    },
                    "required": [
                      "name",
                      "columns",
                      "rows"
                    ],
                    "type": "object"
                  },
                  "type": "array"
                }
              },
              "type": "object"
            },
            "SeverityDescription": {
              "type": "string"
            },
            "Threshold": {
              "type": "integer"
            },
            "WorkspaceId": {
              "type": "string"
            }
          },
          "type": "object"
        },
        "essentials": {
          "properties": {
            "alertContextVersion": {
              "type": "string"
            },
            "alertId": {
              "type": "string"
            },
            "alertRule": {
              "type": "string"
            },
            "alertTargetIDs": {
              "items": {
                "type": "string"
              },
              "type": "array"
            },
            "description": {
              "type": "string"
            },
            "essentialsVersion": {
              "type": "string"
            },
            "firedDateTime": {
              "type": "string"
            },
            "monitorCondition": {
              "type": "string"
            },
            "monitoringService": {
              "type": "string"
            },
            "originAlertId": {
              "type": "string"
            },
            "severity": {
              "type": "string"
            },
            "signalType": {
              "type": "string"
            }
          },
          "type": "object"
        }
      },
      "type": "object"
    },
    "schemaId": {
      "type": "string"
    }
  },
  "type": "object"
}

Expected Output

All values not null
computer        Timegenerated       AggregatedValue
server.example.org  2019-10-30T08:44:48Z    2.41

Two values null
computer        Timegenerated       AggregatedValue
server.example.org

I'm sorry but I'm not so clear about what you want ? Could you please tell us your requirements and I will help you solve it ? Currently, per my understanding, if you don't want the null value in the result, do you mind add a "If" condition in you logic app to judge if the value is null. For example if variables['Number'] not null, then do the compose, otherwise variables[Number] = "0.00" and do the compose. — Hury Shen

George Chen George Chen · Accepted Answer · 2019-11-04T08:20:52

If you want to use logic app to handle this data, you have to add some actions to handle the errors. I use Parse JSON to mock receiving table data.

I init the variable Timegenerated and set it with the value in the table and set it with space while the last action failed.

And then init the math same as the Timegenerated. One more step is init the variable Number with if(contains('56789', substring(string(variables('math')),4,1)),substring(string(add(float(variables('math')),0.01)),0,4),substring(string(variables('math')),0,4)) and set it run after last action is skipped.

At last, do the compose action, one with variable math run after init variable Number is skipped and one with variable Number.

And if you think that this is too cumbersome, you could choose to use azure function to handle this data. The azure function support powershell, also you could call Azure Function in the logic app to process your table data to function, in the powershell function handle it then return it to logic app, then you will be able to get the data you want in the logic app.

Handle null values in a logic app with JSON table and compose object

1 Answers