Table of contents

This is for internal use by the PaaS team. Public-facing documentation is located at docs.cloud.service.gov.uk.

Getting data about trial accounts

This guide is for the technical work involved in managing trial and abandoned orgs. We are now enforcing a 3 month trial policy in order to reduce costs. We look for orgs which are older than 75 days because we want to notify them just prior to expiry.

How to get the data

  1. Log into Prod
  2. Run ./scripts/org-usage-report.sh > org_usage.csv

expect to see some errors containing

jq: error (at <stdin>:1): Cannot iterate over null (null)
jq: error (at <stdin>:1): Cannot iterate over null (null)

These are from orgs that are created or removed during the data dump i.e. CATS and SMOKE test orgs.

How to import the data

  1. docker run -d -p 5432:5432 --name postgres -e POSTGRES_PASSWORD= -v "$PWD:/var/downloads" postgres:9.5
  2. psql -Upostgres -hlocalhost
  3. create table orgdump (name text, guid text, created_at timestamptz, updated_at timestamptz, managers int, users int, running_apps int, stopped_apps int, services int, last_logon_time text, quota text, org_manager_emails text);
  4. COPY orgdump FROM '/var/downloads/org_usage.csv' DELIMITER ',' CSV HEADER;

Trial Orgs

How to query the data

To find orgs that are over 75 days old and are on the default quota:

SELECT * FROM orgdump
WHERE created_at < NOW() - INTERVAL '75 days'
AND quota like 'default'
ORDER BY created_at;

To export the data for contacting the org managers run the following

COPY (
  WITH expiring_trials AS (
    SELECT * FROM orgdump WHERE created_at < NOW() - INTERVAL '75 days' AND quota LIKE 'default' ORDER BY created_at
  ),

  filtered_empty_users AS (
    SELECT * FROM expiring_trials WHERE managers > 0
  ),

  emails_to_arrays AS (
    SELECT name, string_to_array(trim(org_manager_emails), ' ') AS user_array FROM filtered_empty_users
  ),

  unnested_emails AS (
    SELECT unnest(user_array) AS "email address", name AS organisation FROM emails_to_arrays
  )
    SELECT * FROM unnested_emails
) TO '/var/downloads/expiring_trial_orgs.csv' WITH HEADER CSV DELIMITER ',';

Our process is still being refined. This work should be carried out as part of a Pivotal story, so for now it should be fine to just attach the CSV output to a comment in the story.

To contact the org managers

Login to Notify. If you do not have login details speak to a senior member of the team.

  1. Click on Templates -> Convert trial period to paid org -> Send -> Upload list of email addresses -> Choose a file.
  2. Navigate to where you stored the file (/var/downloads/expiring_trial_orgs.csv).
  3. Click on ‘Send X emails’, where X should be the number of people to contact.

We should then mark the story as blocked until seven days have passed. Once this time has passed we can upgrade the quota of the orgs to the next highest non-trial quota.

Abandoned orgs

How to query the data

To find orgs with nothing running:

SELECT * FROM orgdump
WHERE users = 0 or (running_apps + stopped_apps) = 0
AND created_at < NOW() - INTERVAL '75 days'
ORDER BY created_at;

At this point the data should be checked with the PM team before proceeding

To export the data for contacting the org managers run the following

COPY (
  WITH unused_orgs AS (
    SELECT * FROM orgdump
    WHERE (users = 0 OR (running_apps + stopped_apps) = 0)
    AND created_at < NOW() - INTERVAL '75 days'
    ORDER BY created_at
  ),
  filtered_empty_users AS (
    SELECT * FROM unused_orgs WHERE managers > 0
  ),
  emails_to_arrays AS (
    SELECT name, string_to_array(trim(org_manager_emails), ' ') AS user_array FROM filtered_empty_users
  ),
  unnested_emails AS (
    SELECT unnest(user_array) AS "email address", name AS organisation FROM emails_to_arrays
  )
  SELECT * FROM unnested_emails
) TO '/var/downloads/abandoned_orgs.csv' WITH HEADER CSV DELIMITER ',';

You will then need to exit psql:

\q

To contact the org managers

Login to Notify. If you do not have login details speak to a senior member of the team.

  1. Click on Templates -> Unused organisation removal notice -> Send -> Upload list of email addresses -> Choose a file.
  2. Navigate to where you stored the file (/var/downloads/abandoned_orgs.csv).
  3. Click on 'Send X emails’, where X should be the number of people to contact.

We should then mark the story as blocked until seven days have passed. Once this time has passed we can delete the orgs and notify them of deletion. For notification of deletion, follow the steps above but using the template called 'Unused organisation removed’.

Tidy up

docker stop postgres && docker rm postgres
rm org_usage.csv