Dashboarding your KQL queries

SquaredUp is a flexible dashboarding and analytics platform that makes it really easy to turn your KQL queries into dashboards that you can use for monitoring or sharing. In this article, we’ll take a look at getting started with the Azure plugin for SquaredUp and build our first dashboard.

Example – An Azure audit dashboard

The example we’ll use for our introduction is a very simple dashboard showing user activity in Azure. We could use this to monitor any changes to our application resources, for example. It’s a nice simple KQL query that is available in all Azure accounts, so it makes for a useful starting point as an example. You’ll find many more examples over in our Dashboard Gallery, such as Dashboard Azure Entra ID (Active Directory) SignIns with KQL, Aggregated Azure Log Analytics dashboard and Azure OpenAI Metrics dashboard.

Here’s the dashboard we’ll create:

Let’s walk through how to build it in SquaredUp.

Why SquaredUp

If you’re reading this then you’re probably looking for an easier way to dashboard your Azure KQL data. There are ways to do it in the Azure portal using native dashboards and workbooks, but it has a few limitations.

1. Flexibility – To create different visualizations with your KQL query, you need to make sure that the query itself is structured accordingly, which can be a significant effort at times.
   Also, you can only run the query on one object at a time (selected scope), so it is a repetitive task if you want to run it on multiple objects such as Log Analytics Workspaces, Subscriptions, etc.
2. Simplicity – It requires a considerable amount of KQL knowledge and general know-how around the Azure portal itself to be able to create dashboards in Azure. This is especially true in the case of Workbooks, as they can get pretty complex as you add different components to it.
3. Sharing – To share a dashboard in the portal to other users, the dashboard must be published as an Azure resource and the users must have an identity in Azure Entra ID with permissions assigned to view that dashboard.
   This makes it unnecessarily complicated to share your dashboards with users who have no need to have this level of access and/or external users.
4. Combining with other data sources – The only data source Azure recognizes in its dashboards is Azure. If you're not somehow getting the data external to Azure into Azure storage or LA Workspaces, there is no way in Azure to visualize it.

SquaredUp takes away these limitations and adds more benefits on top.

SquaredUp is a dedicated dashboard that plugs into over 70 data sources, including Azure. It’s ideal for creating customized, aggregated views across your data. It’s also easy to get started – just sign up for a free account if you’d like to follow along.

Building your first dashboard

Connect SquaredUp to your Azure account

To build dashboards with KQL queries, we need to use the Azure plugin for SquaredUp. The Azure plugin comes with a ton of other Azure integrations such as cost, metrics, resource graph and so on. In this article we’ll just focus on using it for KQL.

In a new or existing workspace, navigate to the data source page, search for Azure and click to add the Azure plugin. This will open the configuration screen.

We have two choices for authenticating with Azure:

• Single sign on
• Explicit app credentials

Using single sign-on is by far the easiest, but in production environments you might want to use explicit app credentials (see docs).

That’s all there is to it. Once we connect to Azure, SquaredUp indexes the objects in the Azure account. Depending on your Azure account size, this may take anywhere from a few seconds to a few minutes. Indexing makes it easy to scope and filter dashboards, as well as drilldown into individual Azure resources. You can read more about indexing here.

All the help you may need in adding this is easily accessible in the wizard itself.

Browsing the pre-built dashboards

Once you’re connected to your Azure account, you’ll be able to browse all of the objects indexed, and also view the pre-built “out of the box” dashboards that are installed with the data source. These dashboards are useful for getting quick views on cost and performance. Some of these dashboards use KQL queries and you may want to use them to build your own custom dashboards by using the ‘Copy to’ option on the tile menu. For now we’ll just take a look at them but start with a fresh dashboard for creating our audit view.

Creating a custom dashboard

To create a new dashboard, hit the ‘+’ button on the Dashboards menu. This creates us a blank canvas.

Add a tile by clicking ‘+’ within the dashboard. This will open the tile editor, where we see the ‘data streams’ for the Azure data source. Each data stream gives us access to a different Azure API or dataset. As you can see, there are quite a few of them!

To use KQL, search for ‘KQL’ in the search box. You’ll see that there are a few different options even for KQL queries. The easiest one to get started with is ‘KQL Log Analytics’. You can use the other KQL data streams for more advanced use cases like querying the Azure Resource Graph and querying using dynamic filters from the indexed objects.

On the next screen, you can select the resource you want to run the query against. Just like in the Azure portal, you can select the specific workspace, the resource group or the subscription in the scope.

Let's go ahead and choose the subscription.

Next thing to do is paste in our KQL query. We’re going to use a very simple query against the AzureActivity dataset:

AzureActivity | project ActivityStatus, OperationName, Level, TimeGenerated, ResourceGroup, Resource
Hit apply and voila, we have a result!

On the next screens, you can apply the timeframe and some data shaping options, we'll get back to that in minute.

Our query results are returned as a table by default, but we can now use the capabilities of Squared Up to create visualizations and really bring our data alive.

First let's start with the line graph. For that, all I have to do is pick the "Line" visualization from the menu on the right and it'll tell me what to do.

As you can see, it has automatically picked up the "Time Generated" column from our result since it's the best fit for a line graph. Fair enough - now we need to set up the Y-axis. Let's plot a trend graph of how many activities were performed within the given points of time. I'll switch over to the "Shaping" tab for that.

If I group the activities by the given timeframe (within the hour), and count the number of them - I can plot a trend graph for various points of time. Like so:

And the graph has already populated! Sweet, that was easy. From the "Timeframe" tab I can change the time period for which I want the query to run and the graph will change accordingly.

Let's try something else. Maybe a donut of Activity Status column by the categories.

Everything same as the above, but this time instead of "Line", choose "Donut". Go to "Shaping" again and group the results by the value and count the number. Like so:

And just like that, the donut is ready too!

Repeat this with a few tiles, and give the dashboard a name. Here’s our finished dashboard:

Next Steps - Monitoring and Notifications

There is a lot more you can do with SquaredUp dashboards – let’s finish off with two of the most common use cases: monitoring and sharing.

SquaredUp monitoring makes it easy to turn our dashboard tiles into monitors, so that we can be alerted about changes. For example, let’s say we want to be alerted if there are any failed executions in the last 24 hours. We can create a new tile and filter the events. And then we simply toggle on monitoring and set a threshold of zero.

Not only can I see this in SquaredUp, I can also set up notifications to let me know if any alerts are raised so I can be aware anytime. They can be sent as an email, as an IM message or forward it over to any of your automation workflows.

If the monitor triggers, we can receive a notification by email, Slack, Teams or via any system that supports webhooks. You can learn more about monitoring here.

Lastly, let’s share this dashboard with others. Unlike the Azure console, the people we share this with won’t need access to the Azure console, and they won’t need to go digging around the Azure console to find the right data. This will be just a clean simple dashboard that we could share alongside other important data.

Sharing is very simple in SquaredUp. We just hit the share button and have the options of inviting a user to the workspace, or sharing just the dashboard via a link:

Now that you've seen how quick and easy it is to turn any KQL into visualizations - try it out yourself and see what you'd like to do with it!

Get started with SquaredUp

That was a very simple example, but hopefully you have a flavor of how easy is to start dashboarding your KQL with SquaredUp. Now you’re up and running with KQL, you might also be interested in adding Azure DevOps, M365 or SQL. SquaredUp has over 60 data sources, including PowerShell and WebAPI for unlimited extensibility. You can get started with a free account, or check out our flexible pricing for dashboarding at scale.

Happy dashboarding!

Happy dashboarding!