Dashboards

Dashboards keep your frequent SQL queries as chart panels, for quick views of your infrastructure. Dashboards are saved in your browser, so other users do not see them.

Creating dashboards

Create dashboards on the Dashboards page of the console. Each panel of a dashboard is a saved SQL query:

Panels

A panel shows its query as a bar, pie, or treemap chart. The chart counts rows per value of one field, for example resources by type or by provider. This query counts resources by type:

SELECT type, count(*) as type_count
FROM resources
GROUP BY type
ORDER BY count(*) DESC
LIMIT 10

Example dashboards

Infrastructure overview

Security dashboard

Resource type is in the resources table, so join instances to resources to filter by type.

Security groups:

SELECT i.address, i.attributes->>'name' AS name
FROM instances AS i
INNER JOIN resources AS r
  ON i.resource_address = r.address AND i.state_id = r.state_id
WHERE r.type = 'aws_security_group'

IAM roles:

SELECT i.address, i.attributes->>'name' AS name
FROM instances AS i
INNER JOIN resources AS r
  ON i.resource_address = r.address AND i.state_id = r.state_id
WHERE r.type = 'aws_iam_role'

Instance type distribution

Count instances by EC2 type or RDS class. For actual cost data, see Cost intelligence.

SELECT i.attributes#>>'{instance_type}' AS instance_type, COUNT(*) AS instance_count
FROM instances AS i
INNER JOIN resources AS r
  ON i.resource_address = r.address AND i.state_id = r.state_id
WHERE r.type = 'aws_instance'
GROUP BY i.attributes#>>'{instance_type}'
ORDER BY COUNT(*) DESC

Useful queries

Resource inventory

All resources by type:

SELECT type, count(*) as type_count
FROM resources
GROUP BY type
ORDER BY type

Resources by workspace:

SELECT s.workspace, count(*) as resources
FROM resources AS r
INNER JOIN states AS s ON r.state_id = s.id
GROUP BY s.workspace
ORDER BY s.workspace

Compliance

Resources without tags:

SELECT address, resource_address
FROM instances
WHERE attributes->'tags' IS NULL
   OR attributes->>'tags' = '{}'

Resources without the required Environment tag:

SELECT address, resource_address
FROM instances
WHERE attributes->'tags'->>'Environment' IS NULL

Security

Security groups:

SELECT i.address, i.attributes->>'name' AS name
FROM instances AS i
INNER JOIN resources AS r
  ON i.resource_address = r.address AND i.state_id = r.state_id
WHERE r.type = 'aws_security_group'

IAM users. Keep their number small:

SELECT i.address
FROM instances AS i
INNER JOIN resources AS r
  ON i.resource_address = r.address AND i.state_id = r.state_id
WHERE r.type = 'aws_iam_user'

Change tracking

States changed most recently. Stategraph sets updated_at when a transaction writes to the state:

SELECT name, workspace, updated_at
FROM states
ORDER BY updated_at DESC
LIMIT 10

Sharing dashboards

To share a view:

• Copy the SQL of each panel, so that others can recreate or save it.
• Export the results of a query from the Query page, for example to CSV, as a point-in-time snapshot.

Best practices

• Use names that tell the purpose, and describe what each panel shows.
• Group related queries. Create dashboards per team or function, and keep operational and security dashboards apart.
• Add date ranges where relevant.
• Use LIMIT for large result sets, filter early, and avoid SELECT * when possible.
• Review dashboards regularly as your infrastructure changes: check queries after major changes, and update dashboards when you add new resource types.
• Remove obsolete panels, and archive outdated dashboards.

Next steps

• Query (SQL): SQL syntax.
• API Reference: programmatic access.
• Dashboard metrics: console home metrics.
• Timeline: transaction history.