Follow

Follow
Getting Started with Steampipe on Azure

Getting Started with Steampipe on Azure

Stephen Jones's photo
Stephen Jones
·Apr 28, 2023·

6 min read

Play this article

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:-

Network Dashboards

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.

Express Route Dashboard

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

Did you find this article valuable?

Support Stephen Jones by becoming a sponsor. Any amount is appreciated!

See recent sponsors Learn more about Hashnode Sponsors
 
Share this