# FinOps SQL Queries (BigQuery)

This directory contains SQL queries used for FinOps analysis in BigQuery, organized by cloud provider.

## Directory Structure

### 1. `cloud_alibaba` (Alibaba Cloud)
Queries for analyzing Alibaba Cloud billing data (e.g., `gcp-digital-bi.alibaba_cloud.monthly_*`).

*   **`_DataDict_Alibaba.sql`**: Reference query to view table schema.
*   **`cost_byaccount.sql`**: Monthly cost aggregation by account.
*   **`cost_byproduct.sql`**: Cost broken down by product, region, and account.
*   **`cost_bytenant.sql`**: Cost allocation by `tenant` tag (parsed from `instance_tags`).
*   **`k8s_aks_cost.sql`**: Cost analysis for Container Service (ACK) clusters.
*   **`cost_prod_*.sql`**: Queries targeted at specific production environments (Control Plane, Data Plane) and tenant allocation.
*   **`cost_tag_sample.sql`**: Sample queries for extracting and analyzing tag data.

### 2. `cloud_aws` (Amazon Web Services)
Queries for AWS Cost and Usage Reports (CUR).

#### `CUR2.0` (Core Queries)
Active queries for cost analysis, typically targeting `gcp-digital-bi.aws_billing_no_payer` or `aws_billing`.

*   **General & Account Analysis**:
    *   `aws_accountid.sql` / `cost_byaccount.sql`: Total cost grouped by usage account.
    *   `account_all_daybyday.sql`: Daily cost trends and ranking per account over 30 days.
*   **Product & Service Analysis**:
    *   `cost_by_product_servicecode.sql`: Cost breakdown by `product_servicecode` and `product_family`.
    *   `ec2_*.sql`: Detailed analysis for EC2 resources, specifically focusing on storage (`gp2`) and instance types (`m5`), separated by Prod/NonProd accounts.
    *   `eks_*.sql`: EKS cost analysis, including cluster listing, extended support, and daily costs.
    *   `rds_cost.sql`: RDS cost reporting.
*   **Tagging & Cost Allocation**:
    *   `cost_bytag.sql` / `cost_byservicetype.sql`: Cost distribution grouped by `user_servicetype`.
    *   `cost_bytenant.sql`: Cost distribution grouped by `user_tenant`.
    *   `cost_byusedby.sql`: Cost distribution grouped by `user_usedby`.
    *   `cost_nontag.sql` / `cost_byservicetype_none.sql`: Identification of resources missing standard tags.

#### `backup`
Contains historical, ad-hoc, or case-specific queries organized by issue ID (e.g., `0414`, `3567`) or function. Includes scripts for:
*   Specific resource investigations (EBS, ELB, NatGateway, ECR).
*   Data mirroring scripts (`project_clone`).
*   Pivot table generation for tenant costs (`tenant_id`).

### 3. `cloud_azure` (Microsoft Azure)
Queries for Azure billing data (`gcp-digital-bi.azure_billing`).

*   **`_DataDict_Azure.sql`**: Reference query to view table schema.
*   **`k8s_aks_cost.sql`**: Cost analysis for Azure Kubernetes Service (AKS), identifying clusters via Resource IDs or tags.

### 4. `cloud_gcp` (Google Cloud Platform)
Queries for GCP billing export data (`gcp-digital-bi.gcp_billing_v2`).

*   **`_DataDict_GCP.sql`**: Reference query to view table schema.
*   **`Overall.sql`**: High-level monthly cost trends and estimates by project.
*   **`cost_byproject.sql`**: Total cost aggregated by project ID.
*   **`Detail.sql`**: Granular cost analysis by service, SKU, and location.
*   **`k8s_gke_cost.sql`**: Cost analysis for GKE clusters, filtering by service and SKU.

### 5. `cloud_prod`
Contains miscellaneous or placeholder queries for production environment analysis.