Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



Use an Azure Logic App for custom processing of a Virtual Machine CPU alert
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-09-03








In this post I will explain the process of using a new Azure Logic App to allow custom processing of an Azure Virtual Machine CPU Alert.
I will create a new Azure Logic App workflow process to receive the alert from the Alert Service and then use the Alert details to store
it as a record in an Azure SQL Database table.


Components needed:

1. Azure Logic App
2. Webhook URI
3. Virtual Machine Alert
4. Azure SQL Database (SaaS)


Let's first define a few items using Azure Online Documentation:

Azure Logic App:
"Logic Apps provide a way to simplify and implement scalable integrations and workflows in the cloud. It provides a visual designer to model
and automate your process as a series of steps known as a workflow. There are many connectors across the cloud and on-premises to quickly
integrate across services and protocols. A logic app begins with a trigger (like 'When an account is added to Dynamics CRM') and after firing
can begin many combinations actions, conversions, and condition logic."

Azure Webhook:
"Webhooks allow the user to route the Azure Alert notifications to other systems for post-processing or custom notifications. Examples of this
can be routing the Alert to services that can handle an incoming web request to send SMS, log bugs, notify a team via chat/messaging services
etc. The webhook uri must be a valid HTTP or HTTPS endpoint. The Azure Alert service will make a POST operation at the specified webhook,
passing on a specific JSON payload and schema."




Step 1: Create a new Azure Logic App - select Blank LogicApp:






Create a new "Request" and a new "SQL Server - Insert a Row": (see image below)

Request:
Search for "Request" and configure it. As you configure that Request it is technically creating an Endpoint(URI). That Endpoint, in the Logic
App, is where you can send a message(ex. CPU Alert message) too. When an Alert gets sent from the Alert Service it has a specific message
type and that has a specific JSON Schema. You need to find & then enter that JSON Schema in the "REQUEST BODY JSON SCHEMA" of the
Request. You can get that JSON Schema from a Message JSON Payload located here. Then copy that JSON "Payload schema" (for all metric based
alerts) and take it to here and extract the JSON Schema from it. It's a quick method to get the needed JSON schema.

Note: "HTTP POST TO THIS URL" will be blank until you save the Logic App. At that point it will populate the URL. That URL is the
actual Endpoint that will receive the Virtual Machine alert. (i.e. Virtual Machine Alert fires and passes the alert details(message) to the
Webhook URI(Logic App) for post-processing)

SQL Server - Insert a Row
Search for "SQL" and choose "SQL Server - Insert a Row"
Here you need to create a connection to a SQL Server & Database using username & password. I used our existing Azure SQL Database.
You will also need to pre-create a table that will log the alert records. Below is the table schema I used for this.

CREATE TABLE [dbo].[Azure](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[MetricName] [varchar](100) NULL,
[MetricUnit] [varchar](50) NULL,
[MetricValue] [varchar](25) NULL,
[MetricThreshold] [varchar](5) NULL,
[MetricStatus] [varchar](25) NULL,
[InsertedDate] [datetime] NULL CONSTRAINT [DF_Azure_InsertedDate] DEFAULT (getdate()),
CONSTRAINT [PK_Azure] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)





Step 2: Create a new Azure Virtual Machine Alert - CPU User Time > X%:

The key part here is the Webhook field. The URI you enter is located in your Logic App - Request - HTTP POST TO THIS URL.





At this point the setup is completed. You now have a a new Logic App created that will accept Alert messages and then process
those messages. Take a break. Grab a coffee. Then wait for the CPU to increase or run some fancy T-SQL to spike the CPU. I always choose not
to wait. Once the CPU has passed the Alert threshold it will send the Alert Message to the WebHook URI and then execute the remaining steps
in the LogicApp, which in this case insert the alert details as a record in a SQL Server table. Many other steps of course can be added to
the Logic App like query the table and see if the logged alerts pass your SLA and if so send a Text Message or Email to an Operations staff
to check out why the Virtual machine CPU has increased. Have fun with it because it is a very powerful tool to have for us in Operations/DevOps.



Query the Azure SQL Database table:





Add more Azure LogicApp Actions or Conditions (Optional):

There are tons of connectors here to integrate with and design a detailed workflow for your processes.





Logic App Testing:

Just a quick note on QA. In the Logic App you can select "Run Trigger" and then select "Manual". This will simulate a message being
send to the Logic App and then run the Logic App steps and checks for success or failure for each. A great way to unit test your Logic App.
Below is an example of results posted in the Azure Portal Log App "Overview" section.






Resources:

Microsoft Azure

What are Logic Apps?

Azure Automation webhooks

How to configure webhooks for alerts