20 minute readApplies to: v4

How to use the Azure Log Analytics tile

The Azure Log Analytics tile is a SquaredUp feature that enables you to view Azure data within your SquaredUp dashboards.

The Azure Log Analytics tile supersedes the v3 OMS tile. We recommend that you upgrade to the latest version of SquaredUp and use the Log Analytics tile.

Log Analytics Donut

Overview

The Azure Log Analytics tile allows you to query Log Analytics for event data (both stand-alone and in the context of objects within SCOM) and display that information as part of your SquaredUp dashboard.

Using this tile you can unify data held within SCOM and Azure on the same screen to bring all collected monitoring information together into a single pane of glass.

The Log Analytics tile queries Log Analytics using the new query language (KQL).

The ability to render information as a graph is a feature of the Azure portal rather than the Log Analytics Query language itself. Graphs will only be shown if you have selected a suitable graph tile such as the Log Analytics Line Graph or Log Analytics Donut.

Prerequisites

  • SquaredUp v4 with the Enterprise or EAM edition - this gives you the Azure Log Analytics tiles. See An Overview of SquaredUp Licensing for more information
  • If you use a proxy server you may need to configure the proxy to allow the Azure Log Analytics tile to communicate with Log Analytics.
  • A Microsoft Azure Log Analytics workspace
  • For configuring the provider you will also need the following: (which are not required for using the tile)

    • Azure subscription administrator role
    • Azure Active Directory User administrator role

Connecting SquaredUp to Azure Log Analytics is a simple procedure, but requires some one-time configuration in your Azure Active Directory (AAD) environment prior to making a SquaredUp provider. These settings will enable permissions and connectivity for API access, and only need to be performed once regardless of the number of SquaredUp instances you have.

Log Analytics tile types

The Azure tile button indicates that there are more tiles available from this one button:

Azure Tile button

After selecting the Azure tile you will get the choice of tiles.

Log Analytics Scalar

Scalar will show a single value:

Log Analytics Scalar button

Log Analytics Grid

Grid will show a grid or table of data:

Log Analytics Grid button

Log Analytics Line Graph

Line Graph will show the queried data as a graph:

Log Analytics Line Graph button

Log Analytics Line Graph

Log Analytics Donut

Donut will show the queried data as a donut:

Log Analytics Donut button

Log Analytics Donut

Configure Azure Active Directory

In order for SquaredUp to authenticate and access Log Analytics data you must create an Azure AD application that represents SquaredUp and configure an SPN for that application with the appropriate permissions to access Log Analytics data for your workspace(s).

In order to complete the Azure Active Directory configuration process, you will need to have Administrative permissions over Azure AD.

A. Add Log Analytics as a new Service Principal in your Azure Active Directory

This step is required to ensure that your Azure Active Directory is set up to interact with the Log Analytics API.

Adding the Service Principal is a one-time operation.

First, you will need to find your Azure Active Directory Tenant ID, and then create a new Log Analytics API Service Principal. The instructions below assume you have PowerShell v4 or later installed (if not, you can use the in-browser PowerShell session right from the Azure portal).

  1. In the Azure portal, open your Azure Active Directory resource. From here, click properties.
  2. Take note of the Directory ID down as you will need it in following steps. (This will be entered as the azure active directory id on the azure log analytics provider page later).

    Azure Active Directory Tenant ID

  3. Start a new PowerShell prompt, and type in the below (where <AAD Tenant Directory ID> is the ID located in the previous step):

    Install-Module AzureAD

    Connect-AzureAD -TenantId <AAD Tenant Directory ID> -Credential (get-credential)

  4. You will get a prompt to log in using your Azure credentials.
  5. Execute the following command:

    New-AzureADServicePrincipal -AppId ca7f3f0b-7d91-482c-8e09-c5d840d0eac5 -DisplayName "Log Analytics API"

B. Create an Azure Active Directory Application

In this step, you will create an AAD application, which SquaredUp will use to authenticate against your AAD. If you have previously used the v3 OMS tile in this AD Tenant, you can use the existing application that was created for that tile (skip this section).

  1. Open your Azure Active Directory resource in the Azure portal and click on App registrations.
  2. Click New application registration.
  3. Give your application a name (such as SquaredUp). Make sure the application type is set to Web app/API. Finally, enter a sign-on URL for SquaredUp. This must be unique, but is only used if someone clicks on a link to the application within Azure. If you do not wish to enter your URL here, you can put in a dummy value such as http://squaredup.local/.
  4. Click create.

C. Allow the Application to access Log Analytics data

  1. Open the Azure Active Directory resource and under the App registrations page, find your SquaredUp app, and click on it to open it's details page.
  2. Take note of the Application ID as you will need it later. (This will be entered as the active directory application id on the azure log analytics provider page later).
  3. Click required permissions.
  4. On the required permissions page, click add.
  5. On the Add API Access wizard, select the Service Principal you created in the first section of these instructions (i.e. Log Analytics API).
  6. Next, in step 2 of the wizard, ensure that Read Log Analytics data as user is checked under Delegated Permissions. Click Select and then complete the add API access process by clicking Done.
  7. The last step to get your AAD application configured is to create an API access key. Back in the Settings page for our AD Application, click on Keys. Give your key a name and an expiry date and press Save. Your API Key will be generated and displayed - make sure to copy it down, as it cannot be retrieved once you navigate away from the page. (This will be entered as the application key on the azure log analytics provider page later).

We now have an application that will allow us to authenticate against your Azure Active Directory!

D. Grant access from your Azure AD application to Log Analytics workspaces

In this step you will allow the Azure AD application to access Log Analytics workspaces. This step will need to be repeated on each Log Analytics workspace you want to query using SquaredUp.

  1. Find your Log Analytics resource on the Azure portal.
  2. Take note of the Workspace ID as you will need it later. (This will be entered as the workspace id on the azure log analytics provider page later).
  3. Next, click on Access Control (IAM) and then Add.
  4. Set the Role to Log Analytics Reader.
  5. On the Select dropdown list click on the AAD application created earlier, and make sure it appears under Selected members.
  6. Click Save.

Walkthrough: In SquaredUp create an Azure Log Analytics provider

Now that SquaredUp has an identity within Azure Active Directory that it can use to query Log Analytics workspaces, you need to provide the details to SquaredUp. A provider defines the Log Analytics workspace that you wish to query, and the identity used for doing so (in this case, the Azure AD Application authenticates to query Log Analytics as the user who authorises the provider).

If you have multiple workspaces that you want to query, you can either create multiple providers (one per workspace) and/or create an empty Log Analytics workspace for use with the provider, and then use the additional workspaces option in each tile to query up to 10 workspaces simultaneously.

Create provider for a Log Analytics workspace

  1. Log on to SquaredUp and navigate to the right-hand menu ☰ then system and then click on the web api perspective.
  2. Click add new provider.
  3. Change the provider type to azure log analytics and type in a suitable name, e.g. LondonLogAnalytics.
  4. Enter the Log Analytics workspace ID you noted down in section D above.
  5. Enter the Azure Active Directory Tenant ID you noted down in section A above.
  6. Enter the Azure Active Directory Application ID you noted down at the beginning of section C above.
  7. Enter the application API access key you noted down at the end of section C above.
  8. Click add provider.

Add ReplyUrl to Azure AD Application and authorise provider

Once the provider is created, the final step is to authorise it within Azure AD. These credentials are sent to Azure AD and are not stored anywhere within SquaredUp.

  1. Note down the reply url listed under the newly created provider.
  2. In the Azure portal, click on the Azure Active Directory resource and under the App registrations page, find your SquaredUp app, and click on it to open it's details page.
  3. Click on Reply URLs.
  4. Add the URL from step 1 to the list of URLs and click Save.
  5. Wait a few minutes for the Azure AD to process the update, then back in SquaredUp click perform authorization under the newly created provider.

If you do not want to repeatedly add a reply URL to the application for each new provider, you can specify a wildcard when configuring the reply URL using a * (e.g. https://squaredup.local/*).

Log Analytics tile options

Scope

The scope is optional, and allows you to specify a scope of SCOM objects, which can be used later in the tile configuration to insert SCOM object properties into the query using mustache syntax.

If you use a scope ensure that it contains objects monitored by Azure Log Analytics.

Provider

A provider defines the Log Analytics workspace that you wish to query. Select a provider, that you created earlier, from the drop down list.

See How to create an Azure Log Analytics provider

Query

The Logs tile use the Log Analytics query language (KQL), Kusto, which is very rich and offers features such as sorting, projection and calculated values, which you can use to control the display of data in your dashboard.

Test your query in the Log Analytics workspace in the Azure portal to be sure it returns the results expected, and then copy and paste the query into the Azure Log Analytics tile query box. There are also many sample queries in this area of the portal for you to work with or copy back into the Logs tile.

For more information see Microsoft Azure Monitor log queries article - provides links to other resources for learning how to write queries.

Queries can also include Mustache syntax to create a dynamic query that will change depending on the object being viewed. After typing {{ the mustache helper will appear, see below. This is particularly useful on perspectives where you can insert the object name as a variable using mustache syntax.

The mustache helper

When specifying a mustache clicking the {{}} button or typing {{ brings up a helpful picker which shows all the properties of your selected object, along with sample values. It also includes a suggested mustache for creating a dynamic list of names which is useful if the tile is scoped to more than one object (such as a list, group, Enterprise Application, or class).

Once the mustache helper is displayed, the list of properties will automatically filter based on what you type, allowing you to quickly find a property using a partial name or likely term. Clicking an item in the list will automatically insert that property into your query and complete the mustache.

For example, if you have specified a computer in the scope section you can insert the computer name into the query using {{scope[0].displayName}} or on a perspective use {{displayName}} to create a dynamic query scoped to the object being viewed.

Property names are case-sensitive and should be written as they appear in the mustache helper (e.g. displayName not DisplayName).

Column overrides

For the donut and line graph tiles there is a column overrides option following the query box. This important option allows you to choose how the data returned is grouped and displayed.

For example, if your results have more than one numeric column, SquaredUp won't know which one to use as the line graph Y axis, so you select the one you want here.

ColumnOverrides

Timeframe

If your query doesn't specify a timeframe then you can set it using the tile timeframe.

Take care when extending the timeframe beyond 7 days, as this may pull many thousands of entries and significantly impact browser performance.

If you are planning on changing the page timeframe beyond 7 days then it may be worth switching to the specific timeframe option within the tile configuration instead.

It is also possible to specify a timeframe in the query (e.g. where timestamp >= ago(2h)), however events must satisfy both the timeframe specified in the tile and in the where clause.

You may like to consider using the page timeframe, and adding a "clamping" timeframe in the query (such as where timestamp >= ago(7d)). When the page timeframe is less than 7 days it will be used, but once the page timeframe exceeds 7 days the query will enforce the max time.

Scalar

You can change the font size of the figure shown, and add text to show the unit after the figure, for example GB or servers.

Data range

The Data Range option allows you to choose the range of the y-axis for the line graph. The min and max will be set, depending on the option selected.

percentage shows 0 to 100

fit to data shows the data minimum to data maximum

fit to data (from zero) shows from 0 to the data maximum

custom allows you to specify the min and max

custom fit - allows you to specify the min and max, however if data falls within the specified ranges the y axis range will fit to data.

Display

For the line graph a height slider allows you stretch or shrink the graph height.

For the donut the size mode allows you to change from the default to a fill mode where the donut will fit the space available.

show legend allows you to display a key to the colours on the donut, and the legend size slider will change the size of the legend text.

color palette gives you a choice of several colour selections suited towards different data sets such as priorities and health.

Label

For the line graph the show legend option will display a key to the coloured graph lines, and allow you to change from the automatic label to specify your own custom label format.

Custom labelling

Using the custom option you can create your own advanced label to specify exactly how you want the results to be displayed (using both static text and dynamic properties) to ensure that the results always make sense.

See How to use Custom labelling

Walkthrough: Create tiles to query data

Now that you have an authorised provider, you can start to create Log Analytics tiles to perform our queries. It's useful to use the Azure portal to prepare and test your queries first, see the links at the bottom of this article for further information. For this walkthrough, you are going to query Software Update status summary information.

Sample: Query Update information

The provided sample below assumes you have the Update Management solution enabled for your workspace.

  1. In SquaredUp browse to the dashboard you wish to add the App Insights tile to.
  2. Click on the orange + button to add a new tile, then click on Azure > Log Analytics (Grid)

    Azure Tile button

    Log Analytics Grid button

  3. The scope section (v4.2 and above) allows you to specify a scope of SCOM objects, which can be used later in the tile configuration to insert SCOM object properties into the query. If you are using v4.2 or above and wish to add a scope, click on group and search to find a suitable group of computers. You need a group that contains servers monitored by Azure Log Analytics which we can query later for update information.
  4. In the provider select the provider you created earlier.
  5. On the Query panel, enter the following Log Analytics Query:

    UpdateSummary
    | summarize max(TimeGenerated) by Computer
    | project Computer, TimeGenerated = max_TimeGenerated
    | join (
    	UpdateSummary
    	| where TotalUpdatesMissing > 0 or RestartPending == true
    )
    on Computer, TimeGenerated
    | project-away Computer1, TimeGenerated1
    | order by TimeGenerated desc
  6. If you are using SquaredUp v4.2 and above, you might like to use one of the mustache style code snippets provided. Once you've specified a scope (as described above) then you can either start with one of the snippets provided, or write your own dynamic query string. SquaredUp v4.2 and above support multi-object query building based on a list of objects from the tile's scope, and the use of JavaScript to manipulate the SCOM property values using functions such as split() and substring().

    When you click the mustache editor button {{}} you will see a list of example scope code snippets, along with the snippet result based on the selected scope context for the tile.

    Web API Snippets

    For example, you can add the JavaScript below after the UpdateSummary line and before the | summarize line to inject the scope and filter the results by the computers in the scope:

    | where Computer in ({{scope.map(item => '\"'+item.displayName+'\"').join(',')}})

    To show:

    	UpdateSummary
    	| where Computer in ({{scope.map(item => '\"'+item.displayName+'\"').join(',')}})
    	| summarize max(TimeGenerated) by Computer
    	| project Computer, TimeGenerated = max_TimeGenerated
    	| join (
    		UpdateSummary
    		| where TotalUpdatesMissing > 0 or RestartPending == true
    	)
    	on Computer, TimeGenerated
    	| project-away Computer1, TimeGenerated1
    	| order by TimeGenerated desc

    We're working on a dashboard, but the Azure Log Analytics tile scope and dynamic queries can be particularly useful on perspectives. For example, you might like to show security logs for a dynamic list of servers found as children of an Enterprise Application (EA) object. See Scoping tiles on perspectives.

  7. Leave the timeframe on 24 hours. For other queries, be careful with extending the timeframe beyond 7 days, as this may pull many thousands of log entries and significantly impact browser performance.
  8. Configure the desired columns on the grid columns panel. As a best practise, once you know which columns you want to display, modify the Log Analytics query to only return those columns (via project or project away), as it will improve performance when loading and displaying the tile.
  9. You can customise the appearance of columns by clicking the edit link next to it's name in the grid columns panel and then specifying a custom template. For example, locate the TimeGenerated column and set the template to {{timeago(value)}} to show a friendly time in the form Last x minutes rather than the specific time. For more information see How to use the Grid designer when configuring tiles.
  10. Click done.

The Log Analytics query language (KQL) is very rich and offers features such as sorting, projection and calculated values, which you can use to control the display of data in your dashboard.

Restricting data to a specific timeframe

By default the tile will not return any entries older than 24 hours. You can use the timeframe panel to control this behaviour and select various values. If you attempt to load a large timeframe that may contain many thousands of records, this may cause significant browser delays. You can also include further timeframe restrictions in your query (such as where timegenerated >= ago(2h)) if you need more granularity, but be aware events must satisfy both where clauses and the timeframe settings).

Querying multiple (cross-resource) workspaces

The Azure Log Analytics API allows you to send a query to multiple workspaces simultaneously using implicit and explicit cross-resource unions. The Log Analytics tile supports both mechanisms, with implicit being easier but explicit offering more control over how data is returned. Regardless of which mechanism you use, you may only query across 10 workspaces with a single cross-resource query (and therefore a single tile), and the provider's configured workspace is always used.

Identifying resources

In order to specify another workspace, you will need to specify one of the below identifiers (all are supported):

  • Resource name: The human readable name of the resource. If used this must be unique to all Azure subscriptions the provider has access to or the query will fail as ambiguous.
  • Qualified name: The "full name" of the workspace, in the format <subscriptionName>/<resourceGroup>/<workspaceName>. This may still be ambiguous as subscription names are not unique, but it is extremely unlikely.
  • Workspace ID: This is a GUID (e.g. b438b4f6-912a-46d5-9cb1-b44069212ab4) and is completely unique and unambiguous.
  • Azure Resource ID: This is a string in the form /subscriptions/<subscriptionId>/resourcegroups/<resourceGroup>/providers/microsoft.OperationalInsights/workspaces/<componentName>, which whilst unambiguous is extremely long and difficult to work with.

Implicit Unions

When making use of implicit unions, you provide a query to the tile and specify up to 9 other additional workspaces identifiers. The query is automatically sent to the provider's configured workspace and any others you specify, and the output joined together in a single result for further processing. To configure an implicit cross-resource query:

  1. In the query panel in the Azure Log Analytics tile click add under additional workspaces.
  2. Enter an identifier for your second workspace (using any of the formats discussed above) and press enter or deselect the text box.
  3. Either repeat the process by clicking add again and adding additional workspaces, or click next and continue to configure the tile.

Explicit Unions

In contrast to implicit unions, explicit ones are specified directly within your query using the Union statement, and allow you to pull in a specific subset of the data in the other workspaces. The example below shows results from the provider's workspace, along with only security updates from another named contosoretail:

union Update, (workspace("contosoretail").Update | where Classification == "Security Updates" )
| where TimeGenerated >= ago(1h)
| where UpdateState == "Needed"
| summarize dcount(Computer) by Classification

For further information check out this Azure blog post on Querying across resources.

Troubleshooting

Attempts to authorise the provider fail

Ensure that you have added the Reply URL to the Azure AD application, and that you have given Azure enough time to replicate the configuration (often takes several minutes).

"The API returned a 400 response" with message "Failed to resolve entity 'xxxxx'

Typically this indicates that a solution is missing from the queried workspace, resulting in it not containing the requested table (such as Update or UpdateSummary missing from workspaces without the Software Management solution enabled).

Azure Log Analytics Query Language

Getting started with queries

An Overview of SquaredUp Licensing

How to use the Grid designer when configuring tiles

Querying across resources

How to use Custom labelling

Squared Up Ltd. (c) 2020Report an issue with this article