Discovering Steampipe with Scalingo and Airtable plugins

14 November 2021 - François

I recently discovered Steampipe which allows you to query a lot of remote APIs live with SQL. Beyond having a common language to use any API, it is possible to mix several data sources with joins, to make checks and much more.

To add available data sources, it is necessary to create/install plugins.

I have created two plugins, the first one for Scalingo and the second one for Airtable.

Scalingo

The Scalingo plugin allows you to query a large number of resources from the Scalingo API, such as applications, addons, collaborators, etc …

For example this query will display all hosted applications:

select
  name,
  region,
  url
from
  scalingo_app

Some use cases:

• We now have rules for Pix to check the consistency of our application configuration with pix-steampipe-mod-scalingo-compliance.

• Find out which application uses a certain environment variable.

    select
      a.name as app_name,
      e.name as env_name
    from
      scalingo_app as a
    join
      scalingo_environment as e
    on
      a.name = e.app_name
    where
      e.value = 'my variable value'
  

• When someone leaves, it is easy to list all the applications to which they had access (and then delete them manually).

    select
      a.name as app_name,
      c.email as email
    from
      scalingo_collaborator as c
    inner join
      scalingo_app as a
    on
      a.name = c.app_name
    where
      email = 'xxx'
  

Airtable

The Airtable plugin allows you to query your Airtable tables using SQL. In addition to allowing you to use Airtable formulas, it also allows you to make joins, which is very useful in the case of Airtable.

Some use cases:

• extract a list of HTML files referenced in Pix proofs that belong to a specific domain.

    select
      c.fields->>'Référence' as ref,
      a.fields->>'Nom' as nom,
      e.id,
      e.fields->>'Consigne' as consigne,
      e.fields->>'Statut' as statut,
      e.fields->>'Embed URL' as embed
    from
      epreuves e,
      jsonb_array_elements_text(e.fields->'Acquis') acquis,
      acquis a,
      jsonb_array_elements_text(a.fields->'Compétences') competence,
      competences c
    where
      acquis = a.id
      and competence = c.id
      and c.fields->Code = '3'
      and (e.fields->>'Consigne' LIKE '%.html%'
           or e.fields->>'Embed URL' is not null)
    order by
      c.fields->'Référence'
  

• find a duplicate id due to an error.

    select
      fields->'id',
      count(*) as count
    from
      xxx
    group by
      fields->'id' having count(*) > 1
  

• calculate a sum of a column by specifically selecting a subpart with a join.

    select
      sum((a.fields->'PixValue')::float)
    FROM
      acquis a,
      jsonb_array_elements_text(a.fields->'Compétence (via Tube)') as cid,
      competences c
    where
      c.fields->>'Sous-domaine' = '2.1'
      and cid = c.id
      and a.fields->>'Status' = 'actif'
  

An evolution of the plugin is to use the metadata API when it will be available. It will be possible to offer a real introspection of tables with columns of the right type.

Conclusion

In short, Steampipe is really nice to use. Very quickly you can create your first queries and perform operations that would be difficult to perform otherwise.

Have a comment? Contact me by email.