#AzureMonitor and #LogicApps: Using Logic Apps to send reports from Azure Monitor Logs data

Sometimes, we want to be able to email the output of a query from Azure Monitor Logs, and creating an alert rule is not appropriate. Examples of this could be the current state of specific services, a list of agents that have not sent a heartbeat in a certain amount of time, or a list of machines that have a specific application installed.

For such instances, the easiest method would be to use a Logic App.

In this example we are going to send a daily email with the status of the SQL services across all our servers, based on the queries from our previous blog posts (here and here). We have used the Logic Apps designer in the Azure portal to create the Logic App.

Building the Logic App

In this example, we want to send the email every day, so we will start the Logic App with a recurrence activity, and configure it to run every 1 day, at 6:30am.

clip_image002

The next activity to add would be the Run Query and List results

clip_image004

You can now configure this activity to connect to your subscription and workspace, and enter the query.

clip_image006

The query used in this example will retrieve the latest status of any service where the Display Name contains SQL

ConfigurationData
| where ConfigDataType == “WindowsServices” and SvcStartupType == “Auto” and SvcDisplayName contains “SQL”
| project Computer , SvcDisplayName , SvcName , SvcStartupType, SvcState , SvcAccount, TimeGenerated
| summarize arg_max(TimeGenerated, *) by Computer, SvcName
| sort by Computer asc, SvcName asc, TimeGenerated desc

Note: The results returned by this activity will be an array of results, and so, if you follow this activity directly with an email activity, you will receive an email for each row result. If you want a consolidated email, you want to consider a consolidation activity. In this example, we have used the Create HTML Table activity and passed the results from the previous activity into this activity.

clip_image008

You can now pass the table as a single output into an email message. We have used the Office365 Send an Email (v2) activity in this example.

To pass the output from the previous activity, select Dynamic content, and then See more to view the output variable.

clip_image010

Once you’ve clicked save, you will be able to test the Logic App, and it will run on the schedule you defined.

Further Reading

One of our colleagues, Brad Watts, had a similar idea with some alternative options, which you can read about in his blog post on Tech Communities

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s