Connect to Turbot Pipes from Azure Data Studio

Azure Data Studio is a cross-platform database tool for data exploration and visualization that connects to many databases, including Postgres, and enables users to monitor, query, and visualize data.

Steampipe provides a single interface to all your cloud, code, logs and more. Because it's built on Postgres, Steampipe provides an endpoint that any Postgres-compatible client -- including Azure Data Studio -- can connect to.

You can get the information needed to connect to your Turbot Pipes database instance from the Query tab for your workspace. On the Query tab, click the info button at the top of the query window to show the connection information.

Connect to Steampipe CLI from Azure Data Studio

You can also connect Azure Data Studio to Steampipe CLI. To do that, run steampipe service start --show-password and use the displayed connection details.

Steampipe service is running:
Database:
  Host(s):            localhost, 127.0.0.1, 192.168.29.204
  Port:               9193
  Database:           steampipe
  User:               steampipe
  Password:           99**_****_**8c
  Connection string:  postgres://steampipe:99**_****_**8c@localhost:9193/steampipe

Getting started

Azure Data Studio is available to use on the desktop. First let's create a Turbot Pipes connection from Azure Data Studio, then chart Apple's hourly price history using the Finance plugin.

To create a new connection, first install the PostgreSQL extension from the Extensions tab in the sidebar. Click on New Connection from the Connections tab, select PostgreSQL as the Connection type and add the connection details. Click Advanced and update the Port number and set the SSL mode to Require.

Once the database is connected, you can load plugins and the tables from the navigation bar.

Now to create a chart, first right click on the database name, select New Query and paste this query.

select
  close
from
  finance_quote_hourly
where
  symbol = 'AAPL'
order by
  timestamp desc

Data studio previews the data in a table form. To convert it into a visual, click Chart from the sidebar and select Chart Type as Bar. The data can be saved in CSV, XML, JSON, Excel formats or as an Image.

You can save the chart configuration and add it as a custom widget to display data in a dashboard. To do that, click Create Insight under the Chart tab and save the code displayed in a JSON format.

{
  "name": "Apple hourly price history",
  "gridItemConfig": {
    "sizex": 2,
    "sizey": 1
  },
  "widget": {
    "insights-widget": {
      "type": {
        "bar": {
          "dataDirection": "horizontal",
          "columnsAsLabels": true,
          "labelFirstColumn": false,
          "legendPosition": "none",
          "xAxisMin": "2022-12-05T17:41",
          "xAxisMax": "2022-12-06T17:41",
          "dataType": "point"
        }
      },
      "queryFile": "Users/<user>/applquery.sql"
    }
  }
}

Create a dashboard to analyze Azure resources

The charts made with Insights widget are the building blocks of a dashboard. Here we'll build a dashboard that monitors and analyzes Azure resources. To begin, create charts with these four queries using Doughnut for Storage accounts with versioning disabled, Line for Disk metric read ops daily, Table for List of unattached disks and Pie for Virtual machine count per region. Then, click Create Insight to save their JSON configurations.

Storage accounts with versioning disabled

select
  count(name)
from
  azure_storage_account
where
  not blob_versioning_enabled;

Disk metric read ops daily

select
  name,
  timestamp,
  minimum,
  maximum,
  average,
  sample_count
from
  azure_compute_disk_metric_read_ops_daily
order by
  timestamp;

List of unattached disks

select
  name,
  disk_state,
  sku_tier,
  time_created,
  encryption_type,
  network_access_policy
from
  azure_compute_disk
where
  disk_state = 'Unattached';

Virtual machine count per region

select
  region,
  count(name)
from
  azure_compute_virtual_machine
group by
  region;

To build, open Dashboard under Preferences: Open User Settings and click Edit in settings.json for Dashboard > Database: Widgets. Paste the insight JSON configuration for the visuals under dashboard.database.widgets. To note here, Data Studio requires the queries to be saved in a .sql file with the queryFile: configuration property pointing at its path.

Save the user settings and right-click on the database name and select Manage to display the Dashboard.

Summary

With Azure Data Studio and Turbot Pipes you can:

View tables in your Turbot Pipes workspace
Write custom queries to preview data from the tables in your Turbot Pipes workspace
Create insight widgets for dashboards driven by your custom queries