It's been a while since I gave Steampipe a run, and wow, has it had some excellent updates!
I needed to get some network info about our Express Routes from some of our subscriptions, so this post covers getting it up and running with Azure.
Installation
I'm on a Mac, so that was a breeze. Head to steampipe.io and follow the steps there.
$ brew install turbot/tap/steampipe
$ steampipe -v
steampipe version 0.19.4
Plugins
As we will be working in Azure, we must install Azure and the Azure Active Directory plugins.
steampipe plugin install azure
steampipe plugin install azuread
azure [====================================================================] Done
Installed plugin: azure@latest v0.41.0
Documentation: https://hub.steampipe.io/plugins/turbot/azure
azuread [====================================================================] Done
Installed plugin: azuread@latest v0.9.0
Documentation: https://hub.steampipe.io/plugins/turbot/azuread
These plugins need Azure Cli, so again, I just followed the instruction here
brew update && brew install azure-cli
Authentication
This was the first time I'd set up authentication for Steampipe to Azure, but the doco here was very helpful.
Firstly we need to authenticate our Azure Cli session via az login.
After authenticating via the browser, I get my list of subscriptions.
[
{
"cloudName": "AzureCloud",
"homeTenantId": "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee",
"id": "ffffffff-gggg-hhhh-iiii-jjjjjjjjjjjj",
"isDefault": true,
"managedByTenants": [
{
"tenantId": "kkkkkkk-llll-mmmm-nnnn-oooooooooooo"
}
],
"name": "Subscription.01",
"state": "Enabled",
"tenantId": "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee",
"user": {
"name": "sjramblings@sjramblings.io",
"type": "user"
}
.....
As we have multiple subscriptions, we need to grab the id value in the output for each subscription we want to query.
This value then gets added to ~/.steampipe/config/azure.spc
cat ~/.steampipe/config/azure.spc
connection "azure" {
plugin = "azure"
tenant_id = "ffffffff-gggg-hhhh-iiii-jjjjjjjjjjjj"
}
NOTE: Create the above with a unique connection name if you have multiple subscriptions.
With multiple connections, you can create an aggregator that selects the appropriate connections by name, allowing you to query multiple subscriptions concurrently.
connection "azure_all" {
type = "aggregator"
plugin = "azure"
connections = ["azure_*"]
}
Now that our connection is configured let's test it quickly.
Firstly we fire up the interactive console using steampipe query and perform a select * to grab everything from the azure_subscription table.
$ steampipe query
Welcome to Steampipe v0.19.4
For more information, type .help
> select * from azure_subscription
+-----------------------------------------------------+--------------------------------------+----------------------------+--------------------------------------+--------->
| id | subscription_id | display_name | tenant_id | state >
+-----------------------------------------------------+--------------------------------------+----------------------------+--------------------------------------+--------->
| /subscriptions/ffffffff-gggg-hhhh-iiii-jjjjjjjjjjjj | ffffffff-gggg-hhhh-iiii-jjjjjjjjjjjj | Subscription.01 | aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee | Enabled >
+-----------------------------------------------------+--------------------------------------+----------------------------+--------------------------------------+--------->
As you can see, our auth is working, and we get the details of our subscription back from the query.
Azure Insights Mod
To see what was already available without having to write any queries, I pulled down the Azure Insight Mod available here.
This is a collection of dashboards and queries that provide an excellent overview of the configuration of the Azure environment.
Accessing the mod is as simple as cloning the repo, jumping into that directory, and starting steampipe dashboard.
git clone https://github.com/turbot/steampipe-mod-azure-insights.git
cd steampipe-mod-azure-insights
steampipe dashboard
Network Dashboard
As I'm after some network information, a search for network-related dashboards gives me the following:-
I recommend checking out some great dashboards, but no Express Route info, so I must go it alone.
With the vision of pushing this back to the public GitHub repo, I explored how to extend the dashboards to include Express Route.
Creating Express Route Queries
Firstly we need to populate our queries and dashboard containers. I created the following additional files under dashboards.
network_express_route_dashboard will provide our top-level entry dashboard, while network_express_route_detail will be per Express Route.
steampipe-mod-azure-insights/dashboards/network/network_express_route_dashboard.sp
steampipe-mod-azure-insights/dashboards/network/network_express_route_detail.sp
As part of the dashboard specification, I also need to populate the docs folder with markdown files describing the questions the dashboards answer.
This approach forces the contributor to think about what information should be displayed.
steampipe-mod-azure-insights/dashboards/network/docs/network_express_route_dashboard.md
steampipe-mod-azure-insights/dashboards/network/docs/network_express_route_detail.md
Network Express Route Dashboard
As part of the dashboard, we need to define the following section, which gives it a unique title, points to the documentation markdown file, and applies any common tags.
dashboard "network_express_route_dashboard" {
title = "Azure Express Route Circuit Dashboard"
documentation = file("./dashboards/network/docs/network_express_route_dashboard.md")
tags = merge(local.network_common_tags, {
type = "Dashboard"
})
Next up, we define our container. A container is a group of cards, tables, etc., represented in a row.
The following container blocks define three rows that will display:-
- Express Route Circuit Count
- Express Route Circuits by Subscription
- Express Route Circuits by Region
- Express Route Circuits by Provisioning State
- Express Route Circuits by Sku Tier
- Service Provider Properties
container {
card {
query = query.express_route_circuit_count
width = 3
}
}
container {
title = "Analysis"
chart {
title = "Express Route Circuits by Subscription"
query = query.express_route_circuit_by_subscription
type = "column"
width = 3
}
chart {
title = "Express Route Circuits by Region"
query = query.express_route_circuit_by_region
type = "column"
width = 3
}
chart {
title = "Express Route Circuits by Provisioning State"
query = query.virtual_network_by_provisioning_state
type = "column"
width = 3
}
chart {
title = "Express Route Circuits by Sku Tier"
query = query.express_route_circuit_by_sku_tier
type = "column"
width = 3
}
}
container {
table {
title = "Service Provider Properties"
width = 12
query = query.network_express_route_service_provider_properties
}
}
}
Our next step is to create the essential queries that will populate our containers.
To get the number of Express Routes we do a simple count from the azure_express_route_circuit table.
query "express_route_circuit_count" {
sql = <<-EOQ
select count(*) as ExpressRoutes from azure_express_route_circuit;
EOQ
}
To determine the number of Express Route circuits per Azure subscription, we need to extract data from azure_express_route_circuit and azure_subscription tables and filter the data based on a condition that matches "subscription_id" columns between them.
The count function is then used to calculate the number of circuits in each subscription group. The output displays the subscription name and the corresponding number of Express Route circuits per subscription in ascending order.
query "express_route_circuit_by_subscription" {
sql = <<-EOQ
select
sub.title as "Subscription",
count(n.*) as "Express Route Circuits"
from
azure_express_route_circuit as n,
azure_subscription as sub
where
sub.subscription_id = n.subscription_id
group by
sub.title
order by
sub.title;
EOQ
}
To get the number of Express Routes per Region, Provisioning State, and Sku Tier, we add the group by criteria.
query "express_route_circuit_by_region" {
sql = <<-EOQ
select
region as "Region",
count(*) as "Express Route Circuits"
from
azure_express_route_circuit
group by
region
order by
region;
EOQ
}
query "express_route_circuit_by_provisioning_state" {
sql = <<-EOQ
select
provisioning_state as "Provisioning State",
count(*) as "Express Route Circuits"
from
azure_express_route_circuit
group by
provisioning_state
order by
provisioning_state;
EOQ
}
query "express_route_circuit_by_sku_tier" {
sql = <<-EOQ
select
sku_tier as "Sku Tier",
count(*) as "Express Route Circuits"
from
azure_express_route_circuit
group by
sku_tier
order by
sku_tier;
EOQ
}
The final query is trickier as the service_provider_properties column is returned as a jsonb object. However, we can extract those values using the jsonb -> operator.
query "network_express_route_service_provider_properties" {
sql = <<-EOQ
select
sub.title as "Subscription",
n.name as "Name",
n.sku_tier as "Sku Tier",
n.sku_family as "Sku Family",
n.service_provider_properties -> 'peeringLocation' as "Peering Location",
n.service_provider_properties -> 'bandwidthInMbps' as "Bandwidth In Mbps",
n.service_provider_properties -> 'serviceProviderName' as "Service Provider Name"
from
azure_express_route_circuit as n,
azure_subscription as sub
order by
name;
EOQ
}
With our containers and queries configured, our dashboard now looks like this.
Summary
In this post, we walked through setting up Azure authentication for Steampipe and added an Express Route dashboard to the Azure Insights Mod.
It shows how easy it is to represent your desired data via Steampipe.
I do have a pull request open for the updates, but it's early days. If you want to review the files I updated they are available here.
I hope this helps someone else.
Cheers