export_cost_data.yml

name: Export Cost Data

on:
  schedule:
    # The workflow runs every day at 3PM
    - cron: "0 19 * * *"  #UTC-5

env:
  AZURE_CREDENTIALS: '{"clientId":"${{ secrets.AZURE_CLIENT_ID }}","clientSecret":"${{ secrets.AZURE_CLIENT_SECRET }}","subscriptionId":"${{ secrets.AZURE_SUBSCRIPTION_ID }}","tenantId":"${{ secrets.AZURE_TENANT_ID }}"}'

jobs:
  export_cost_data:
    name: Export Azure Cost Data to Storage
    strategy:
      fail-fast: false
      matrix:
        include:
          - rg: "prime-data-hub-staging"
            env: "staging"
            pwd_key: "POSTGRESQL_STAGING_PWD"
            db: "pdhstaging-pgsql"
          - rg: "prime-data-hub-prod"
            env: "prod"
            pwd_key: "POSTGRESQL_PROD_PWD"
            db: "pdhprod-pgsql"
    runs-on: ubuntu-latest
    steps:
      - name: "Check out changes"
        uses: actions/checkout@d632683dd7b4114ad314bca15554477dd762a938
      - name: Connect to VPN and login to Azure
        uses: ./.github/actions/vpn-azure
        with:
          env-name: ${{ matrix.env }}
          sp-creds: ${{ env.AZURE_CREDENTIALS }}
          tf-auth: false
      - name: Run Az Cost CLI
        id: az-cost
        uses: ./.github/actions/az-cost
        with:
          rg: ${{ matrix.rg }}
          days_ago: 180
          total_format: daily
      - name: Get Az Cost Result
        run: |
          echo "${{ steps.az-cost.outputs.result }}" > costs_raw.csv
          touch costs.csv
          sed 's/\r//' costs_raw.csv > costs_total.csv
          cat ./costs_total.csv
      - name: Run Az Cost CLI By Resource
        id: az-cost-by-resource
        uses: ./.github/actions/az-cost
        with:
          rg: ${{ matrix.rg }}
          days_ago: 180
          total_format: dailyByResource
      - name: Get Az Cost Result By Resource
        run: |
          echo "${{ steps.az-cost-by-resource.outputs.result }}" > costs_by_res_raw.csv
          touch costs_by_res.csv
          sed 's/\r//' costs_by_res_raw.csv > costs_by_res.csv
          cat ./costs_by_res.csv
      - name: Get runner ip
        id: runner_ip
        uses: ./.github/actions/runner-ip
      # Allow runner db access
      - name: Allow DB access
        id: add-db-access
        shell: bash
        run: |
          az postgres server update -g ${{ matrix.rg }} -n ${{ matrix.db }} --public-network-access "Enabled"
          sleep 10;
          az postgres server firewall-rule create -g ${{ matrix.rg }} -s ${{ matrix.db }} -n github_actions_runner \
            --start-ip-address ${{ steps.runner_ip.outputs.ip-address }} --end-ip-address ${{ steps.runner_ip.outputs.ip-address }}
      - name: Store cost data in DB
        uses: ./.github/actions/db-query
        with:
          pass: ${{ secrets[matrix.pwd_key] }}
          host: ${{ matrix.db }}
          port: 5432
          user: prime
          database: prime_data_hub
          output-file: result.txt
          query: |
            CREATE TABLE IF NOT EXISTS Azure_Costs (
              Date       date,
              Cost       NUMERIC(13, 8),
              CostUsd    NUMERIC(13, 8),
              Currency   varchar(3)
            );
            TRUNCATE Azure_Costs;
            \copy Azure_Costs (Date, Cost, CostUsd, Currency) FROM './costs_total.csv' WITH DELIMITER ',' CSV HEADER;

            CREATE TABLE IF NOT EXISTS Azure_Resource_Costs (
              Date       date,
              Name       varchar(128),
              Cost       NUMERIC(13, 8),
              CostUsd    NUMERIC(13, 8),
              Currency   varchar(3),
              Tags       varchar(256)
            );
            TRUNCATE Azure_Resource_Costs;
            \copy Azure_Resource_Costs (Date, Name, Cost, CostUsd, Currency, Tags) FROM './costs_by_res.csv' WITH DELIMITER ',' CSV HEADER;

      # Remove runner db access
      - name: Remove DB access
        if: always() && steps.add-db-access.outcome == 'success'
        shell: bash
        run: |
          az postgres server firewall-rule delete -g ${{ matrix.rg }} -s ${{ matrix.db }}  -n github_actions_runner --yes
          sleep 10;
          az postgres server update -g ${{ matrix.rg }} -n ${{ matrix.db }} --public-network-access "Disabled"