Export Cost Data #225
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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" |