Search code examples
jsonazurecsvazure-logic-appsazure-logic-app-standard

Logic Apps :: parse JSON or CSV and make a list of e-mails separated by semi column

I created a Logic Apps that allows me to query the Azure Resource Graph Explorer and send a list of untagged resources through e-mail.

The Logic Apps is parsing JSON and putting the results in CSV. The CSV looks like this:

name type location tags_createdBy
Resource microsoft.compute/virtualmachines westeurope [email protected]
Resource microsoft.compute/virtualmachines eastus [email protected]
Resource microsoft.compute/virtualmachines eastus [email protected]
Resource microsoft.compute/virtualmachines eastus [email protected]
Resource microsoft.compute/virtualmachines eastus [email protected]
Resource microsoft.compute/virtualmachines westeurope [email protected]
Resource microsoft.compute/virtualmachines eastus [email protected]
Resource microsoft.compute/virtualmachines eastus [email protected]
Resource microsoft.compute/virtualmachines eastus [email protected]
Resource microsoft.compute/virtualmachines germanywestcentral [email protected]
Resource microsoft.compute/virtualmachines westeurope [email protected]
Resource microsoft.compute/virtualmachines switzerlandnorth [email protected]
Resource microsoft.sql/servers/databases eastus
Resource microsoft.storage/storageaccounts westeurope
Resource microsoft.storage/storageaccounts switzerlandnorth [email protected]
Resource microsoft.storage/storageaccounts eastus [email protected]
Resource microsoft.storage/storageaccounts germanywestcentral [email protected]
Resource microsoft.storage/storageaccounts germanywestcentral [email protected]
Resource microsoft.storage/storageaccounts eastus
Resource microsoft.storage/storageaccounts eastus
Resource microsoft.storage/storageaccounts eastus [email protected]
Resource microsoft.storage/storageaccounts eastus
Resource microsoft.storage/storageaccounts westeurope

I would like to parse the tags_createdBy column and take a list of e-mails.

In this case in example the e-mails will be only 3:

I then would like to insert those e-mails in the list of recipients because I want them to receive that e-mail, like this:

enter image description here

So the question is how to:

  1. parse JSON or CSV file
  2. take each value only one and erase NULLs
  3. separate each value by a column or semi column
  4. insert that into the e-mail recipient in Logic App.
Solution

  • After reproducing from my end, I could able to get this work by Parsing the json initially. Considering below is the Json, I have used Parse JSON action in order to extract tags_createdBy column.

    [
  {
    "location": "westeurope",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "westeurope",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "germanywestcentral",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "westeurope",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "switzerlandnorth",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.compute/virtualmachines"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "",
    "type": "microsoft.sql/servers/databases"
  },
  {
    "location": "westeurope",
    "name": "Resource",
    "tags_createdBy": "",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "switzerlandnorth",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "germanywestcentral",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "germanywestcentral",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "[email protected]",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "eastus",
    "name": "Resource",
    "tags_createdBy": "",
    "type": "microsoft.storage/storageaccounts"
  },
  {
    "location": "westeurope",
    "name": "Resource",
    "tags_createdBy": "",
    "type": "microsoft.storage/storageaccounts"
  }
]

    Here is the flow of my logic app which extracts tags_createdBy from the json and saves it in an array variable using Append to array variable.

    enter image description here

    To remove duplicates and get only the unique values, I have used union and filtered the array to remove null values from the resultant. Below is the expression and the flow used in my logic app.

    union(variables('tags_createdBy'),variables('tags_createdBy'))

    Expression to replace ',' with ';'

    replace(string(body('Filter_array')),',',';')

    enter image description here

    RESULTS:

    enter image description here

    enter image description here