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 closefrom finance_quote_hourlywhere 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_accountwhere not blob_versioning_enabled;
Disk metric read ops daily
select name, timestamp, minimum, maximum, average, sample_countfrom azure_compute_disk_metric_read_ops_dailyorder by timestamp;
List of unattached disks
select name, disk_state, sku_tier, time_created, encryption_type, network_access_policyfrom azure_compute_diskwhere disk_state = 'Unattached';
Virtual machine count per region
select region, count(name)from azure_compute_virtual_machinegroup 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