Configuring a Logic App to create and save Entra ID Sign-In logs in CSV format in a storage account

A common request I’ve been asked over the years is whether there is a way automate the process of exporting Entra ID Sign-in logs that can be performed from the portal.azure.com portal on a scheduled basis for reporting purposes because as much as it isn’t too difficult to do so manually, the 5 to 10 minutes of time can accumulate to a lot of time saved over the course of the year.

I’ve done a lot of research through the use of Get-AzAuditLog with Connect-AzAccount as well as Get-MgAuditLogSignIn with Connect-MgGraph and could not figure out a way to mimic the same data that is exported through the portal. From what I’ve been able to gather through reading, it doesn’t appear any of these cmdlets provide the complete set of data required and since many environments I work in typically export the logs to Log Analytics for a longer retention, the method I went with was to retrieve the data from there. This blog post serves to provide the following:

1. Configure a storage account to store the CSV exports
2. Configure a Logic App to query an existing Log Analytics Workspace that stores Entra ID logs for the previous week’s Monday 12:00AM to the next Monday 12:00AM
3. Save the data in a CSV data on the storage account

Step #1 – Create a Storage Account with a container to store the CSV extracts

Being by creating a storage account and container to store the CSV extracts. For this example, the container I’m going to use is named entra-id-logs

Step #2 – Create a Logic App to execute the automation

Next, create a Logic App for the automation. For this example, I will be using a consumption based Logic App.

Step #3 – Configure the Logic App to execute the automation

The Logic App will consist of the following 6 steps.

Recurrence

This step will allow the Logic App to run on every Monday at 2:00AM EST to create the CSV extra of the previous week beginning at Monday 12:00AM:

Run query and list results

The next step will use the Run query and list results action to query the Log Analytics Workspace that stores the Entra ID logs with the following query:

SigninLogs
| where TimeGenerated >= startofday(datetime_add(‘day’, -7, datetime_add(‘day’, 1, startofweek(now()))))
and TimeGenerated < startofday(datetime_add(‘day’, 1, startofweek(now())))
| order by TimeGenerated asc

The query I included here will set the beginning of the logs to:

• The start of today’s week on Monday, subtract 7 days so we go back to the previous Monday.

Then set the end time to:

• The beginning of this week’s Monday.

We will also sort the list of entries in ascending order.

Create CSV Table

With the Entra ID logs successfully retrieved, we will not proceed to store the value of the query in a csv table:

Initialize Variable for Start Date

The desired file name for the CSV extract will contain the start date and end date of the logs so we’ll need to initialize and set the value with a function expression with the following:

Initialize Variable for End Date

We will repeat the same activity for the end date as such:

Create blob (V2) – Creating the CSV file

The last step is to use the previous 2 initialized variables and their values to create the CSV file that will be placed into the storage account. The function expression we’ll be using for the Blob Name is the following:

Specify the Create CSV table Output for the Blob Content:

Complete the configuration and click on the Save button.

Hope this helps anyone who may be looking for such an automation.