Azure ExpressRoute Utilisation – Automate EOM Reporting with Log Analytics & Logic Apps

/ Hugo Gungormez

We can generate ExpressRoute link utilisation within Azure by leveraging log analytics, logic apps and email!

Very beneficial for automating end of month reporting.

 

Pre-requisites

  • Azure subscription.
  • Log analytics workspace (per GB if you require reporting more than 7 days).
  • ExpressRoute link.
  • ExpressRoute network monitor
  • SPN
  • SMTP Relay

 

KQL

KQL stands for Kusto-Query Language and is used to manipulate/draw data from Azure Log Analytics. I’ve written the following query which we can execute within our Log Analytics Workspace in Azure to generate a graphical timechart of ExpressRoute utilisation, spanning 30 days with data aggregation of 24 hours.

Code

NetworkMonitoring
| where TimeGenerated > ago(30d)
| where SubType == "ERCircuitTotalUtilization"
| summarize avg(BitsInPerSecond), avg(BitsOutPerSecond) by bin(TimeGenerated, 24h)
| render timechart

 

Output

 

Automation with Logic Apps

We can now automate the above query to send email at the end of each month with our graphical timechart attached!

To do so, follow these instructions:

  1. Within Azure search bar, enter Logic Apps and click open Logic Apps. This will take us to the following screen.
  2. Click Add and call your new logic app “ExpressRoute-Report-30days”.
  3. Within our new logic app “ExpressRoute-Report-30days”, click Logic app designer.
  5. We must create the following 4 steps within Logic app designer:
    1. Recurrence.
    2. Run query and visualize results (Preview).
    3. Compose.
    4. Send Email (V3) (Preview).

Recurrence

Here, we define our recurrence interval for our logic app. Since it is EOM, we require once a month.

Run query and visualize results (Preview)

  1. Select your Azure subscription which contains your log analytics workspace and ExpressRoute resources.
  2. Select resource group which contains your log analytics workspace.
  3. Select workspace name of your log analytics workspace.
  4. Enter KQL query to generate timechart report, as specified in this article.
  5. Select Time Chart or desired within Chart Type drop-down menu.

Compose

Within Compose step > Inputs, select Attachment Name. This will ensure our rendered time chart will be attached within email.

Send Email (V3)

Our last step within logic app is to send email.

  1. Write a generic message within Body which will be informative enough for recipients to understand message intention ie. ExpressRoute report for <Customer Name>.
  2. Within Attachments Content data – 1, select Attachment Content 1.
  3. Attachments File name – 1, select Attachment Name.
  4. Click Add/Change connection.
    1. Specify SMTP relay server.
    2. Configure authentication Username/Password.
    3. Finish.

SPN

You will require an SPN with Contributor privileges within your Azure Subscription in order to execute above KQL and integrate within logic app. For instructions on how to create an SPN, follow my previous blog post.

 

SMTP

You will require an SMTP relay server using authentication. Recommended to use a service account for this purpose.

SMTP Server

Username

Password
smtp.domain.com svc-service ********

 

Finish

Congratulations! We have now configured ExpressRoute automated end-of-month reporting leveraging log analytics, logic apps and email relay!