Connect to Turbot Pipes from Grafana
Grafana is a visualization tool that connects to many databases including Postgres, and enables users to query, monitor, create alerts and analyze metrics.
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 Grafana -- 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 Grafana
You can also connect Grafana 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/steampipeGetting started
Grafana is an open-source interactive data-visualization platform that runs on the cloud, or in a container, or on the desktop. To use Grafana you will need to sign up and create an account. Here, we will use the desktop version.
With Grafana up and running, open a browser to http://localhost:3000. Click on
Data Sources under the configuration option, then click Add Data Source,
select PostgreSQL, and enter the connection details. Since there is no field
for port number, use the syntax Host:Port. Set the TLS/SSL mode to Require,
then click Save & Test to test your connection.
The plugins and its tables are available from the Explore tab. Use Edit SQL
to customize the queries.
The data can be previewed in a Table format or Time series. Here we see the
AWS EC2 instances listed in a table format.
Dashboard to monitor resources and cost
Panels that display charts and tables are the building blocks of Grafana
dashboards. We'll focus here on a dashboard to monitor AWS services and costs.
To begin, click Dashboards and create a new dashboard. On the empty dashboard
click Add panel, then select aws_ebs_volume_metric_read_ops_daily from the
query builder and click Apply. Then add panels for
aws_ec2_instance_metric_cpu_utilization_hourly, aws_s3_bucket_by_region and
aws_vpc_by_region. And finally paste this query, which monitors the top ten
monthly costs by service, into a new panel.
select service, sum(unblended_cost_amount)::numeric::money as sum, avg(unblended_cost_amount)::numeric::money as averagefrom aws_cost_by_service_monthlygroup by serviceorder by average desclimit 10;Grafana provides a variety of chart types. Here we use Time series for
aws_ebs_volume_metric_read_ops_daily, Bar chart for
aws_ec2_instance_metric_cpu_utilization_hourly, Pie chart for
aws_s3_bucket_by_region, Bar gauge for aws_vpc_by_region and Table for top
ten monthly costs by service.
Import a dashboard with JSON file
A Grafana dashboard is represented by a JSON object that stores its metadata. Here is the JSON definition for an AWS S3 dashboard.
AWS S3 JSON
{ "annotations": { "list": [ { "builtIn": 1, "datasource": { "type": "datasource", "uid": "grafana" }, "enable": true, "hide": true, "iconColor": "rgba(0, 211, 255, 1)", "name": "Annotations & Alerts", "target": { "limit": 100, "matchAny": false, "tags": [], "type": "dashboard" }, "type": "dashboard" } ] }, "editable": true, "fiscalYearStartMonth": 0, "graphTooltip": 0, "id": 4, "links": [], "liveNow": false, "panels": [ { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "fieldConfig": { "defaults": { "mappings": [], "thresholds": { "mode": "percentage", "steps": [ { "color": "green", "value": null }, { "color": "orange", "value": 70 }, { "color": "red", "value": 85 } ] } }, "overrides": [] }, "gridPos": { "h": 8, "w": 5, "x": 0, "y": 0 }, "id": 13, "options": { "orientation": "auto", "reduceOptions": { "calcs": ["lastNotNull"], "fields": "", "values": false }, "showThresholdLabels": false, "showThresholdMarkers": true }, "pluginVersion": "9.2.2", "targets": [ { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "format": "table", "group": [], "metricColumn": "none", "rawQuery": true, "rawSql": "select\n count(*)\nfrom\n aws_s3_bucket\nwhere\n logging ->> 'TargetBucket' = name;", "refId": "A", "select": [ [ { "params": ["average"], "type": "column" } ] ], "table": "aws_rds_db_instance_metric_cpu_utilization_hourly", "timeColumn": "\"timestamp\"", "timeColumnType": "timestamp", "where": [ { "name": "$__timeFilter", "params": [], "type": "macro" } ] } ], "title": "Logging Destination Same As The Source Bucket", "type": "gauge" }, { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "fieldConfig": { "defaults": { "mappings": [], "thresholds": { "mode": "percentage", "steps": [ { "color": "green", "value": null }, { "color": "orange", "value": 70 }, { "color": "red", "value": 85 } ] } }, "overrides": [] }, "gridPos": { "h": 8, "w": 5, "x": 5, "y": 0 }, "id": 1, "options": { "orientation": "auto", "reduceOptions": { "calcs": ["lastNotNull"], "fields": "", "values": false }, "showThresholdLabels": false, "showThresholdMarkers": true }, "pluginVersion": "9.2.2", "targets": [ { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "format": "table", "group": [], "metricColumn": "none", "rawQuery": true, "rawSql": "select\n count(*)\nfrom\n aws_s3_bucket\nwhere\n not block_public_acls\n or not block_public_policy\n or not ignore_public_acls\n or not restrict_public_buckets;", "refId": "A", "select": [ [ { "params": [ "data_transfer_progress_current_rate_in_mega_bytes_per_second" ], "type": "column" } ] ], "table": "aws_redshift_clusters", "timeColumn": "cluster_create_time", "timeColumnType": "timestamp", "where": [ { "name": "$__timeFilter", "params": [], "type": "macro" } ] } ], "title": "Public Access Block Disabled", "type": "gauge" }, { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "fieldConfig": { "defaults": { "mappings": [], "thresholds": { "mode": "percentage", "steps": [ { "color": "green", "value": null }, { "color": "orange", "value": 70 }, { "color": "red", "value": 85 } ] } }, "overrides": [] }, "gridPos": { "h": 8, "w": 5, "x": 10, "y": 0 }, "id": 3, "options": { "orientation": "auto", "reduceOptions": { "calcs": ["lastNotNull"], "fields": "", "values": false }, "showThresholdLabels": false, "showThresholdMarkers": true }, "pluginVersion": "9.2.2", "targets": [ { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "format": "table", "group": [], "metricColumn": "none", "rawQuery": true, "rawSql": "select\n count(*)\nfrom\n aws_s3_bucket,\n jsonb_array_elements(policy_std -> 'Statement') as s,\n jsonb_array_elements_text(s -> 'Principal' -> 'AWS') as p,\n jsonb_array_elements_text(s -> 'Action') as a,\n jsonb_array_elements_text(\n s -> 'Condition' -> 'Bool' -> 'aws:securetransport'\n ) as ssl\nwhere\n p = '*'\n and s ->> 'Effect' = 'Deny'\n and ssl :: bool = false;", "refId": "A", "select": [ [ { "params": [ "data_transfer_progress_current_rate_in_mega_bytes_per_second" ], "type": "column" } ] ], "table": "aws_redshift_clusters", "timeColumn": "cluster_create_time", "timeColumnType": "timestamp", "where": [ { "name": "$__timeFilter", "params": [], "type": "macro" } ] } ], "title": "Enforced Encryption In Transit", "type": "gauge" }, { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "fieldConfig": { "defaults": { "mappings": [], "thresholds": { "mode": "percentage", "steps": [ { "color": "green", "value": null }, { "color": "orange", "value": 70 }, { "color": "red", "value": 85 } ] } }, "overrides": [] }, "gridPos": { "h": 8, "w": 5, "x": 15, "y": 0 }, "id": 5, "options": { "orientation": "auto", "reduceOptions": { "calcs": ["lastNotNull"], "fields": "", "values": false }, "showThresholdLabels": false, "showThresholdMarkers": true }, "pluginVersion": "9.2.2", "targets": [ { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "format": "table", "group": [], "metricColumn": "none", "rawQuery": true, "rawSql": "select\n count(*)\nfrom\n aws_s3_bucket\nwhere\n versioning_enabled = false;", "refId": "A", "select": [ [ { "params": ["average"], "type": "column" } ] ], "table": "aws_rds_db_instance_metric_cpu_utilization_hourly", "timeColumn": "\"timestamp\"", "timeColumnType": "timestamp", "where": [ { "name": "$__timeFilter", "params": [], "type": "macro" } ] } ], "title": "Versioning Disabled", "type": "gauge" }, { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "fieldConfig": { "defaults": { "mappings": [], "thresholds": { "mode": "percentage", "steps": [ { "color": "green", "value": null }, { "color": "orange", "value": 70 }, { "color": "red", "value": 85 } ] } }, "overrides": [] }, "gridPos": { "h": 8, "w": 5, "x": 0, "y": 8 }, "id": 9, "options": { "orientation": "auto", "reduceOptions": { "calcs": ["lastNotNull"], "fields": "", "values": false }, "showThresholdLabels": false, "showThresholdMarkers": true }, "pluginVersion": "9.2.2", "targets": [ { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "format": "table", "group": [], "metricColumn": "none", "rawQuery": true, "rawSql": "select\n count(*)\nfrom\n aws_s3_bucket,\n jsonb_array_elements(policy_std -> 'Statement') as s,\n jsonb_array_elements_text(s -> 'Principal' -> 'AWS') as p,\n string_to_array(p, ':') as pa,\n jsonb_array_elements_text(s -> 'Action') as a\nwhere\n s ->> 'Effect' = 'Allow'\n and (\n pa[5] != account_id\n or p = '*'\n );", "refId": "A", "select": [ [ { "params": ["average"], "type": "column" } ] ], "table": "aws_rds_db_instance_metric_cpu_utilization_hourly", "timeColumn": "\"timestamp\"", "timeColumnType": "timestamp", "where": [ { "name": "$__timeFilter", "params": [], "type": "macro" } ] } ], "title": "External Access Granted", "type": "gauge" }, { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "fieldConfig": { "defaults": { "mappings": [], "thresholds": { "mode": "percentage", "steps": [ { "color": "green", "value": null }, { "color": "orange", "value": 70 }, { "color": "red", "value": 85 } ] } }, "overrides": [] }, "gridPos": { "h": 8, "w": 5, "x": 5, "y": 8 }, "id": 0, "options": { "orientation": "auto", "reduceOptions": { "calcs": [], "fields": "", "values": true }, "showThresholdLabels": false, "showThresholdMarkers": true }, "pluginVersion": "9.2.2", "targets": [ { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "format": "table", "group": [], "metricColumn": "none", "rawQuery": true, "rawSql": "select account_id, count(*) from aws_s3_bucket group by account_id", "refId": "A", "select": [ [ { "params": [ "data_transfer_progress_current_rate_in_mega_bytes_per_second" ], "type": "column" } ] ], "table": "aws_redshift_clusters", "timeColumn": "cluster_create_time", "timeColumnType": "timestamp", "where": [ { "name": "$__timeFilter", "params": [], "type": "macro" } ] } ], "title": "Total Bucket Count By Account ID", "type": "gauge" }, { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "fieldConfig": { "defaults": { "mappings": [], "thresholds": { "mode": "percentage", "steps": [ { "color": "green", "value": null }, { "color": "orange", "value": 70 }, { "color": "red", "value": 85 } ] } }, "overrides": [] }, "gridPos": { "h": 8, "w": 5, "x": 10, "y": 8 }, "id": 7, "options": { "orientation": "auto", "reduceOptions": { "calcs": ["lastNotNull"], "fields": "", "values": false }, "showThresholdLabels": false, "showThresholdMarkers": true }, "pluginVersion": "9.2.2", "targets": [ { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "format": "table", "group": [], "metricColumn": "none", "rawQuery": true, "rawSql": "select\n count(*)\nfrom\n aws_s3_bucket\nwhere\n server_side_encryption_configuration is null;", "refId": "A", "select": [ [ { "params": ["average"], "type": "column" } ] ], "table": "aws_rds_db_instance_metric_cpu_utilization_hourly", "timeColumn": "\"timestamp\"", "timeColumnType": "timestamp", "where": [ { "name": "$__timeFilter", "params": [], "type": "macro" } ] } ], "title": "Default Encryption Disabled", "type": "gauge" }, { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "fieldConfig": { "defaults": { "mappings": [], "thresholds": { "mode": "percentage", "steps": [ { "color": "green", "value": null }, { "color": "orange", "value": 70 }, { "color": "red", "value": 85 } ] } }, "overrides": [] }, "gridPos": { "h": 8, "w": 5, "x": 15, "y": 8 }, "id": 11, "options": { "orientation": "auto", "reduceOptions": { "calcs": ["lastNotNull"], "fields": "", "values": false }, "showThresholdLabels": false, "showThresholdMarkers": true }, "pluginVersion": "9.2.2", "targets": [ { "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "format": "table", "group": [], "metricColumn": "none", "rawQuery": true, "rawSql": "select\n count(*)\nfrom\n aws_s3_bucket\nwhere\n object_lock_configuration ->> 'ObjectLockEnabled' = 'Enabled';", "refId": "A", "select": [ [ { "params": ["average"], "type": "column" } ] ], "table": "aws_rds_db_instance_metric_cpu_utilization_hourly", "timeColumn": "\"timestamp\"", "timeColumnType": "timestamp", "where": [ { "name": "$__timeFilter", "params": [], "type": "macro" } ] } ], "title": "Object Lock Enabled", "type": "gauge" } ], "schemaVersion": 37, "style": "dark", "tags": ["aws", "s3"], "templating": { "list": [ { "current": { "selected": true, "text": ["All"], "value": ["$__all"] }, "datasource": { "type": "postgres", "uid": "OxfC_3N4z" }, "definition": "select account_id from aws_account", "hide": 0, "includeAll": true, "multi": true, "name": "account_ids", "options": [], "query": "select account_id from aws_account", "refresh": 1, "regex": "", "skipUrlSync": false, "sort": 1, "type": "query" }, { "current": { "selected": true, "text": ["All"], "value": ["$__all"] }, "hide": 0, "includeAll": true, "multi": true, "name": "regions", "options": [ { "selected": true, "text": "All", "value": "$__all" }, { "selected": false, "text": "us-east-2", "value": "us-east-2" }, { "selected": false, "text": "us-east-1", "value": "us-east-1" }, { "selected": false, "text": "us-west-1", "value": "us-west-1" }, { "selected": false, "text": "us-west-2", "value": "us-west-2" }, { "selected": false, "text": "af-south-1", "value": "af-south-1" }, { "selected": false, "text": "ap-east-1", "value": "ap-east-1" }, { "selected": false, "text": "ap-south-1", "value": "ap-south-1" }, { "selected": false, "text": "ap-northeast-3", "value": "ap-northeast-3" }, { "selected": false, "text": "ap-northeast-2", "value": "ap-northeast-2" }, { "selected": false, "text": "ap-southeast-1", "value": "ap-southeast-1" }, { "selected": false, "text": "ap-southeast-2", "value": "ap-southeast-2" }, { "selected": false, "text": "ap-northeast-1", "value": "ap-northeast-1" }, { "selected": false, "text": "ca-central-1", "value": "ca-central-1" }, { "selected": false, "text": "eu-central-1", "value": "eu-central-1" }, { "selected": false, "text": "eu-west-1", "value": "eu-west-1" }, { "selected": false, "text": "eu-west-2", "value": "eu-west-2" }, { "selected": false, "text": "eu-south-1", "value": "eu-south-1" }, { "selected": false, "text": "eu-west-3", "value": "eu-west-3" }, { "selected": false, "text": "eu-north-1", "value": "eu-north-1" }, { "selected": false, "text": "me-south-1", "value": "me-south-1" }, { "selected": false, "text": "sa-east-1", "value": "sa-east-1" } ], "query": "us-east-2,us-east-1,us-west-1,us-west-2,af-south-1,ap-east-1,ap-south-1,ap-northeast-3,ap-northeast-2,ap-southeast-1,ap-southeast-2,ap-northeast-1,ca-central-1,eu-central-1,eu-west-1,eu-west-2,eu-south-1,eu-west-3,eu-north-1,me-south-1,sa-east-1", "queryValue": "", "skipUrlSync": false, "type": "custom" } ] }, "time": { "from": "now-6h", "to": "now" }, "timepicker": {}, "timezone": "", "title": "AWS S3", "uid": "aws_s3_json", "version": 1, "weekStart": ""}
To import it, click Import under the Dashboards icon in the side menu, then
copy and paste the JSON.
Send alerts
Suppose you'd like to be notified when the CPU utilization crosses a threshold
value. You can achieve that with Grafana's Alerting feature. To start go to
Alert Rules under the Alerting tab, then click New Alert Rule and paste this
query under Set a query and alert condition. Then we set an
Expression
with a classic condition operation to trigger an alert when the CPU utilization
goes above the set value.
select "timestamp" AS "time", averagefrom aws_ec2_instance_metric_cpu_utilization_hourlywhere $__timeFilter("timestamp")order by 1
Give your alert a name and set up Contact-points to that should receive these notifications. Here we see an alert sent to slack.
Summary
With Grafana and Turbot Pipes you can:
Create interactive dashboards driven by data from the tables and queries in your Turbot Pipes workspace
Send query-driven alerts