Skip to main content

How to use the Azure Log Analytics tile

Catherine
  • Updated

The Azure Log Analytics tile allows you to query Log Analytics data from Azure Monitor and display that information in your SquaredUp dashboard.

LogsDonutGraphDM.png

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.

Configuring the Azure Log Analytics tile

See How to configure an Azure Log Analytics provider

Log Analytics tile types

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

Snag_122e8afd.png

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

Log Analytics (Scalar)

Scalar will show a single value:

Snag_122f7906.png

Log Analytics (Grid)

Grid will show a grid or table of data:

Snag_12300a69.png

LogGridDM.png

Log Analytics (Bar Graph)

mceclip0.png

2020-07-13_13_38_33-New_Dashboard_-_SquaredUp.png

The Logs bar graph is available in v4.7 and above. You can choose to sort or not sort the results using the KQL query, for example:

| top 5 by max_counterValue asc

Log Analytics (Line Graph)

Line Graph will show the queried data as a graph:

Snag_12310a06.png

LogsLineGraph2DM.png

Log Analytics Donut

Donut will show a maximum of 10 categories from the queried data as a donut:

Snag_1231ad99.png
LogsDonutGraphDM.png

Log Analytics tile options

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 configure the Azure Log Analytics tile

Query

KQL

The Logs tile uses 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.

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

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.

Queries in a donut tile should return a maximum of 10 categories. This can be achieved by using the top or limit operators in your query.

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.

mceclip3.png

Timestamp - Line graphs will automatically pick a time value to use for the x-axis, or you can make your own choice here.

Value - Where there are multiple values it is possible to display these by expanding the column overrides section and then ticking the show all box next to value.

Grouping - If your results have more than one numeric column, SquaredUp will simply pick one to use as the line graph Y axis, or you can select the one you want here.

Timeframe

The tile timeframe section allows you to determine how the time period for the data is chosen, even when your query doesn't specify a timeframe. Within each tile configuration, you can set a specific time range which will be fixed.

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

If your query specifies a timeframe

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.

If you want to control the timeframe using only the query, set the tile timeframe to time range > all.

mceclip1.png

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 to 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 labels

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 Log Analytics tile to.

  2. Add a new tile, then click on Azure > Log Analytics (Grid)

    Snag_122e8afd.png

    Snag_12300a69.png 

  3. In the provider select the provider you created earlier.

  4. 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
  5. 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.
  6. 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.
  7. 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.
    mceclip2.png
  8. Click done.
    Snag_12448f66.png

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).

Related articles