Master Advanced PolicyCortex Queries for Cloud Governance Insights

query ComplexResourceAnalysis(
  $accountIds: [String!]
  $regions: [String!]
  $timeRange: TimeRange!
  $riskThreshold: Float
) {
  # Get resources with complex filtering
  resources(
    filter: {
      accountId: { in: $accountIds }
      region: { in: $regions }
      and: [
        { tags: { key: "Environment", value: { in: ["production", "staging"] } } }
        { securityScore: { lt: $riskThreshold } }
        { or: [
          { publiclyAccessible: true }
          { hasViolations: { severity: ["HIGH", "CRITICAL"] } }
        ]}
      ]
    }
    orderBy: [{ securityScore: ASC }, { lastModified: DESC }]
    pagination: { first: 50 }
  ) {
    edges {
      node {
        id
        name
        type
        accountId
        region

        # Get security and compliance context
        securityScore
        complianceScore
        publiclyAccessible

        # Related violations with details
        violations(
          filter: {
            status: OPEN
            detectedAt: { gte: $timeRange.start }
          }
        ) {
          totalCount
          edges {
            node {
              policy { name, framework, severity }
              detectedAt
              message
              estimatedImpact {
                riskScore
                businessImpact
                remediationCost
              }
            }
          }
        }

        # Cost and usage metrics
        costMetrics(timeRange: $timeRange) {
          totalCost
          dailyAverage
          trend
          optimizationPotential
        }

        # Compliance framework mappings
        complianceStatus {
          framework
          overallScore
          failedControls {
            controlId
            reason
            severity
          }
        }

        # Resource relationships
        dependencies {
          type
          resource {
            id
            name
            type
          }
          relationship
        }
      }
    }

    # Aggregated analytics
    analytics {
      totalResources
      averageSecurityScore
      violationsByFramework {
        framework
        count
        severity
      }
      costByRegion {
        region
        totalCost
        violationCount
      }
    }
  }
}

query ComplianceTrendAnalysis(
  $frameworks: [String!]!
  $timeRange: TimeRange!
  $granularity: TimeGranularity = DAILY
) {
  complianceAnalytics(
    frameworks: $frameworks
    timeRange: $timeRange
  ) {
    # Historical trend data
    trends(granularity: $granularity) {
      date
      scores {
        framework
        score
        change
        violationCount
      }
    }

    # Framework comparison
    frameworkComparison {
      framework
      currentScore
      previousScore
      trend

      # Control-level analysis
      controlAnalysis {
        controlId
        controlName
        currentStatus
        historicalPerformance {
          date
          status
          score
        }

        # Associated violations
        recentViolations(limit: 10) {
          policyName
          resourceCount
          severity
          trend
        }
      }

      # Risk areas identification
      riskAreas {
        category
        riskLevel
        affectedResources
        potentialImpact
        recommendedActions
      }
    }

    # Cross-framework insights
    insights {
      # Common failure patterns
      commonFailurePatterns {
        pattern
        affectedFrameworks
        frequency
        impactScore
      }

      # Improvement opportunities
      improvementOpportunities {
        area
        potentialScoreGain
        estimatedEffort
        affectedControls
      }

      # Regulatory gaps
      regulatoryGaps {
        missingControls
        frameworkGaps
        riskExposure
      }
    }
  }
}

-- Identify violation patterns and trends
WITH violation_patterns AS (
  SELECT
    v.policy_name,
    v.resource_type,
    v.account_id,
    v.region,
    COUNT(*) as violation_count,
    AVG(EXTRACT(EPOCH FROM (v.resolved_at - v.detected_at))/3600) as avg_resolution_hours,
    COUNT(CASE WHEN v.severity = 'CRITICAL' THEN 1 END) as critical_count,
    COUNT(CASE WHEN v.severity = 'HIGH' THEN 1 END) as high_count,
    COUNT(CASE WHEN v.auto_remediated = true THEN 1 END) as auto_remediated_count
  FROM violations v
  WHERE v.detected_at >= NOW() - INTERVAL '90 days'
  GROUP BY v.policy_name, v.resource_type, v.account_id, v.region
),

resource_risk_scores AS (
  SELECT
    r.id as resource_id,
    r.name,
    r.type,
    r.account_id,
    COUNT(v.id) as total_violations,
    SUM(CASE
      WHEN v.severity = 'CRITICAL' THEN 10
      WHEN v.severity = 'HIGH' THEN 5
      WHEN v.severity = 'MEDIUM' THEN 2
      ELSE 1
    END) as risk_score,
    STRING_AGG(DISTINCT cf.framework, ', ') as affected_frameworks
  FROM resources r
  LEFT JOIN violations v ON r.id = v.resource_id
    AND v.detected_at >= NOW() - INTERVAL '30 days'
  LEFT JOIN compliance_frameworks cf ON v.policy_id = cf.policy_id
  GROUP BY r.id, r.name, r.type, r.account_id
)

-- Main analysis query
SELECT
  vp.policy_name,
  vp.resource_type,
  vp.account_id,
  vp.region,
  vp.violation_count,
  vp.avg_resolution_hours,
  vp.critical_count,
  vp.high_count,
  vp.auto_remediated_count,

  -- Calculate remediation efficiency
  ROUND((vp.auto_remediated_count * 100.0 / vp.violation_count), 2) as auto_remediation_rate,

  -- Risk assessment
  CASE
    WHEN vp.critical_count > 5 THEN 'HIGH_RISK'
    WHEN vp.high_count > 10 THEN 'MEDIUM_RISK'
    ELSE 'LOW_RISK'
  END as risk_level,

  -- Get top affected resources
  (
    SELECT STRING_AGG(rrs.name, ', ' ORDER BY rrs.risk_score DESC)
    FROM resource_risk_scores rrs
    WHERE rrs.account_id = vp.account_id
    AND rrs.type = vp.resource_type
    LIMIT 5
  ) as top_affected_resources

FROM violation_patterns vp
WHERE vp.violation_count >= 3  -- Focus on recurring issues
ORDER BY vp.critical_count DESC, vp.violation_count DESC
LIMIT 50;

-- Calculate ROI of governance policies
WITH cost_analysis AS (
  SELECT
    v.policy_name,
    v.account_id,
    COUNT(*) as violation_count,

    -- Estimate cost impact of violations
    SUM(
      CASE v.severity
        WHEN 'CRITICAL' THEN 10000  -- Estimated impact of critical violation
        WHEN 'HIGH' THEN 5000
        WHEN 'MEDIUM' THEN 1000
        ELSE 200
      END
    ) as estimated_violation_cost,

    -- Calculate prevention savings (auto-remediated violations)
    SUM(
      CASE
        WHEN v.auto_remediated = true THEN
          CASE v.severity
            WHEN 'CRITICAL' THEN 8000  -- Prevented cost
            WHEN 'HIGH' THEN 4000
            WHEN 'MEDIUM' THEN 800
            ELSE 100
          END
        ELSE 0
      END
    ) as prevention_savings,

    -- Time to resolution metrics
    AVG(EXTRACT(EPOCH FROM (COALESCE(v.resolved_at, NOW()) - v.detected_at))/3600) as avg_resolution_hours,

    -- Manual effort estimation
    SUM(
      CASE
        WHEN v.auto_remediated = true THEN 0
        ELSE
          CASE v.severity
            WHEN 'CRITICAL' THEN 8  -- Hours of manual work
            WHEN 'HIGH' THEN 4
            WHEN 'MEDIUM' THEN 2
            ELSE 1
          END
      END
    ) as manual_effort_hours

  FROM violations v
  WHERE v.detected_at >= NOW() - INTERVAL '180 days'
  GROUP BY v.policy_name, v.account_id
)

SELECT
  ca.policy_name,
  ca.account_id,
  ca.violation_count,

  -- Cost metrics
  ROUND(ca.estimated_violation_cost, 2) as total_violation_cost,
  ROUND(ca.prevention_savings, 2) as automation_savings,
  ROUND((ca.prevention_savings * 100.0 / NULLIF(ca.estimated_violation_cost, 0)), 2) as savings_percentage,

  -- Efficiency metrics
  ROUND(ca.avg_resolution_hours, 2) as avg_resolution_hours,
  ca.manual_effort_hours as total_manual_hours,
  ROUND(ca.manual_effort_hours * 150, 2) as manual_cost_estimate,  -- $150/hour

  -- ROI calculation
  ROUND((ca.prevention_savings - (ca.manual_effort_hours * 150)), 2) as net_savings,

  -- Policy effectiveness score
  CASE
    WHEN ca.prevention_savings > ca.estimated_violation_cost * 0.8 THEN 'HIGHLY_EFFECTIVE'
    WHEN ca.prevention_savings > ca.estimated_violation_cost * 0.5 THEN 'EFFECTIVE'
    WHEN ca.prevention_savings > ca.estimated_violation_cost * 0.2 THEN 'MODERATELY_EFFECTIVE'
    ELSE 'NEEDS_IMPROVEMENT'
  END as effectiveness_rating

FROM cost_analysis ca
ORDER BY ca.prevention_savings DESC, ca.violation_count DESC;