Creating a Logic App that retrieves AAD sign-in events from Log Analytics and sends a report in an email with a CSV attachment and HTML table insert

Two of the common questions I’ve been asked since publishing the following post over a year ago:

Monitoring, Alerting, Reporting Azure AD logins and login failures with Log Analytics and Logic Apps

http://terenceluk.blogspot.com/2022/02/monitoring-alerting-reporting-azure-ad.html

… is whether there was a way to:

1. Provide the report as a CSV attachment
2. Pretty up the table that is inserted into the email

Providing the report as a CSV attachment is fairly easy but making the Html table more aesthetically pleasing wasn’t. After trying a few methods and not being very successful, I ended up landing on using an Azure Function App that takes the report in JSON format, create the HTML formatted table with colour output, then return it back to the Logic App. The method isn’t very efficient but provides the desired result so this post serves to demonstrate the configuration.

The screenshot below, shows two reports and emails sent out in the Logic App flow. The first Run query and visualize results and Send an email (V2) that is highlighted in red is what my previous post demonstrated, and it sends out an email that contain a plainly formatted HTML table in an email. The second Run query and list results, Create blob (V2), Convert JSON to HTML, Delete blob (V2), Initialize Variable, Set Variable, Create CSV table, Send an email (V2) 2 that I highlighted in green are the additional steps to create a CSV file with the report and send an email with a coloured HTML table:

Step #1 - Create Storage Account

While it is possible to send the full JSON directly to a Function App’s HTTP Trigger, logs exceeding the maximum size would fail so I opted to first create a JSON file and temporarily place it onto a Storage Account container so it can be retrieved by a Function App for processing. The storage of the JSON can be permanent as well but most environments I work with typically sends AAD logs to a storage account for audit retention so this design will only have the file stored for processing then deleted after.

Begin by creating a Storage Account and a container that will store the JSON file. For the purpose of this example, the container will be named: integration

The Function App that will be created will temporarily place a file similar to the one shown in this screenshot:

Due to the sensitivity of data, we want to ensure that the container is not publicly assessable so the Public access level should be configured as Private (no anonymous access):

For improved security, I always prefer to disable Allow storage account key access (Shared Key authorization) and use Azure Active Directory (Azure AD) for authorization. The method in which the Function App will securely access the Storage Account container is through a managed identity maintained by AAD so unless there is a need to allow shared key authorization, we can go ahead and disable it:

You’ll notice that browsing into the container through the will now require the Authentication method to be configured as Azure AD User Account:

Step #2 - Create Azure Function App

With the storage account created, we can proceed to create the Azure Function App that will be triggered via HTTP with the URL of the JSON file passed to it.

Create a new function of the type HTTP Trigger:

Open the function, navigate to Code + Test and paste the code from my GitHub repo into the function: https://github.com/terenceluk/Azure/blob/main/Function%20App/JSON-To-HTML-Function.ps1

Notable items in the code are the following:

1. The container name is extracted from the full path to the JSON file with Regex
2. The blob and storage account name are extracted from the full path to the JSON file with substring and indexOf method
3. The function app expects the full URL path to be passed as a JSON like the following:

{

"body": "https://rgcacinfratemp.blob.core.windows.net/integration/AD-Report-06-29-2023.json"

}

Another way for defining the storage account and container name for the function app is in the Application settings but this hardcodes the value and requires updating:

The function app uses two Az modules to authenticate as a managed identity and retrieve the JSON file. Rather than loading the full Az module, which I have never had any luck because the amount of time it requires to be downloaded causes my function apps to time out, we will only load Az.Accounts and Az.Storage. Proceed to navigate to the App files blade, open the requirements.psd1 and edit the file as such:

# This file enables modules to be automatically managed by the Functions service.

# See https://aka.ms/functionsmanageddependency for additional information.

#

@{

# For latest supported version, go to 'https://www.powershellgallery.com/packages/Az'.

# To use the Az module in your function app, please uncomment the line below.

# 'Az' = '10.*'

'Az.Accounts' = '2.*'

'Az.Storage' = '4.*'

}

I’ve ran into scenarios where the modules do not get downloaded or loaded properly and the way I typically troubleshoot the issue is to navigate into Kudo for the function app to check the downloaded or not downloaded modules via the URL:

https://json-to-html-converter.scm.azurewebsites.net/

Once the function app code has been saved and configuration updated, proceed to navigate to the Identity blade and turn on system managed identity:

Step #3 - Create Logic App

One of the key differences between the plain table report and the new report is that the old one uses Run query and visualize results to query Log Analytics or the report details, while the new report uses Run query and list results to query Log Analytics for the data. The Run query and visualize results action provides output options:

• Html Table
• Pie Chart
• Time Chart
• Bar Chart

In order to generate an output that will allow us to create a customized Html table and CSV file, we would need to use Run query and list results action that generates a JSON file. This JSON file will allows us to create a blob on a storage account container that will be used to generate a customized Html table, as well as create a CSV file:

We want to create the JSON file with a meaningful name so we’ll be using the concat function to name the file:

concat(‘AD-Report-‘,formatDateTime(utcNow(), ‘MM-dd-yyy’),’.json’)

This expression will generate a file with the name AD-Report-<today’s date>.json

The blob content will be provided by the results from the Run query and list results action.

Once the JSON file with the AAD logs is created and placed into a storage account, the Logic App will call an Azure Function App and pass the full URL path so the Function App can retrieve the retrieve the JSON file, format the data into a Html table, then return it to the Logic App. Upon receiving the Html formatted results, the Logic App will then delete the log file. The remaining 2 steps after obtaining the properly formatted Html code is to create and set a variable so it can be used to send the logs as a table.

With the Html email reported ready, we will then use the Create CSV table action to create a CSV file from the Run Query and List Results action and send the email:

The following is a screenshot of how the email is composed with the EmailBody variable containing the HTML content, attaching the CSV table as an attachment and provide it the same name format:

Once the Logic App has been saved, proceed to navigate to the Identity blade and turn on system managed identity:

Step 4 – Assign managed identity for the Function App and Logic App permissions to the Storage Account

The last step is to grant the managed identities the appropriate permissions to the storage account.

The Azure Function App will only need Storage Blob Data Reader because it will only need to retrieve the JSON file.

The Logic App will need Storage Blob Data Contributor because it will need to write the JSON file to the storage account and then delete it afterwards.

Step 5 – Test Report

Proceed to run the Logic App and the following report should arrive in the configured mailbox:

Note that the CSS nth-child selector for even and odd rows does not work with Outlook so while the Html generated would display alternating blues for rows as shown in the screenshot below, the report sent in Outlook would not be the same.

Troubleshooting

The following PowerShell script can call the Function App directly if the Logic App does not generate the report and you want to troubleshoot by calling the Function App directly.

GitHub: https://github.com/terenceluk/Azure/blob/main/Function%20App/Test-Calling-API.ps1$Body = @{

path = https://storageAccountName.blob.core.windows.net/integration/AD-Report-06-28-2023.json

}

$Parameters = @{

Method = "POST"

Uri = "https://youFunctionName.azurewebsites.net/api/Converter?code=xxxxxxxxxxxxm_Dnc_avHxxxxxxxxxxxxxxDH1A=="

Body = $Body | ConvertTo-Json

ContentType = "application/json"

}

Invoke-RestMethod @Parameters | Out-File "C:\Temp\Call-API.html"

The Function App URI can be located in the field shown in the screenshot below: