Month: November 2017

Azure Log Analytics Query Quick Start

This post is an Azure Log Analytics query quick start to get you up and running with queries in a few minutes. It follows on my previous post showing some of the common tasks performed in Azure Log Analytics. The official documentation can be found here.

Query Syntax

Queries in Azure Log Analytics start with a data table name followed by query operators and optionally rendering instructions. Data is piped from one operator to the next using a pipe sign.

Event
 | where Computer like "webserver" and EventID == 14
 | summarize count() by Computer
 | render barchart

Common Query Operators

Summarize : Similar to the SQL Group By statement it applies one or more aggregations with optionally one or more grouping expressions. For example to see how many events were logged per computer and when the last one was logged

Event
| summarize count(Computer), max(TimeGenerated) by Computer, EventLevelName

Distinct: Returns distinct values from one or more column(s)

Event | distinct Computer

Bin: Groups records into bins and works on numbers and dates. For example if you wanted to see how many events were logged per hour.

Event | summarize count(EventID) by bin(TimeGenerated, 1h)

Or group events by their eventid in bins of 1000.

Event | summarize count(EventID) by bin(EventID, 1000)

Or aggregate events by eventid and group on EventLevelName per day. For example to see the failed versus successful entries per day

Event | summarize count(EventID) by EventLevelName , bin(TimeGenerated, 1d)

Join: Just like your typical SQL join.

Event | join kind=inner (SecurityEvent) on Computer

Let: Let stores a value in a variable, the values can be tabular query results or any user supplied value. The variable can then be used in queries. For example if you have to join results from two very long queries or store constants used in your queries.

let errors = Event | ... very long query ...| summarize Count = count(EventID) by Computer, bin(TimeGenerated, 1h);

let memory = Perf | ... very long query ... | summarize avg(CounterValue) by Computer, bin(TimeGenerated,1h);

errors | join kind= inner (memory) on TimeGenerated

Project: Selects the columns to include in the query result, just like SQL Select statement.

Parse: Parse text into columns, it is a long explanation link here. It is really handy to extract data into columns from free-form text like custom log files.

Event| where EventLog == "DPM Backup Events"| parse RenderedDescription with * "Backup job for datasource: " ServerName " on " *

In this example RenderedDescription looked like this:
“Backup job for datasource: SomeServerName on production server: SomeDPMServerName completed succcessfully…“.

The section between the qoutes are the “guide” string to look for, including the spaces and ServerName is new column name to extract.

Render: Renders a chart, too many options to mention here look at the documentation.

Event
| where Computer like "local" and EventID == 14
| summarize count() by Computer
| render piechart

Extend: Create calculated columns.

Event | extend RequiresAttention = iif(EventLevel == 4, "Yes", "No" )
Scope: You can create queries that span multiple applications and workspaces. Use the app() and workspace() expression to include other Application Insights applications and OMS workspaces in your query.
Datetime: You can use basic arithmetic on datetime values. You can subtract two datetime values to get a timespan value representing the difference between them. You can add or subtract a timespan value from a datetime value. You can use todatetime() to convert literals to datetime values. You can find the list of supported literals here. As per the documentation try to stick to ISO8610 date formats.
Case Sensitivity: String comparisons are case insensitive except for ==. There are case sensitive equivalents for most comparison operators ending in “_cs”
Entity names and query operator names are case sensitive.

 

Francois Delport
Latest posts by Francois Delport (see all)

OMS Log Analytics Common Tasks

In this post I’m going to give a quick overview of some the common tasks you can perform in OMS using queries. If you are looking for an Azure Log Analytics query quick start you can find it here. You can also find the official documentation here.

Lookup Tables

To create your own lookup tables you create a query that will return the desired results. Save the query and provide a function name for it. The function name will be the identifier you use to reference the lookup table in queries. In this example AllComputers is the lookup table/function

Event | join kind= inner (
AllComputers
) on Computer

Computer Groups

Computer groups are basically a specialised lookup table. You can use it in queries or other OMS functionality that act on a group of machines like scheduling updates. To create a computer group follow the procedure to create a lookup table but select the “Save this query as computer group” option to save it as a computer group instead of a plain lookup table.

OMS Log Analytics Common Tasks

Creating Custom Alerts

Alerts are based on queries that execute on a schedule, if the query returns any records the alert is triggerd. To setup an alert you start with a query to check for the alert condition. Click on the alert button on the top left to open the alert rule screen and configure your alert rules. Out of interest take a look at the actions the alert rule can perform on the right hand side, you can execute Azure Automation Runbooks or webhooks to create self healing systems  or generate work items in your ITSM application.

UPDATE: Alerts are now created in the Monitor blade of the Portal in the Alerts menu -> Manage Alert Rules.

Create Custom OMS Dashboards

To create custom dashboards you use the View Designer which can be opened by clicking the green plus sign on the left panel. Double click the tile you want for your overview tile and fill in a query that will be used to populate the tile. This will be the tile you see on the home screen.

OMS Log Analytics Common Tasks

Add additional tiles to the view dashboard tab. These will be displayed when you click on the overview tile in the home screen.

Create Custom OMS Dashboards For Azure

To create custom dashboards for Azure from your OMS data you have to create a shared Azure dashboard first, more info here. The functionality to pin the dashboard is not in the OMS query screen, it is in the Azure Log Analytics screen. On the OMS query screen click on Advanced Analytics to open Azure Log Analytics in a new window.

OMS Log Analytics Common Tasks

Create your query in Azure Log Analytics and click on the pin on the right hand side to pin the chart to a shared Azure dashboard.

OMS Log Analytics Common Tasks

You can read more about OMS and Azure integration in this post.

It is a bit confusing having functionality split between OMS and Azure Log Analytics but eventually all the querying functionally will be in Azure Log Analytics.

OMS PowerBI Integration

There are two ways to use PowerBI with OMS. The first and simplest but more manual way is to export a query to PowerBI by clicking on the PowerBI button in the OMS query screen.

OMS Log Analytics Common Tasks

This will download your current query as a query text file that you can then import in PowerBI.

The second and more streamlined method is to link your OMS account to PowerBI but this requires an organisational/paid PowerBI account. In OMS in the settings menu click on Accounts and Connect To PowerBI account.

OMS Log Analytics Common Tasks

Francois Delport
Latest posts by Francois Delport (see all)

Azure Relay Service

In this post I’m going to take a quick look at the Azure Relay service and what it provides.

What Is Azure Relay

Azure Relay is a service that enables communication between applications in different networks, usually public cloud to on-premise but in reality it can be any two networks with internet access. It supports listening for incoming connections as well as outgoing connections without using VPN, special network configuration or opening firewall ports

How Does It Work

Azure Relay service directs requests between different networks using a rendezvous service hosted in Azure. You can read the official documentation here but in short both applications connect to the Service Bus rendezvous namespace and the service then relays communication between the connected parties. The Azure Relay service operates at the application level. You have to write your applications to specifically make use of the Relay WCF connections or Websocket Hybrid Connections. The WCF Relay connections work with .NET only via Nuget packages while Hybrid Connections uses Web Sockets and any language can use it. The service does have some smarts to determine the best way to create connections and will create a direct connection between two parties if possible for example two applications on the same network.

When To Use It

If you require point to point communication between applications on a specific port without using a VPN connection or opening firewall ports Azure Relay is a good candidate. The service is not well suited for real time communication due to the slight delay introduced by the rendezvous service. It is also not well suited for very high volume data transfer or a large number of connections. For example it would not be a good idea to expose a high traffic website hosted on-premise to the internet using the Azure Relay service. If you use the Hybrid Connection integration provided by App Services there is a limit on the number of connections at a time based on your App Service Plan.

Technical Details

Azure Relay service offers 2 connection options:

  • New Hybrid Connections using web sockets which is supported by multiple languages, most new applications or cross platform applications will use this type.
  • Older WCF Relays using WCF Relay bindings and WCF Relay Rest bindings for .NET only, mostly legacy applications or applications leveraging WCF specific features will use this type.

To use relays in your application you have to develop them using the specific Azure Relay connections in the form of WCF Relay bindings or HybridConnectionClient and HybridConnectionListeners from the Microsoft Azure Relay Nuget package. When using Hybrid Connections in your application you will be listening for requests and sending requests. In the case of WCF Relays most of the heavy lifting is done for you by the WCF Relay bindings. When using WebApp Hybrid Connections integration or PortBridge your application is not directly responsible for the relay communication but you will be configuring selected ports that will be forwarded to the relay.

The connections are encrypted using TLS and access to the Azure Relay Namespace is protected with access keys.

Generic Point To Point Connections With PortBridge

The PortBridge sample application uses Azure Hybrid Relay to tunnel communications between two TCP ports without modifying the applications sending or receiving the requests. It uses a server side application to forward requests from a specific port to the Azure Hybrid Relay and a client side application that responds back to the relay. This is handy for applications where you don’t have control over the source code or if you just need a quick way for Azure to reach a service on-premise.

Azure WebApp Integration

Hybrid connections are exposed directly to Azure WebApps. You can access it under the Networking tab.

Azure Relay Service
Azure Relay Service

To use WebApp Hybrid Connections you have to install a connection manager on-premise. The download link for the connection manager is on the Hybrid Connections blade.

Francois Delport
Latest posts by Francois Delport (see all)