Steampipe & Correlate Azure App Services with Azure App Services Plans

steampipe

asdf plugin-add steampipe
asdf install steampipe latest
asdf global steampipe latest
steampipe plugin install azure
steampipe plugin install config

az functionapp list | tee /tmp/functionapps.json
az webapp list | tee /tmp/webapps.json

steampipe query 

steampipe-query.sql

CREATE TEMP TABLE app_services AS
  select
    jsonb_array_elements(functionapp.content) ->> 'appServicePlanId' as app_service_plan_id,
    jsonb_array_elements(functionapp.content) ->> 'name' as app_name,
    jsonb_array_elements(functionapp.content) ->> 'kind' as app_kind,
    jsonb_array_elements(functionapp.content) ->> 'location' as app_location,
    jsonb_array_elements(functionapp.content) ->> 'resourceGroup' as app_resource_group,
    jsonb_array_elements(functionapp.content) ->> 'state' as app_state
  from
    json_file as functionapp
  where
    path = '/tmp/functionapps.json';

INSERT INTO app_services
select
  jsonb_array_elements(webapp.content) ->> 'appServicePlanId' as app_service_plan_id,
  jsonb_array_elements(webapp.content) ->> 'name' as app_name,
  jsonb_array_elements(webapp.content) ->> 'kind' as app_kind,
  jsonb_array_elements(webapp.content) ->> 'location' as app_location,
  jsonb_array_elements(webapp.content) ->> 'resourceGroup' as app_resource_group,
  jsonb_array_elements(webapp.content) ->> 'state' as app_state
from
  json_file as webapp
where
  path = '/tmp/webapps.json';

select
  azure_app_service_plan.name as app_service_plan_name,
  azure_app_service_plan.kind as app_service_plan_kind,
  azure_app_service_plan.sku_name as app_service_plan_sku_name,
  azure_app_service_plan.sku_tier as app_service_plan_sku_tier,
  azure_app_service_plan.region as app_service_plan_location,
  azure_app_service_plan.resource_group as app_service_plan_resource_group,
  app_services.app_name,
  app_services.app_kind,
  app_services.app_location,
  app_services.app_resource_group,
  app_services.app_state
from
  azure_app_service_plan
full join app_services on azure_app_service_plan.id = app_services.app_service_plan_id;