Creating automated reports using Logic Apps for Log Analytics queries

From time to time, you may want an automated report to be emailed which displays a specific Log Analytics query, in this blog I will detail how this can be done via using a Logic App.

What are Logic Apps?

A little resource with a big outcome, Logic Apps assist you with automated workflow; scheduling, automating, coposing a task or even rewiting a task.

Logic Apps run in a “trigger”-type fashion, it requires an event to happen or when something is specifically met, to begin the flow of the specific app.

What is Log Analytics?

A Log Query resource that collects data within Azure from numerous points, resources and also Azure Monitor. Data is then stored and Kusto query language is used to retrieve this data in various formats and displays.

The Solution

Will consist of a Logic App, running the Kusto query on a schedule once a day. Once query is ran, the report will be emailed.

Lets get building

Resource Group created: LogicAppReports

Create Logic App

Name: Logic App Name 
Subscription: Subscription to deploy Logic App
Resource Group: Resource Group which Logic App will be deployed 
Location: Location of new resource group
Log Analytics: Can plug Logic Apps direct into Log Analytics, for this example I won’t pursue with this.

Now lets out at a Log Analytics query, for this example I have created a basic return of AzureDiagnostic table events AzureBackupCentralReport

AzureDiagnostics
| where TimeGenerated > now() - 24h
| where EventName_s == "AzureBackupCentralReport" 
| summarize by TimeGenerated, PolicyName_s, BackupFrequency_s, BackupTimes_s

Example of output shown on Log Analytics

Now I have an example query, lets work on the Logic App.

Select Logic App LogicApp-Report and as mentioned, the trigger will be on a daily occurrence. Opening the Logic App, view designer will display, select Recurrence

Change Recurrence value to 1 day or however long you want the report to be sent to you via email

Next step, select action: Run query and visualize results (preview)

Sign-in with user credentials or select Service Principal

Subscription: Select Subscription of Log Analytics Workspace
Resource Group: Log Analytics Resource Group
Workspaces Name: Log Analytics Workspace
Query: Enter Log Analytics query you want to report on
Chart Type: HTML Table, as you want to view output similar to Log Analytics table view

Final action, “send an email”

Sign into your Office 365

Enter details as below, noting attachments Content & Attachments Name

Now save your Logic App and its time to test 🙂

Click Run

You will now see the flow of your function and all should be successful as below, for each action you can click and view further details

Checking your email you will see an attachment and body: test email

Opening .html report will display the output of the query created within your Logic App

Pretty cool and very handy! Generating automated reports from Log Analytics queries!