alphagov / paas-steampipe-dashboard

a steampipe.io based dashboard for GOV.UK PaaS and related things

GOV.UK PaaS dashboard

A dashboard to monitor the GOV.UK PaaS platform.

Uses Steampipe to access all the component parts from the underlying infrastructure in AWS and Aiven through to the Cloud Foundry core of the paas, its code base and supporting tools.

see kanban board for current plan.

Originally a 🔥 firebreak experiment.

Overview

Steampipe provides a SQL layer on top of a wide range of cloud platform services that have apis using a postgresql foreign data wrapper.

This dashboard uses steampipe.io to build a set of dashboards over GOV.UK PaaS and its infrastructure.

It uses the Cloud Foundry CLI to access the Cloud Foundry API and list resources such as orgs, apps,services, the data is saved locally as csv files and accessed from a local steampipe dashboard running at http://localhost:9194

It uses the plugins configured in dashboards/mod.sp

The dashboards pull data from the underlying csv files using postgres SQL and render the results as a dashboard.

How it works

1. logs into Cloud Foundry instances using the CF CLI

cf login --sso

2. extracts data in csv format from the Cloud Foundry API

cf curl /v3/foobar | in2csv -f json -k resources converting JSON into CSV using csvkit's in2csv

3. Logs into AWS using GDS CLI using MFA and assumes a role with read only permissions

gds aws paas-prod-ro

4. extracts AWS data into CSV using steampipe aws plugin

steampipe query query.sql -- output csv

5. launches steampipe dashboard

Render results locally accessing data from plugins, running SQL queries against the normalised data using postgresql

Read about the data model

Prerequisites

Assumes you are on a mac with homebrew installed with /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# homebrew packages
brew install cf-cli@8      # Cloud Foundry CLI
brew install gawk          # GNU awk
brew install gh            # GitHub CLI
brew install glow          # Glow CLI for markdown 
brew install gnu-sed       # GNU sed
brew install jq            # JSON wrangling tool
brew install steampipe     # make cloud apis queryble via SQL 
brew install yq            # YAML tools

# python tools
pip3 install csvkit        # csv wrangling tools
pip3 install visidata      # data wrangling swiss army penknife tool

that you are on the VPN

you have a GOV.UK PaaS account with global auditor permissions and you are logged in using make login

you are a gds-user with permisssions to assume role into all the paas infrastructure AWS accounts

you have the aiven CLI configured with an access token to list services

Usage

1. install dependencies

git clone https://github.com/alphagov/paas-steampipe-dashboard

cd paas-steampipe-dashboard

make dependencies to install all the necessary packages

2. configure plugins

see config for examples

vim ~/.steampipe/config

3. extract data

you need creds to access the AWS accounts

gds aws paas-prod-ro -- make data

this logs into to AWS and Cloud Foundry, extracts data locally in csv format

4. run dashboard

make dashboard to run the dashboard with the current data and launch dashboard at http://localhost:9194

does not need Cloud Foundry or AWS creds because the dashboard uses local data

5. work with data

use steampipe service start to run locally and steampipe service status to get creds to configure your favourite postgres tools.

• vd *.csv to work with the raw data
• steampipe query to work with the data using SQL in an interactive terminal interface
• steampipe query --output csv 'select * from organizations' to access data from the shell
• psql
• pgcli
• pgadmin4

paas-steampipe-dashboard's Issues

Issue Title

test from visual studio

check buildpack repositories releases

• https://hub.steampipe.io/plugins/turbot/github/tables/github_search_repository

fix link from dashboard to report and use internal link

https://steampipe.io/docs/reference/mod-resources/card#dynamic-data-and-static-internal-link

Convert buildpacks to use account snapshot

apps

User query reference to point to sql

Create input selectors

https://steampipe.io/docs/reference/mod-resources/input

• region select
• department select
• organisation select
• bill_month
• buildpack
• service offering

Services dashboard

• service_instances_count.sql
• service_instances.sql
• service_offerings_count.sql
• service_offerings.sql
• service_plans_count.sql
• service_plans.sql

aws plugin

test with paas-experiments

• ec2 instances
• rds instances

Add aiven support

Extract data from aiven using the CLI https://aiven.io/api for influxdb and open search

--json switch to emit json and wrangle
Json in the usual way into csv format

Add plug-in dependencies to mod.sp

https://steampipe.io/docs/reference/mod-resources/mod

Add in csv and config plugins as
Required

CC api versions

select
  url,
  method,
  response_status_code,
  jsonb_pretty(response_body::jsonb) as response_body
from
  net_http_request
where
  url = 'https://api.cloud.service.gov.uk/';

Then fish out.links.cloud_controller_v3.meta.version

Also from https://api.London.cloud.service.gov.uk/

Replace use of cf plug-in with csv snapshot from the api for domains

add routes

• list all the routes into a csv
• make report
• make linkable
• make searcheable

add default config for csv plugin

add a default config file for the csv plugin to the config directory

connection "csv" {
  plugin = "csv"
  paths = [ "*.csv"  ]
}

AWS infra

• VPC
• Load Balancers
• Cloudfront distribution
• IAM users

link departments to orgs in table

Add schema definitions

Schemata

orgs.csv

• org_name
• owner
• org_guid

services.csv

virtual_machines.csv

Automate data refresh

• automate regular updates
• Add a last published date to the dashboard

add cells from diego and isolation segments in regions

possibly using the config plugin to read json/yaml, the terrafrom plugin or the net plugin, failing that curl the repo and jq out the bits I need

calculate the advertised memory?

statuspage

• add rss plug-in
• card.statuspage_latest
• table.statuspage
• dashboard.statuspage

routes

Minimal preso

Preso for knowledgeshare

Fix working copy to make mobile changes

add department metadata

• department type, ie alb, ministerial department etc based on the GOV.UK taxonomy
• home url

Document data model

• Drawio in repo
• Script to extract SVG
• Makefile to install
• make commit
• first draft of model
  • orgs spaces and owners
  • add tables PKs and FKs

card of log drains

the net plugin http support looks broken

check out the cf plugin

https://github.com/SvenTo/steampipe-plugin-cf

fix controls and benchmark

• Count of suspended accounts = 0
• Count of active trial accounts = 0
• Run benchmark

Refactor makefile

Remove repetitious api access with a macro

services

net plugin for tls and dns

sources: dns, whois, tls, certificates, cf API,
plugins: net, whois

stacks

Add schema definitions

Document the data

Schemata

orgs.csv

• org_name
• owner
• org_guid

services.csv

virtual_machines.csv

Test .sql query at cli

Switch to steampipe v0.16.0

https://github.com/turbot/steampipe/releases/tag/v0.16.0

buildpacks

🚀Deploy dashboard to ECS Fargate with AWS Copilot

Use AWS copilot to drive deployment

backend dashboard service

• minimal dashboard and test data fed from a fake data source in csv format
• run using docker locally using the turbot/steampipe image
• configure Dockerfile with dependencies and data
• build container image from a fake data source in csv format
• run using docker locally using the turbot/steampipe image
• configure Dockerfile with dependencies and data
• build dashboard container image
• run dashboard container locally docker run --rm -ti -p 8080:8080 hello-steampipe
• configure AWS account
• Request VPC limit increase requested 6 oct so I can make multiple environments in copilot
• confirm vpc limit is raised
• copilot init to create a test environment with an app
• copilot deploy
• copilot app delete
• deploy service as back end service so that there is no route to the internet and its safe
• create additional staging environment
• create additional production environment
• run remote command copilot svc exec -c /bin/bash like cf ssh
• add debug tools to container apt-get install vim curl python3 python3-pip net-tools jq tree net-tools procps iputils-ping
• create environment variable in the manifest
• create a secret using copilot secret init
• add environment variables
• Move to separate directory
• enable ecs service connect
• redeploy and test ecs service connect

front end proxy service

• deploy stand alone nginx with a static index.html
• ping back end from front end using service discovery
• curl back end
• configure nginx reverse proxy to point to dashboard.dev.hello-steampipe.local:8080 for http
  Traffic
• Configure nginx proxy to handle websockets
• add nginx basic auth
• Register a domain govukpaasmigration.digital with route53
• enable TLS by specifying --domain siting copilot app init
• redeploy proxy to ecs and verify it works with basic auth
• deploy new app for actual dashboard
• restrict nginx access to vpn
• enable ecs service connect
• redeploy and test ecs service connect
• reverse the hack to rewrite the nginx config
• [] control ingress source

general

• write initial draft of documentation

Modularise the dashboard

Break apart monolithic dashboard into components grouped by type

• general - countdown, cf version
• departments and organisations - trial accounts
• users
• buildpacks
• apps and processes
• backing services and plans
• GitHub
• Infrastructure
• domains, routes

track releases of key things

move decomission date to config.csv

Pivotal tracker data

Pull data from pivotal tracker

Apps and processes

• apps
• processes
• log drains
• docker apps
• buildpack apps
• total ram
• [ ]

security groups

replace dependency on cf plugin for domains

Event countdown

Select '2023-12-31' - current_date

access tenant status using google sheet plugin

• configure gssheet plugin to point to the sheet
• update makefile to extract data
• chart readiness as donut

Add CI Dev and staging accounts

fix service_instances extract

clean up the extract