I will be exploring the usage of Azure Logic Apps for the simple creation of a mock application which will receive via web hook a customer id, use SQL to retrieve data on that client, iterate through it, perform some basic business logic and then based on that notify the client and possibly a slack chat room. As a bonus I will setup the recently released alerts log and use that to notify me of failings in my app. Here we go.
Azure Logic Apps is a server-less technology. For those not following along , server less means you don’t worry about the server, Azure will scale it based on your consumption needs. Yay. Logic Apps also have a ton of pre-built connectors.
Functionality Breakout
Azure Logic apps can be broken out into 4 main types of events.
- Triggers : Can be scheduled, web hook, or driven by other functions
- Actions: These are internal logic events to perform. Logic checks, statements, business Logic, etc. would be in actions.
- Connectors (MS managed) : these are all the services that Logic Apps connect to. This is probably 80% of what drives the value of using Azure Logic Apps because it makes connecting into various services, very easy. You cannot add your own although there are some basic web hook events you can connect into.
- Flow Control: what to execute next, conditionals, if-else-then, switch and parallel routes
Building A Logic App
Ok, so I am going to build a logic app that is web triggered. We will pass through a Customer Id , and based on whether the customer has a certain volume of orders we will also notify a chatroom in a slack chat, otherwise I will just reply back with the findings.
- Receive a http request
- Verify if Customer has a certain number of orders
- If # of orders constitutes that he is a big spender, notify slack chat room
- Send back general response to service request
Preparation
I need:
- SQL Server Database Server and a database with customer information
- Slack Channel\user
- Something to post the http Request (Postman installed: check)
- Customers that have big orders
- Customers that don’t have big orders
- Stored Procedure to retrieve Customer Order Details
Going to be using the AdventureWorksLT database for this one, you can do this in Azure as well ( link).
I created a simple stored proc that retrieves some basic Customer Info ( Id, First,Last, OrderID, Product Name, Order Quantity, and Line Amount ) based on customer ID:
-- =============================================
-- Author: Esteban Smits
-- =============================================
CREATE PROCEDURE SalesLT.RetrieveSaleDetails
(
-- Add the parameters for the stored procedure here
@CustomerID int
)
AS
BEGIN
Select C.CustomerID, C.FirstName, C.LastName, SOH.SalesOrderID,
P.Name,
SOD.OrderQty, SOD.LineTotal from SalesLT.Customer C
join SalesLT.SalesOrderHeader SOH on SOH.CustomerId =C.CustomerID
join SalesLT.SalesOrderDetail SOD on SOD.SalesOrderId = SOH.SalesOrderId
join SalesLT.Product P on P.ProductID = SOD.ProductID
where C.CustomerId = @CustomerID
End
GO
I also queried the database to find Customers with low and high quantity orders.
If(OBJECT_ID('tempdb..#tempDB') Is Not Null)
Begin
Drop Table #tempDB
End
Select C.CustomerID, Sum(SOD.OrderQty) as 'NumOrders' into #tempDB from SalesLT.Customer C
join SalesLT.SalesOrderHeader SOH on SOH.CustomerId =C.CustomerID
join SalesLT.SalesOrderDetail SOD on SOD.SalesOrderId = SOH.SalesOrderId
group by C.CUstomerID
Select * from (Select top 2 * from #tempDB order by NumOrders DESC) as a1
union
Select * from (Select top 2 * from #tempDB order by NumOrders ASC) as a2
Excellent, results:
Onto setting up the Logic App.
Setting up a Logic App
Go to the Azure Portal and Type in Logic App.
Give it a name, select your subscription, create or use an existing Resource and Res. location.
Please note you can add Log Location but only if you have created an OMS Log Storage . I created one in a directory but even though it was shared between my 2 directories I wasn’t able to select it from the dropdown. Wasn’t the scope so I am going to leave that one for now.
After hitting “Create” ,azure requires time to deploy the Application (takes 30 seconds).
Refresh the Logic Apps window and a new resource will appear in the list. Click into it. A screen is visualized which reminds me of a Microsoft Workflow, IFTTT, Zapier: (i.e. automation tooling).
For the purposes of my example scenario above, I clicked on “When an HTTP Request is received” as my initial starter action. Please note you can change this later so its a good idea to start with the easiest trigger for your first app. Now the very first thing I wondered was “wait.. all the other azure services make having some access token the first step , where is that security in Logic Apps?”
How do logic apps secure access?
By default every Logic App includes a shared access Signature which is built into the URL. the signature is made up of 3 parts, version id (sv), signature (sig) and permission type (sp).
You can learn mor about logic app and securing them here:
Secure access to your logic apps
Building the App
First step is to have the starting event “When a HTTP request is recieved” , I made that a post event. Make sure you do the same unless you want a get event (which is fine by me).I used the data I was going to send as the model:
{ “customerid”:29796, “name”: “Jack Moneybags”}
it created this, schema model which was fine. I have heard that sometimes it won’t set the correct type for integers so be sure to check as mileage may vary.
{
"type": "object",
"properties": {
"customerid": {
"type": "integer"
},
"name": {
"type": "string"
}
}
}
Once you save, it will create the HTTP Request URL but not until you save, so keep that in mind.
I decided on the logic for the order counter to be based on number of orders that had units purchased greater than 3 , So I needed a counter and I wanted a Boolean . I initialized 2 variables (Click “add an Action” and search for initialize variable) , OrderCounter (integer)and BigOrder (boolean).
I then needed to get data from SQL server, it was as stored procedure so I clicked on “Add an Action” and did a search for Stored procedure.
Once added it needs to be able to connect to SQL server , you will need to provide connection string details plus a valid credential for that database\stored proc.
Once that was setup, it provided a list of possible Stored Procedures that I had and based on the selected stored Procedure, it would populate a list of parameters under it , which could be populated.
I assigned the initial data received in the request as the parameter in for customerid .
Ok great. now I had data but i had to iterate through it. so I need a loop or something. A quick search returns that a “for each Action” would be the right approach. Added that as an action and used the result set table (it was a dynamic value you could pass through) to iterate through that.
Now that a had an iterator, I added a condition inside of it to check the OrderQty of the results ( it had it available) and checked to see if the value was greater than 3. if it was increment the NumberOfOrders, using an action called “Increment Variable”. Which is important because I tried using assign variable of variable +1 and that didn’t work, so I use increment variable.
Next we need to evaluate if the customer has enough orders to be considered a “Big Order” customer. For that I need an “If Condition”, a “set variable action”, and additionally a new event of “Slack Post Message Action”, because if the customer is a big Order Customer than we want to send a slack message.
The Slack connector will need you to login for authentication and you need to have enough administrative grants in Slack to grant authorization .
Finally we need to return the general results, the HTTP output response action.
I created a final action event of response and typed in a basic body JSON message, using the dynamic content menu on the right to plugin my variables for the response.
Results
John Bodega Customer id 29844 has no high volume orders, so his total count is a big fat goose egg.
with no slack message.
Jack MoneyBags customer id 29796, has 30 high volume 0rders.
Yay, slack notification!
Bonus Round : Alert Log
My app didn’t work at the beginning so as I was looking into logging ( which Azure Logic Apps don’t have a great deal of debugging functionality just FYI) and I noticed that Azure just recently released a new version of their alert logger.
Setup is pretty self explanatory ( and pictured below), but the results were nice.
Notifications (text & email):
