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.
The next activity to add would be the Run Query and List results
You can now configure this activity to connect to your subscription and workspace, and enter the query.
The query used in this example will retrieve the latest status of any service where the Display Name contains SQL
| 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.
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.
Once you’ve clicked save, you will be able to test the Logic App, and it will run on the schedule you defined.
- Connectors for Azure Logic Apps
- Common scenarios, examples, tutorials, and walkthroughs for Azure Logic Apps
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