Skip to main content
L1

Director - SAM Questions

← Back to Index

Overview​

The Director of Software Asset Management needs operational visibility into license management, vendor relationships, compliance monitoring, and optimization opportunities. Questions focus on spend management, license pool availability, utilization tracking, vendor coordination, and compliance enforcement.

Primary Components: SAM Dashboard, License Management, Vendor Portal, Compliance Monitor

Questions​

Q1: "What is the software spend that I am managing?"​

Why it matters: SAM directors need comprehensive visibility into software spend to manage budgets, track vendor commitments, forecast renewal costs, and identify cost optimization opportunities across the entire software portfolio.

Telemetry Sources​

  • Procurement System: Purchase orders, invoices, contract values, payment schedules
  • Finance/ERP: Actual spend data, budget allocations, department chargebacks
  • Vendor Portals: Subscription billing, usage-based charges, overage fees
  • Contract Repository: License agreements, pricing schedules, commitment terms

L1 Analytics (Bronze→Silver)​

-- Normalize spend data from procurement and finance
CREATE VIEW silver_software_spend AS
SELECT
    vendor_id,
    vendor_name,
    product_name,
    contract_id,
    purchase_date,
    invoice_date,
    amount,
    currency,
    spend_category, -- license, maintenance, support, overage
    payment_status,
    department_id,
    cost_center,
    fiscal_year,
    fiscal_quarter
FROM bronze_procurement_transactions
WHERE category = 'Software'
UNION ALL
SELECT
    vendor_id,
    vendor_name,
    product_name,
    subscription_id as contract_id,
    billing_period_start as purchase_date,
    billing_date as invoice_date,
    charge_amount as amount,
    currency,
    charge_type as spend_category,
    payment_status,
    department_id,
    cost_center,
    fiscal_year,
    fiscal_quarter
FROM bronze_vendor_billing;

L2 Analytics (Silver→Gold)​

-- Aggregate spend by vendor, product, department
CREATE VIEW gold_spend_summary AS
SELECT
    vendor_name,
    product_name,
    fiscal_year,
    fiscal_quarter,
    spend_category,
    department_id,
    SUM(amount) as total_spend,
    COUNT(DISTINCT contract_id) as contract_count,
    MIN(invoice_date) as first_spend_date,
    MAX(invoice_date) as last_spend_date,
    SUM(CASE WHEN payment_status = 'paid' THEN amount ELSE 0 END) as paid_amount,
    SUM(CASE WHEN payment_status = 'pending' THEN amount ELSE 0 END) as pending_amount
FROM silver_software_spend
GROUP BY vendor_name, product_name, fiscal_year, fiscal_quarter, spend_category, department_id;

-- Calculate YoY spend trends
CREATE VIEW gold_spend_trends AS
SELECT
    vendor_name,
    product_name,
    current_year.total_spend as current_spend,
    prior_year.total_spend as prior_spend,
    (current_year.total_spend - prior_year.total_spend) / prior_year.total_spend * 100 as yoy_growth_pct,
    current_year.contract_count
FROM gold_spend_summary current_year
LEFT JOIN gold_spend_summary prior_year
    ON current_year.vendor_name = prior_year.vendor_name
    AND current_year.product_name = prior_year.product_name
    AND current_year.fiscal_year = prior_year.fiscal_year + 1;

Cerebro Enhancement​

  • Memory: Historical spend patterns, vendor pricing trends, renewal history, negotiated discount rates, commitment milestones
  • Intelligence: Spend anomaly detection (unexpected charges, billing errors), category spend distribution, vendor concentration risk, budget variance analysis
  • Reasoning: Cost optimization recommendations (consolidation opportunities, volume discount eligibility), spend forecast for upcoming renewals, variance explanations (new users, feature upgrades, overages)

UI Representation​

Component: SAM Dashboard - Spend Overview Module

Visualization:

┌─────────────────────────────────────────────────────────────┐
│ Software Spend Overview                    FY2026 Q1        │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Total Managed Spend: $12.4M                                │
│  ▲ 8.3% vs Prior Year                                       │
│                                                              │
│  Top Vendors by Spend:                                      │
│  ┌──────────────────────────────────────────────┐          │
│  │ Microsoft      ████████████████  $3.2M  26%  │          │
│  │ Salesforce     ██████████        $2.1M  17%  │          │
│  │ Adobe          ███████           $1.5M  12%  │          │
│  │ ServiceNow     ██████            $1.3M  10%  │          │
│  │ Others         ███████████████   $4.3M  35%  │          │
│  └──────────────────────────────────────────────┘          │
│                                                              │
│  Spend by Category:                                         │
│  License: $8.1M (65%) │ Maintenance: $2.8M (23%)           │
│  Support: $1.1M (9%)  │ Overages: $400K (3%)               │
│                                                              │
│  [View by Department] [View by Product] [Export Report]    │
└─────────────────────────────────────────────────────────────┘

Interactions:

  • Click vendor name to see detailed spend breakdown and contract details
  • Filter by time period (monthly, quarterly, annual), department, or spend category
  • Drill into specific products to see license count, per-license cost, utilization
  • Export spend reports for budget reviews and finance reconciliation

Copilot Query Example:

"What's our total software spend with Microsoft this year and how does it compare to last year?" Answer: "Your Microsoft spend for FY2026 is currently $3.2M (through Q1), tracking toward $12.8M annually. This represents a 12% increase vs FY2025 ($11.4M total). The increase is driven by: (1) Azure consumption up 18% ($420K), (2) 50 additional M365 E5 licenses ($85K), and (3) new Copilot for M365 deployment ($250K). You're on track with your budgeted Microsoft spend of $13.1M."

Q2: "When are contracts expiring?"​

Why it matters: Proactive contract renewal management prevents service disruptions, enables negotiation leverage, allows for vendor evaluation, and provides time to explore alternatives or consolidate licenses before auto-renewal commitments.

Telemetry Sources​

  • Contract Management System: Contract start/end dates, renewal terms, auto-renewal clauses, notice periods
  • Vendor Portals: Subscription expiration dates, renewal notifications
  • Procurement System: Purchase order terms, contract amendments
  • Calendar Systems: Renewal reminders, negotiation milestones

L1 Analytics (Bronze→Silver)​

-- Normalize contract expiration data
CREATE VIEW silver_contract_expirations AS
SELECT
    contract_id,
    vendor_name,
    product_name,
    contract_start_date,
    contract_end_date,
    renewal_term, -- annual, multi-year
    auto_renewal_flag,
    notice_period_days,
    annual_contract_value,
    license_count,
    contract_owner,
    business_owner,
    DATEDIFF(contract_end_date, CURRENT_DATE) as days_until_expiration
FROM bronze_contracts
WHERE contract_type = 'Software License'
    AND contract_status = 'Active';

L2 Analytics (Silver→Gold)​

-- Categorize contracts by renewal urgency
CREATE VIEW gold_renewal_pipeline AS
SELECT
    contract_id,
    vendor_name,
    product_name,
    contract_end_date,
    days_until_expiration,
    annual_contract_value,
    CASE
        WHEN days_until_expiration <= 30 THEN 'Critical - &lt;30 days'
        WHEN days_until_expiration <= 60 THEN 'Urgent - 30-60 days'
        WHEN days_until_expiration <= 90 THEN 'Upcoming - 60-90 days'
        WHEN days_until_expiration <= 180 THEN 'Planning - 90-180 days'
        ELSE 'Future - >180 days'
    END as renewal_urgency,
    auto_renewal_flag,
    notice_period_days,
    CASE
        WHEN days_until_expiration <= notice_period_days THEN 'Action Required'
        ELSE 'Monitor'
    END as renewal_status
FROM silver_contract_expirations
WHERE days_until_expiration <= 365
ORDER BY days_until_expiration;

Cerebro Enhancement​

  • Memory: Historical renewal timelines, vendor negotiation cycles, past renewal terms, decision-making patterns, stakeholder involvement history
  • Intelligence: Renewal workload forecasting, negotiation timeline recommendations, vendor relationship status, alternative vendor options, historical pricing trends
  • Reasoning: Optimal negotiation timing, consolidation opportunities (multiple expiring contracts with same vendor), risk alerts (approaching notice deadlines), stakeholder coordination recommendations

UI Representation​

Component: License Management - Renewal Calendar

Visualization:

┌─────────────────────────────────────────────────────────────┐
│ Contract Renewal Pipeline              Updated: Jan 15      │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Critical (&lt;30 days)                              $1.2M     │
│  ┌────────────────────────────────────────────────┐        │
│  │ ⚠️ Zoom                  Jan 28  $450K  [Act]  │        │
│  │ ⚠️ Atlassian             Feb 5   $380K  [Act]  │        │
│  │ ⚠️ DocuSign              Feb 12  $370K  [Act]  │        │
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  Urgent (30-60 days)                              $2.8M     │
│  ┌────────────────────────────────────────────────┐        │
│  │ Adobe Creative Cloud   Mar 15  $1.5M  [Plan]  │        │
│  │ Slack Enterprise       Mar 22  $820K   [Plan]  │        │
│  │ GitHub Enterprise      Mar 30  $480K   [Plan]  │        │
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  Upcoming (60-90 days)                            $1.9M     │
│  │ ServiceNow             Apr 18  $1.3M           │        │
│  │ Miro                   Apr 25  $340K           │        │
│  │ Figma                  May 2   $260K           │        │
│                                                              │
│  [Calendar View] [By Vendor] [By Value] [Export]           │
└─────────────────────────────────────────────────────────────┘

Interactions:

  • Click contract to see full details (terms, license count, utilization, vendor contacts)
  • Toggle between list view, calendar view, and timeline view
  • Set custom reminder notifications for renewal milestones
  • Initiate renewal workflow (vendor evaluation, stakeholder review, negotiation)
  • Flag contracts for evaluation or alternative vendor research

Copilot Query Example:

"Show me all contracts expiring in Q2 and highlight any with auto-renewal clauses" Answer: "You have 14 contracts expiring in Q2 (Apr-Jun) totaling $6.7M. 9 of these have auto-renewal clauses: Salesforce (Apr 1, $2.1M, 60-day notice), Microsoft EA (May 31, $3.2M, 90-day notice - notice period expires Mar 2!), Workday (Jun 15, $890K, 30-day notice), and 6 others. Priority action: Microsoft EA notice deadline is in 46 days - schedule vendor review meeting ASAP."

Q3: "How many licenses do we have left to assign?"​

Why it matters: SAM directors need real-time visibility into license pool availability to fulfill new user requests, forecast when additional purchases are needed, prevent over-purchasing, and maintain optimal inventory levels.

Telemetry Sources​

  • ITAM/SAM Platform: License entitlements, assigned licenses, license pools
  • Vendor Portals: Purchased license counts, subscription tiers
  • IDP (Okta/Azure AD): Active user assignments, app provisioning status
  • Procurement System: License purchase history, pending orders

L1 Analytics (Bronze→Silver)​

-- Normalize license entitlement and assignment data
CREATE VIEW silver_license_inventory AS
SELECT
    product_id,
    product_name,
    vendor_name,
    license_type, -- named user, concurrent, device, subscription tier
    total_purchased,
    total_assigned,
    total_active, -- actively used in last 30 days
    total_inactive, -- assigned but not used
    contract_id,
    last_updated
FROM bronze_license_entitlements;

L2 Analytics (Silver→Gold)​

-- Calculate available license capacity
CREATE VIEW gold_license_availability AS
SELECT
    product_name,
    vendor_name,
    license_type,
    total_purchased,
    total_assigned,
    (total_purchased - total_assigned) as available_licenses,
    ROUND((total_purchased - total_assigned) / NULLIF(total_purchased, 0) * 100, 1) as available_pct,
    total_active,
    total_inactive,
    ROUND(total_active / NULLIF(total_assigned, 0) * 100, 1) as utilization_pct,
    CASE
        WHEN (total_purchased - total_assigned) = 0 THEN 'At Capacity'
        WHEN (total_purchased - total_assigned) < 10 THEN 'Low Availability'
        WHEN (total_purchased - total_assigned) / NULLIF(total_purchased, 0) < 0.1 THEN 'Order Soon'
        ELSE 'Available'
    END as availability_status,
    contract_id
FROM silver_license_inventory;

-- Forecast license depletion
CREATE VIEW gold_license_forecast AS
SELECT
    la.product_name,
    la.available_licenses,
    ar.avg_monthly_assignments,
    CASE
        WHEN ar.avg_monthly_assignments > 0
        THEN la.available_licenses / ar.avg_monthly_assignments
        ELSE NULL
    END as months_until_depletion,
    ce.contract_end_date
FROM gold_license_availability la
LEFT JOIN (
    SELECT product_id, AVG(monthly_assignments) as avg_monthly_assignments
    FROM bronze_assignment_history
    WHERE assignment_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
    GROUP BY product_id
) ar ON la.product_id = ar.product_id
LEFT JOIN silver_contract_expirations ce ON la.contract_id = ce.contract_id;

Cerebro Enhancement​

  • Memory: Historical assignment patterns, seasonal demand fluctuations, department growth trends, onboarding velocity, past stockout incidents
  • Intelligence: License demand forecasting, optimal buffer levels, reclamation opportunity identification, assignment pattern anomalies
  • Reasoning: Purchase timing recommendations, license pool rebalancing suggestions (move licenses between departments), proactive reclamation triggers when capacity is low

UI Representation​

Component: SAM Dashboard - License Pool Status

Visualization:

┌─────────────────────────────────────────────────────────────┐
│ License Pool Availability              Updated: 2hrs ago    │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  ⚠️ Critical Capacity (Immediate Action)                    │
│  ┌────────────────────────────────────────────────┐        │
│  │ Figma Professional     0 / 150  [🔴 0%]  Order │        │
│  │ Adobe Acrobat Pro      2 / 85   [🟡 2%]  Order │        │
│  │ Slack Enterprise       8 / 320  [🟡 3%]  Order │        │
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  📊 Healthy Capacity                                        │
│  ┌────────────────────────────────────────────────┐        │
│  │ M365 E5               142 / 850 [🟢 17%]        │        │
│  │ Salesforce            38 / 240  [🟢 16%]        │        │
│  │ GitHub Enterprise     55 / 180  [🟢 31%]        │        │
│  │ Zoom                  89 / 500  [🟢 18%]        │        │
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  Forecast: Figma - depleted now, Slack - 3 weeks          │
│                                                              │
│  [Request Purchase] [Reclaim Licenses] [View All]          │
└─────────────────────────────────────────────────────────────┘

Interactions:

  • Click product to see assigned users, inactive licenses, and reclamation candidates
  • Initiate purchase request workflow for products at/near capacity
  • Trigger reclamation workflow to free up licenses from inactive users
  • Set custom thresholds for low-capacity alerts
  • View historical assignment trends and forecast depletion dates

Copilot Query Example:

"How many Figma licenses can I assign today and when will we need to buy more?" Answer: "You have 0 Figma Professional licenses available - you're at full capacity (150/150 assigned). However, 12 users haven't logged in for 60+ days and are candidates for reclamation. If you reclaim those, you'll have 12 licenses available immediately. Based on your average assignment rate (8 licenses/month), you'll need to purchase additional licenses within 6 weeks. Recommend ordering 25-50 more licenses now to avoid future stockouts."

Q4: "What's the utilization of our licenses?"​

Why it matters: License utilization metrics identify waste (unused or underutilized licenses), support cost optimization decisions, provide data for vendor negotiations, and help rightsize license purchases to actual usage patterns.

Telemetry Sources​

  • UEM (Jamf/Intune): Application launch frequency, last used date, usage duration
  • Vendor APIs: Login activity, feature usage, active vs inactive users
  • IDP (Okta/Azure AD): Authentication events, app access patterns
  • SIEM/Log Aggregation: Application access logs, session data

L1 Analytics (Bronze→Silver)​

-- Normalize usage data from multiple sources
CREATE VIEW silver_license_usage AS
SELECT
    user_id,
    product_id,
    product_name,
    license_assigned_date,
    last_login_date,
    last_30d_logins,
    last_90d_logins,
    last_used_date,
    total_usage_hours,
    feature_adoption_score,
    data_source -- UEM, Vendor API, IDP
FROM bronze_application_usage
WHERE license_assigned = TRUE;

L2 Analytics (Silver→Gold)​

-- Calculate utilization metrics
CREATE VIEW gold_license_utilization AS
SELECT
    product_name,
    COUNT(DISTINCT user_id) as total_assigned,
    COUNT(DISTINCT CASE WHEN last_30d_logins > 0 THEN user_id END) as active_30d,
    COUNT(DISTINCT CASE WHEN last_90d_logins > 0 THEN user_id END) as active_90d,
    COUNT(DISTINCT CASE WHEN last_30d_logins = 0 AND last_90d_logins > 0 THEN user_id END) as inactive_30d_active_90d,
    COUNT(DISTINCT CASE WHEN last_90d_logins = 0 THEN user_id END) as inactive_90d,
    ROUND(COUNT(DISTINCT CASE WHEN last_30d_logins > 0 THEN user_id END) /
          NULLIF(COUNT(DISTINCT user_id), 0) * 100, 1) as utilization_30d_pct,
    ROUND(COUNT(DISTINCT CASE WHEN last_90d_logins > 0 THEN user_id END) /
          NULLIF(COUNT(DISTINCT user_id), 0) * 100, 1) as utilization_90d_pct,
    AVG(total_usage_hours) as avg_usage_hours,
    AVG(feature_adoption_score) as avg_feature_adoption
FROM silver_license_usage
GROUP BY product_name;

-- Identify reclamation candidates
CREATE VIEW gold_reclamation_candidates AS
SELECT
    user_id,
    user_name,
    user_email,
    department,
    product_name,
    license_assigned_date,
    last_login_date,
    last_30d_logins,
    last_90d_logins,
    DATEDIFF(CURRENT_DATE, last_login_date) as days_since_last_use,
    CASE
        WHEN last_90d_logins = 0 THEN 'High Priority'
        WHEN last_30d_logins = 0 AND last_90d_logins < 5 THEN 'Medium Priority'
        WHEN last_30d_logins < 3 THEN 'Low Priority'
    END as reclamation_priority
FROM silver_license_usage
WHERE last_90d_logins < 5
ORDER BY days_since_last_use DESC;

Cerebro Enhancement​

  • Memory: Historical utilization trends, seasonal usage patterns, user role-based usage norms, past reclamation outcomes
  • Intelligence: Anomaly detection (sudden usage drops), cohort analysis (usage by department/role), adoption curve tracking, feature utilization patterns
  • Reasoning: Reclamation impact assessment, optimal reclamation timing, user notification strategies, license reallocation recommendations

UI Representation​

Component: SAM Dashboard - Utilization Analytics

Visualization:

┌─────────────────────────────────────────────────────────────┐
│ License Utilization (30-Day)           Updated: Today       │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  High Utilization (>80%)                                    │
│  ┌────────────────────────────────────────────────┐        │
│  │ Slack            ████████████████  320/340  94% │        │
│  │ GitHub           ██████████████    162/180  90% │        │
│  │ Zoom             ████████████      447/500  89% │        │
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  ⚠️ Low Utilization (&lt;50%) - Optimization Opportunity      │
│  ┌────────────────────────────────────────────────┐        │
│  │ Adobe Acrobat    ████              32/85   38%  │  Recl  │
│  │ Figma            █████             68/150  45%  │  Recl  │
│  │ Tableau          ███               45/120  38%  │  Recl  │
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  Reclamation Opportunity: 142 licenses ($186K/year)        │
│                                                              │
│  [View Candidates] [Start Reclamation] [Usage Trends]      │
└─────────────────────────────────────────────────────────────┘

Interactions:

  • Click product to see user-level utilization details and reclamation candidates
  • Filter by utilization threshold, department, or time period
  • Initiate reclamation workflow with automated user notifications
  • View usage trends over time to identify seasonal patterns
  • Export utilization reports for vendor negotiations

Copilot Query Example:

"Show me Adobe products with less than 50% utilization and tell me how much we could save by reclaiming unused licenses" Answer: "3 Adobe products have <50% utilization: Acrobat Pro (38%, 53 inactive users), Creative Cloud (46%, 82 inactive users), Adobe Sign (41%, 35 inactive users). Total reclamation opportunity: 170 licenses worth $238K annually. High-priority candidates (90+ days inactive): 94 licenses ($132K). Recommend starting with 90-day inactive users first, which could save $132K with minimal business disruption."

Q5: "Who is our vendor account manager / contact?"​

Why it matters: Quick access to vendor contacts enables efficient issue resolution, facilitates renewal negotiations, supports escalation workflows, and maintains vendor relationships critical for contract management and support needs.

Telemetry Sources​

  • Contract Management System: Vendor contact information, account manager assignments
  • CRM (Salesforce): Vendor relationship history, communication logs, meeting notes
  • Email/Communication Systems: Contact details, recent interactions
  • Procurement System: Vendor records, purchasing contacts

L1 Analytics (Bronze→Silver)​

-- Normalize vendor contact information
CREATE VIEW silver_vendor_contacts AS
SELECT
    vendor_id,
    vendor_name,
    contact_type, -- account manager, technical support, sales, executive sponsor
    contact_name,
    contact_title,
    contact_email,
    contact_phone,
    contact_region,
    primary_contact_flag,
    escalation_level,
    last_contact_date,
    next_scheduled_meeting,
    contract_id
FROM bronze_vendor_relationships;

L2 Analytics (Silver→Gold)​

-- Create comprehensive vendor contact directory
CREATE VIEW gold_vendor_directory AS
SELECT
    vc.vendor_name,
    vc.contact_type,
    vc.contact_name,
    vc.contact_email,
    vc.contact_phone,
    vc.primary_contact_flag,
    c.product_name,
    c.contract_end_date,
    c.annual_contract_value,
    vc.last_contact_date,
    vc.next_scheduled_meeting,
    DATEDIFF(CURRENT_DATE, vc.last_contact_date) as days_since_contact
FROM silver_vendor_contacts vc
LEFT JOIN silver_contract_expirations c ON vc.contract_id = c.contract_id
WHERE vc.contact_type IN ('account manager', 'sales')
ORDER BY vc.vendor_name, vc.primary_contact_flag DESC;

Cerebro Enhancement​

  • Memory: Interaction history, relationship quality indicators, vendor responsiveness metrics, escalation outcomes, negotiation history
  • Intelligence: Contact recommendation engine (who to reach out to for specific issues), optimal contact timing, relationship health scoring
  • Reasoning: Escalation path recommendations, relationship risk alerts (no recent contact before renewal), stakeholder coordination suggestions

UI Representation​

Component: Vendor Portal - Contact Directory

Visualization:

┌─────────────────────────────────────────────────────────────┐
│ Vendor Contacts                        Search: [________]   │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Microsoft                                  $3.2M | May 31  │
│  ┌────────────────────────────────────────────────┐        │
│  │ 👤 Sarah Johnson (Account Manager)             │        │
│  │    📧 [email protected]                   │        │
│  │    📞 +1-425-555-0142                          │        │
│  │    Last contact: 12 days ago                   │        │
│  │    Next meeting: Jan 28                        │        │
│  │                                                 │        │
│  │ 🔧 Mike Chen (Technical Account Manager)       │        │
│  │    📧 [email protected]                      │        │
│  │    📞 +1-425-555-0198                          │        │
│  │    [Contact] [Schedule Meeting] [View History] │        │
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  Salesforce                                 $2.1M | Apr 1   │
│  ┌────────────────────────────────────────────────┐        │
│  │ 👤 Alex Rodriguez (Enterprise Account Exec)    │        │
│  │    📧 [email protected]                │        │
│  │    📞 +1-415-555-0276                          │        │
│  │    ⚠️ No contact in 45 days - Renewal in 76d  │        │
│  │    [Contact Now] [Schedule Call]               │        │
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  [Sort by Vendor] [Sort by Contract Value] [Export]        │
└─────────────────────────────────────────────────────────────┘

Interactions:

  • Click contact to see full profile, interaction history, and communication logs
  • One-click email or schedule meeting directly from the interface
  • Set reminders for periodic vendor check-ins
  • View escalation contacts for critical issues
  • Filter by vendor, contact type, or upcoming renewal dates

Copilot Query Example:

"Who is our Salesforce account manager and when did we last talk to them?" Answer: "Your Salesforce account manager is Alex Rodriguez ([email protected], +1-415-555-0276). Last contact was 45 days ago on Dec 1, 2025, when you discussed Q1 usage trends. Note: Your Salesforce contract renews in 76 days (Apr 1) - recommend scheduling a renewal discussion in the next 2 weeks to allow time for negotiation."

Q6: "What's the proposed renewal increase percentage?"​

Why it matters: Understanding vendor renewal pricing enables budget planning, supports negotiation strategies, identifies excessive increases for pushback, and allows time to evaluate alternatives if renewal terms are unfavorable.

Telemetry Sources​

  • Vendor Quotes/Proposals: Renewal quotes, pricing proposals
  • Contract Management System: Current contract values, historical renewal terms
  • Email/Communication Systems: Renewal notices, vendor communications
  • Finance/ERP: Historical spend data, approved budgets

L1 Analytics (Bronze→Silver)​

-- Normalize renewal pricing data
CREATE VIEW silver_renewal_pricing AS
SELECT
    contract_id,
    vendor_name,
    product_name,
    current_contract_value,
    current_contract_end_date,
    proposed_renewal_value,
    proposed_renewal_term,
    renewal_quote_date,
    renewal_quote_valid_until,
    pricing_notes,
    quote_source -- vendor, internal estimate
FROM bronze_renewal_quotes
UNION ALL
SELECT
    contract_id,
    vendor_name,
    product_name,
    current_annual_value as current_contract_value,
    contract_end_date as current_contract_end_date,
    NULL as proposed_renewal_value,
    NULL as proposed_renewal_term,
    NULL as renewal_quote_date,
    NULL as renewal_quote_valid_until,
    NULL as pricing_notes,
    'current_contract' as quote_source
FROM bronze_contracts
WHERE contract_status = 'Active';

L2 Analytics (Silver→Gold)​

-- Calculate renewal increase percentages
CREATE VIEW gold_renewal_analysis AS
SELECT
    vendor_name,
    product_name,
    current_contract_value,
    proposed_renewal_value,
    (proposed_renewal_value - current_contract_value) as increase_amount,
    ROUND((proposed_renewal_value - current_contract_value) /
          NULLIF(current_contract_value, 0) * 100, 1) as increase_pct,
    current_contract_end_date,
    DATEDIFF(current_contract_end_date, CURRENT_DATE) as days_until_expiration,
    CASE
        WHEN (proposed_renewal_value - current_contract_value) / NULLIF(current_contract_value, 0) > 0.15
        THEN 'High Increase'
        WHEN (proposed_renewal_value - current_contract_value) / NULLIF(current_contract_value, 0) > 0.08
        THEN 'Moderate Increase'
        WHEN (proposed_renewal_value - current_contract_value) / NULLIF(current_contract_value, 0) > 0
        THEN 'Standard Increase'
        ELSE 'No Increase'
    END as increase_category,
    renewal_quote_date,
    renewal_quote_valid_until
FROM silver_renewal_pricing
WHERE proposed_renewal_value IS NOT NULL
ORDER BY increase_pct DESC;

Cerebro Enhancement​

  • Memory: Historical renewal increase patterns by vendor, industry standard increase rates, past negotiation outcomes, accepted vs rejected increases
  • Intelligence: Market rate benchmarking, excessive increase detection, vendor pricing strategy analysis, budget impact forecasting
  • Reasoning: Negotiation leverage identification, alternative vendor recommendations for high increases, optimal negotiation strategy based on vendor patterns, budget variance impact analysis

UI Representation​

Component: License Management - Renewal Pricing Monitor

Visualization:

┌─────────────────────────────────────────────────────────────┐
│ Renewal Price Changes                  Q1-Q2 2026          │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  🔴 High Increases (>15%) - Negotiate                       │
│  ┌────────────────────────────────────────────────┐        │
│  │ Datadog        $280K → $350K  [+25%] 📈 Feb 15 │  Alt?  │
│  │ Monday.com     $145K → $180K  [+24%] 📈 Mar 8  │  Alt?  │
│  │ Snowflake      $420K → $520K  [+24%] 📈 Apr 2  │  Alt?  │
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  🟡 Moderate Increases (8-15%)                              │
│  ┌────────────────────────────────────────────────┐        │
│  │ Salesforce   $2.1M → $2.3M  [+10%]    Apr 1    │        │
│  │ Adobe        $1.5M → $1.6M  [+8%]     Mar 15   │        │
│  │ ServiceNow   $1.3M → $1.4M  [+9%]     Apr 18   │        │
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  🟢 Standard Increases (&lt;8%)                                │
│  │ Slack         $820K → $860K  [+5%]    Mar 22   │        │
│  │ Zoom          $450K → $470K  [+4%]    Jan 28   │        │
│                                                              │
│  Total Budget Impact: +$1.2M (+11% weighted avg)           │
│                                                              │
│  [View Details] [Compare Alternatives] [Export Report]     │
└─────────────────────────────────────────────────────────────┘

Interactions:

  • Click renewal to see full quote details, historical pricing, and negotiation notes
  • View market benchmark data and alternative vendor pricing
  • Initiate vendor negotiation workflow with talking points
  • Flag excessive increases for executive review
  • Model budget scenarios (accept all, negotiate down, replace vendors)

Copilot Query Example:

"Which vendors are proposing renewal increases above 15% and what's the total budget impact?" Answer: "3 vendors proposed increases >15%: Datadog (+25%, +$70K), Monday.com (+24%, +$35K), Snowflake (+24%, +$100K). Total impact: $205K additional spend. These are above market average (typical SaaS increases: 5-10%). Recommendations: (1) Datadog - competitors (New Relic, Dynatrace) offer 15-20% lower pricing for similar features, (2) Monday.com - Asana/ClickUp alternatives at 30% lower cost, (3) Snowflake - negotiate committed consumption discount (typically 10-15% off). Potential savings if negotiated: $82K."

Q7: "What are potential alternative tools?"​

Why it matters: Identifying alternative vendors provides negotiation leverage, enables cost optimization through vendor switching, supports disaster recovery planning, and allows evaluation of better-fit solutions before renewal commitments.

Telemetry Sources​

  • Market Intelligence Platforms: G2, Gartner, TrustRadius for vendor comparisons
  • Vendor Analysis Tools: Feature matrices, pricing comparisons
  • Internal Knowledge Base: Past vendor evaluations, POC results
  • CASB: Shadow IT discovery (departments already using alternatives)

L1 Analytics (Bronze→Silver)​

-- Normalize alternative vendor data
CREATE VIEW silver_vendor_alternatives AS
SELECT
    primary_product_name,
    primary_vendor_name,
    alternative_product_name,
    alternative_vendor_name,
    feature_parity_score, -- 0-100
    pricing_comparison, -- cheaper, similar, more expensive
    estimated_annual_cost,
    migration_complexity, -- low, medium, high
    data_source, -- market research, internal eval, shadow IT
    last_evaluated_date
FROM bronze_market_intelligence
UNION ALL
SELECT
    app_name as primary_product_name,
    primary_vendor as primary_vendor_name,
    shadow_app_name as alternative_product_name,
    shadow_vendor as alternative_vendor_name,
    NULL as feature_parity_score,
    'unknown' as pricing_comparison,
    NULL as estimated_annual_cost,
    NULL as migration_complexity,
    'shadow_it' as data_source,
    discovery_date as last_evaluated_date
FROM bronze_casb_shadow_it
WHERE shadow_app_category = sanctioned_app_category;

L2 Analytics (Silver→Gold)​

-- Rank alternatives by suitability
CREATE VIEW gold_alternative_analysis AS
SELECT
    primary_product_name,
    primary_vendor_name,
    alternative_product_name,
    alternative_vendor_name,
    feature_parity_score,
    pricing_comparison,
    estimated_annual_cost,
    migration_complexity,
    CASE
        WHEN feature_parity_score >= 90 AND pricing_comparison = 'cheaper' AND migration_complexity = 'low'
        THEN 'High Potential'
        WHEN feature_parity_score >= 80 AND pricing_comparison IN ('cheaper', 'similar')
        THEN 'Medium Potential'
        ELSE 'Low Potential'
    END as replacement_potential,
    data_source,
    last_evaluated_date,
    CASE
        WHEN data_source = 'shadow_it' THEN 'Already in use by some teams'
        ELSE NULL
    END as adoption_note
FROM silver_vendor_alternatives
WHERE feature_parity_score >= 70 OR data_source = 'shadow_it'
ORDER BY replacement_potential, feature_parity_score DESC;

Cerebro Enhancement​

  • Memory: Historical vendor evaluations, POC outcomes, migration experiences, feature requirement priorities, team preferences
  • Intelligence: Feature gap analysis, total cost of ownership (TCO) comparison including migration costs, risk assessment, shadow IT usage patterns indicating organic alternative adoption
  • Reasoning: Replacement recommendations based on renewal timing, negotiation leverage talking points, migration planning guidance, business case generation for vendor switches

UI Representation​

Component: Vendor Portal - Alternative Solutions Explorer

Visualization:

┌─────────────────────────────────────────────────────────────┐
│ Alternative Vendor Analysis                                 │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Analyzing: Datadog ($350K renewal, +25% increase)         │
│                                                              │
│  🎯 High Potential Alternatives                             │
│  ┌────────────────────────────────────────────────┐        │
│  │ New Relic                                       │        │
│  │   Feature match: 92%  |  Est cost: $280K/yr   │        │
│  │   Migration: Medium   |  Savings: $70K (20%)  │        │
│  │   Note: Already in use by Platform Eng (5 users)       │
│  │   [View Comparison] [Request Demo] [Build Case]        │
│  │                                                 │        │
│  │ Dynatrace                                       │        │
│  │   Feature match: 88%  |  Est cost: $295K/yr   │        │
│  │   Migration: Medium   |  Savings: $55K (16%)  │        │
│  │   [View Comparison] [Request Demo]             │        │
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  💡 Negotiation Leverage                                    │
│  • New Relic offers similar capability at 20% lower cost   │
│  • Dynatrace willing to match competitor pricing           │
│  • Current 25% increase is 2.5x market average             │
│                                                              │
│  [Download TCO Analysis] [View Feature Matrix] [Contact]   │
└─────────────────────────────────────────────────────────────┘

Interactions:

  • Select any product to see alternative vendor options with detailed comparisons
  • Filter alternatives by feature parity, pricing, or migration complexity
  • View side-by-side feature comparisons and pricing breakdowns
  • Generate business case documents for vendor replacement
  • Track shadow IT discoveries showing organic alternative adoption

Copilot Query Example:

"What are good alternatives to Datadog and how much could we save?" Answer: "Top Datadog alternatives: (1) New Relic - 92% feature match, $280K/yr vs $350K proposed (save $70K, 20%), medium migration complexity, (2) Dynatrace - 88% match, $295K/yr (save $55K, 16%), (3) Grafana Cloud - 78% match, $180K/yr (save $170K, 49%) but missing some APM features. Note: New Relic is already being used by your Platform Engineering team (5 users, $2K/yr) - could expand this deployment. Recommendation: Use New Relic pricing in Datadog negotiation as leverage, target $300K or explore full New Relic migration."

Q8: "Which users have licenses but aren't using them?"​

Why it matters: Identifying inactive license assignments enables immediate cost recovery through reclamation, improves license utilization metrics, supports capacity planning, and provides data-driven justification for vendor negotiations and right-sizing decisions.

Telemetry Sources​

  • UEM (Jamf/Intune): Application launch logs, last used timestamps
  • Vendor APIs: User login activity, session history, feature engagement
  • IDP (Okta/Azure AD): Authentication logs, app access events
  • ITAM/SAM Platform: License assignment records, user status

L1 Analytics (Bronze→Silver)​

-- Normalize inactive license data
CREATE VIEW silver_inactive_licenses AS
SELECT
    lu.user_id,
    lu.user_name,
    lu.user_email,
    u.department,
    u.manager_name,
    u.employment_status,
    lu.product_name,
    lu.license_assigned_date,
    lu.last_login_date,
    lu.last_30d_logins,
    lu.last_90d_logins,
    DATEDIFF(CURRENT_DATE, lu.last_login_date) as days_since_last_use,
    lp.license_cost_annual,
    la.license_assignment_method -- manual, auto-provisioned, role-based
FROM silver_license_usage lu
JOIN bronze_users u ON lu.user_id = u.user_id
JOIN bronze_license_pricing lp ON lu.product_id = lp.product_id
LEFT JOIN bronze_license_assignments la ON lu.user_id = la.user_id AND lu.product_id = la.product_id
WHERE lu.last_90d_logins < 5; -- Low/no usage threshold

L2 Analytics (Silver→Gold)​

-- Prioritize reclamation candidates
CREATE VIEW gold_inactive_license_report AS
SELECT
    product_name,
    user_name,
    user_email,
    department,
    manager_name,
    employment_status,
    license_assigned_date,
    last_login_date,
    days_since_last_use,
    last_30d_logins,
    last_90d_logins,
    license_cost_annual,
    CASE
        WHEN last_90d_logins = 0 AND days_since_last_use > 90 THEN 'Immediate Reclaim'
        WHEN last_90d_logins <= 2 AND days_since_last_use > 60 THEN 'High Priority'
        WHEN last_30d_logins = 0 AND last_90d_logins <= 5 THEN 'Medium Priority'
        ELSE 'Monitor'
    END as reclamation_priority,
    CASE
        WHEN employment_status != 'Active' THEN 'Terminated/Inactive Employee'
        WHEN license_assignment_method = 'manual' THEN 'Manual Assignment - Review Needed'
        WHEN days_since_last_use > 180 THEN 'Likely Abandoned'
        ELSE 'Low Adoption'
    END as reclamation_reason
FROM silver_inactive_licenses
ORDER BY reclamation_priority, license_cost_annual DESC;

-- Calculate reclamation value by product
CREATE VIEW gold_reclamation_value_summary AS
SELECT
    product_name,
    COUNT(DISTINCT user_id) as inactive_user_count,
    SUM(license_cost_annual) as total_reclamation_value,
    AVG(days_since_last_use) as avg_days_inactive,
    COUNT(DISTINCT CASE WHEN reclamation_priority = 'Immediate Reclaim' THEN user_id END) as immediate_reclaim_count,
    SUM(CASE WHEN reclamation_priority = 'Immediate Reclaim' THEN license_cost_annual ELSE 0 END) as immediate_reclaim_value
FROM gold_inactive_license_report
GROUP BY product_name
ORDER BY total_reclamation_value DESC;

Cerebro Enhancement​

  • Memory: Historical reclamation outcomes, user pushback patterns, successful reclamation strategies, seasonal usage patterns, role-based usage norms
  • Intelligence: User behavior classification (abandoned vs low-need vs seasonal), manager communication history, reclamation impact prediction, re-assignment probability
  • Reasoning: Optimal reclamation timing, user notification personalization, manager escalation triggers, risk assessment for business-critical users, automated workflow recommendations

UI Representation​

Component: License Management - Reclamation Workflow

Visualization:

┌─────────────────────────────────────────────────────────────┐
│ Inactive License Reclamation              Total: $186K      │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Figma Professional              68 inactive │ Value: $42K  │
│  ┌────────────────────────────────────────────────┐        │
│  │ 🔴 Immediate Reclaim (90+ days, 0 logins)      │        │
│  │    Sarah Chen (Marketing)      Last: 142d ago  │  [✓]   │
│  │    Mike Rodriguez (Sales)      Last: 128d ago  │  [✓]   │
│  │    Emma Wilson (Design)        Last: 98d ago   │  [✓]   │
│  │    + 9 more users...                    $7.4K  │  [✓ All]│
│  │                                                 │        │
│  │ 🟡 High Priority (60-90 days, &lt;3 logins)       │        │
│  │    Alex Kim (Product)          Last: 73d ago   │  [✓]   │
│  │    Jordan Lee (Eng)            Last: 68d ago   │  [✓]   │
│  │    + 8 more users...                    $6.2K  │  [✓ All]│
│  │                                                 │        │
│  │ 🟢 Medium Priority (30-60 days, &lt;5 logins)     │        │
│  │    Casey Brown (Marketing)     Last: 45d ago   │  [✓]   │
│  │    + 45 more users...                  $28.4K  │  [✓ All]│
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  [Preview Notification] [Bulk Reclaim] [Export Report]     │
│  [Notify Managers] [Request Justification]                 │
└─────────────────────────────────────────────────────────────┘

Interactions:

  • Select individual users or bulk-select by priority level for reclamation
  • Preview notification emails sent to users and their managers
  • Set grace period before reclamation (7, 14, 30 days)
  • Request usage justification from managers before reclaiming
  • Track reclamation workflow status (notified, justified, reclaimed, reassigned)
  • Generate manager reports showing their team's inactive licenses

Copilot Query Example:

"Show me all users who haven't logged into Figma in 90+ days and calculate how much we'd save by reclaiming those licenses" Answer: "12 users haven't logged into Figma in 90+ days: Sarah Chen (142 days), Mike Rodriguez (128 days), Emma Wilson (98 days), and 9 others. All 12 have 0 logins in the last 90 days. Total reclamation value: $7,440/year ($620/month). These are 'Immediate Reclaim' candidates with very low risk. Recommendation: Send 7-day reclamation notice to users and their managers. If no justification provided, reclaim licenses and add to available pool (currently at 0 available)."

Q9: "Are we compliant with our agreements?"​

Why it matters: License compliance monitoring prevents costly vendor audits, avoids penalties and true-up charges, maintains vendor relationships, and provides audit readiness documentation for internal and external compliance requirements.

Telemetry Sources​

  • ITAM/SAM Platform: License entitlements, deployments, compliance status
  • UEM (Jamf/Intune): Installed software inventory, version tracking
  • Contract Management System: License terms, restrictions, permitted use
  • Vendor APIs: Subscription limits, usage metrics, compliance thresholds

L1 Analytics (Bronze→Silver)​

-- Normalize compliance data
CREATE VIEW silver_license_compliance AS
SELECT
    product_name,
    vendor_name,
    contract_id,
    license_type,
    entitled_quantity,
    deployed_quantity,
    active_usage_quantity,
    contract_restrictions, -- named user, device-based, concurrent, geo restrictions
    compliance_metric_type, -- over-deployed, under-licensed, incorrect deployment
    last_compliance_check_date
FROM bronze_sam_compliance_data;

L2 Analytics (Silver→Gold)​

-- Calculate compliance status and risk
CREATE VIEW gold_compliance_status AS
SELECT
    product_name,
    vendor_name,
    license_type,
    entitled_quantity,
    deployed_quantity,
    (deployed_quantity - entitled_quantity) as quantity_variance,
    CASE
        WHEN deployed_quantity > entitled_quantity THEN 'Over-Deployed'
        WHEN deployed_quantity < entitled_quantity * 0.5 THEN 'Significantly Under-Utilized'
        ELSE 'Compliant'
    END as compliance_status,
    CASE
        WHEN deployed_quantity > entitled_quantity
        THEN (deployed_quantity - entitled_quantity) * license_cost_per_unit
        ELSE 0
    END as estimated_true_up_cost,
    CASE
        WHEN deployed_quantity > entitled_quantity AND (deployed_quantity - entitled_quantity) / entitled_quantity > 0.1
        THEN 'High Risk'
        WHEN deployed_quantity > entitled_quantity
        THEN 'Medium Risk'
        ELSE 'Low Risk'
    END as audit_risk_level,
    contract_restrictions,
    last_compliance_check_date
FROM silver_license_compliance lc
LEFT JOIN bronze_license_pricing lp ON lc.product_id = lp.product_id;

-- Aggregate compliance by vendor
CREATE VIEW gold_vendor_compliance_summary AS
SELECT
    vendor_name,
    COUNT(DISTINCT product_name) as product_count,
    COUNT(DISTINCT CASE WHEN compliance_status = 'Over-Deployed' THEN product_name END) as non_compliant_products,
    SUM(estimated_true_up_cost) as total_potential_true_up,
    MAX(audit_risk_level) as highest_risk_level,
    STRING_AGG(CASE WHEN compliance_status = 'Over-Deployed' THEN product_name END, ', ') as at_risk_products
FROM gold_compliance_status
GROUP BY vendor_name
ORDER BY total_potential_true_up DESC;

Cerebro Enhancement​

  • Memory: Historical audit outcomes, vendor audit patterns, true-up negotiation history, compliance remediation timelines, vendor audit aggressiveness ratings
  • Intelligence: Audit probability prediction, compliance drift detection, license usage trend analysis, early warning for approaching limits
  • Reasoning: Proactive remediation recommendations, purchase timing optimization (buy before audit vs after), negotiation strategy for true-ups, audit preparation checklists

UI Representation​

Component: Compliance Monitor - Dashboard

Visualization:

┌─────────────────────────────────────────────────────────────┐
│ License Compliance Status          Last Check: 2hrs ago     │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Overall Compliance: 94%                                    │
│  🟢 Compliant: 47 products | 🔴 At Risk: 3 products        │
│                                                              │
│  🔴 Critical Compliance Issues                              │
│  ┌────────────────────────────────────────────────┐        │
│  │ AutoCAD                                         │        │
│  │   Entitled: 50  │  Deployed: 58  │  Over: +8   │        │
│  │   Est. True-Up: $18K  │  Audit Risk: High      │        │
│  │   Action: Purchase 10 licenses before Q2 audit │  [Fix] │
│  │                                                 │        │
│  │ Adobe Creative Cloud                            │        │
│  │   Entitled: 85  │  Deployed: 91  │  Over: +6   │        │
│  │   Est. True-Up: $14K  │  Audit Risk: Medium    │        │
│  │   Action: Reclaim 6 inactive licenses          │  [Fix] │
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  Vendor Audit Risk Assessment:                              │
│  • Adobe: Medium (Last audit: 18 mo ago)                   │
│  • Autodesk: High (Audit notice period)                    │
│  • Microsoft: Low (EA true-up quarterly)                   │
│                                                              │
│  [Run Compliance Scan] [Audit Prep Report] [Remediate]     │
└─────────────────────────────────────────────────────────────┘

Interactions:

  • Click product to see detailed deployment vs entitlement breakdown
  • Initiate remediation workflows (purchase additional licenses, reclaim over-deployments, uninstall software)
  • Generate audit-ready compliance reports for specific vendors
  • Set automated compliance check schedules and alert thresholds
  • View historical compliance trends and remediation tracking

Copilot Query Example:

"Are we compliant with our Adobe licenses and what's our audit risk?" Answer: "You have 3 Adobe products with compliance issues: Creative Cloud (91 deployed vs 85 entitled, +6 over), Acrobat Pro (87 deployed vs 85 entitled, +2 over), Adobe Sign (compliant, 62 deployed vs 70 entitled). Total estimated true-up cost: $16,800. Audit risk: Medium. Adobe's last audit was 18 months ago (typical cycle: 24-36 months). Recommended actions: (1) Reclaim 6 inactive Creative Cloud licenses (53 users inactive 60+ days), (2) Purchase 5 additional Acrobat Pro licenses ($1,200), (3) Run full Adobe compliance scan before renewal (Mar 15). Timeline: Complete remediation within 30 days to achieve full compliance."

Q10: "What software is installed that we didn't buy?"​

Why it matters: Shadow IT discovery identifies security risks, prevents compliance violations, reveals unsanctioned spending, uncovers redundant tools, and provides opportunities to negotiate enterprise agreements for widely-adopted unauthorized software.

Telemetry Sources​

  • CASB (Netskope/Zscaler): Cloud app discovery, SaaS usage, OAuth grants
  • UEM (Jamf/Intune): Installed application inventory on managed devices
  • Network Monitoring: Traffic analysis, DNS queries, app signatures
  • IDP (Okta/Azure AD): Unapproved app integrations, SSO requests
  • Procurement System: Authorized software purchases for comparison

L1 Analytics (Bronze→Silver)​

-- Normalize shadow IT discovery data
CREATE VIEW silver_shadow_it AS
SELECT
    app_name,
    app_vendor,
    app_category,
    discovery_source, -- CASB, UEM, Network, IDP
    discovery_date,
    user_count,
    device_count,
    data_sensitivity_level, -- high, medium, low
    security_risk_score, -- 0-100
    estimated_monthly_cost,
    sanctioned_alternative_exists,
    sanctioned_alternative_name
FROM bronze_casb_shadow_apps
WHERE sanctioned_status = FALSE
UNION ALL
SELECT
    application_name as app_name,
    vendor as app_vendor,
    category as app_category,
    'UEM' as discovery_source,
    install_date as discovery_date,
    COUNT(DISTINCT user_id) as user_count,
    COUNT(DISTINCT device_id) as device_count,
    NULL as data_sensitivity_level,
    NULL as security_risk_score,
    NULL as estimated_monthly_cost,
    NULL as sanctioned_alternative_exists,
    NULL as sanctioned_alternative_name
FROM bronze_installed_applications
WHERE application_name NOT IN (SELECT product_name FROM bronze_license_entitlements)
GROUP BY application_name, vendor, category, install_date;

L2 Analytics (Silver→Gold)​

-- Prioritize shadow IT by risk and impact
CREATE VIEW gold_shadow_it_analysis AS
SELECT
    app_name,
    app_vendor,
    app_category,
    user_count,
    device_count,
    data_sensitivity_level,
    security_risk_score,
    estimated_monthly_cost,
    sanctioned_alternative_exists,
    sanctioned_alternative_name,
    CASE
        WHEN data_sensitivity_level = 'high' AND security_risk_score > 70 THEN 'Critical - Block'
        WHEN data_sensitivity_level = 'high' OR security_risk_score > 60 THEN 'High Risk - Review'
        WHEN user_count > 50 THEN 'Widespread Use - Consider Sanctioning'
        ELSE 'Monitor'
    END as priority_action,
    CASE
        WHEN sanctioned_alternative_exists = TRUE
        THEN CONCAT('Users should migrate to ', sanctioned_alternative_name)
        WHEN user_count > 25
        THEN 'High adoption - evaluate for enterprise licensing'
        ELSE 'Low impact - monitor or block'
    END as recommendation,
    discovery_date,
    DATEDIFF(CURRENT_DATE, discovery_date) as days_active
FROM silver_shadow_it
ORDER BY security_risk_score DESC, user_count DESC;

-- Aggregate shadow IT spend by category
CREATE VIEW gold_shadow_spend_summary AS
SELECT
    app_category,
    COUNT(DISTINCT app_name) as app_count,
    SUM(user_count) as total_users,
    SUM(estimated_monthly_cost * 12) as estimated_annual_spend,
    AVG(security_risk_score) as avg_risk_score,
    STRING_AGG(CASE WHEN user_count > 25 THEN app_name END, ', ') as high_adoption_apps
FROM silver_shadow_it
GROUP BY app_category
ORDER BY estimated_annual_spend DESC;

Cerebro Enhancement​

  • Memory: Historical shadow IT trends, remediation outcomes, user pushback patterns, successful migration strategies, sanctioned alternatives catalog
  • Intelligence: Risk correlation analysis (apps with data exposure incidents), spend waste calculation, redundancy detection (shadow IT overlapping with sanctioned tools), organic adoption signals
  • Reasoning: Sanction vs block decision support, enterprise licensing ROI analysis, migration planning, security remediation prioritization, policy enforcement recommendations

UI Representation​

Component: Compliance Monitor - Shadow IT Discovery

Visualization:

┌─────────────────────────────────────────────────────────────┐
│ Shadow IT Discovery                    Est Annual: $124K    │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  🔴 Critical Risk - Immediate Action Required               │
│  ┌────────────────────────────────────────────────┐        │
│  │ WeTransfer (File Sharing)                       │        │
│  │   68 users  │  Risk: 89  │  Data: High         │        │
│  │   Est cost: $8K/yr  │  Alt: Box (sanctioned)  │        │
│  │   Action: Block & migrate to Box               │  [Act] │
│  │                                                 │        │
│  │ Personal Dropbox (Cloud Storage)                │        │
│  │   43 users  │  Risk: 82  │  Data: High         │        │
│  │   Est cost: $5K/yr  │  Alt: OneDrive (sanctioned)     │
│  │   Action: Block & migrate to OneDrive          │  [Act] │
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  🟡 High Adoption - Consider Enterprise License             │
│  ┌────────────────────────────────────────────────┐        │
│  │ Notion (Collaboration)                          │        │
│  │   127 users  │  Risk: 45  │  Est: $51K/yr      │        │
│  │   Note: Overlaps with Confluence (low adoption) │        │
│  │   Action: Evaluate enterprise license vs migrate│  [Eval]│
│  │                                                 │        │
│  │ Loom (Video)                                    │        │
│  │   89 users  │  Risk: 32  │  Est: $36K/yr       │        │
│  │   Action: Negotiate enterprise agreement        │  [Eval]│
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  [Block Apps] [Request Enterprise License] [Export]        │
└─────────────────────────────────────────────────────────────┘

Interactions:

  • Click app to see detailed usage patterns, user list, data access, security risks
  • Initiate blocking workflow (CASB policy, network block, UEM uninstall)
  • Request enterprise license evaluation for high-adoption shadow IT
  • Notify users and managers about policy violations and approved alternatives
  • Generate executive summary of shadow IT spend and risk exposure

Copilot Query Example:

"What unauthorized software has been discovered in the last 30 days and what are the security risks?" Answer: "7 new shadow IT apps discovered in the last 30 days: (1) WeTransfer - 68 users, high data sensitivity, risk score 89, file sharing to external domains, $8K/yr estimated cost, (2) Personal Dropbox - 43 users, high data, risk score 82, unencrypted sync, $5K/yr, (3) Trello personal - 34 users, medium data, risk score 54, $4K/yr, (4) ChatGPT Plus - 28 users, high data (code/docs shared), risk score 76, $7K/yr, and 3 others. Critical actions: Block WeTransfer and personal Dropbox immediately (data exfiltration risk), migrate users to sanctioned alternatives (Box, OneDrive). Total shadow spend discovered: $38K/yr."

Q11: "Which terminated employees still have licenses?"​

Why it matters: Terminated employee license cleanup prevents security risks from unauthorized access, recovers unused license costs immediately, ensures compliance with offboarding procedures, and maintains accurate license inventory for capacity planning.

Telemetry Sources​

  • HRIS (Workday/BambooHR): Employee termination dates, offboarding status
  • IDP (Okta/Azure AD): Account deactivation status, app access revocation
  • ITAM/SAM Platform: License assignments, deprovisioning status
  • UEM (Jamf/Intune): Device return status, access wipe confirmation

L1 Analytics (Bronze→Silver)​

-- Normalize terminated employee license data
CREATE VIEW silver_terminated_employee_licenses AS
SELECT
    u.user_id,
    u.user_name,
    u.user_email,
    u.department,
    u.termination_date,
    u.offboarding_status,
    la.product_name,
    la.license_assigned_date,
    la.license_cost_annual,
    la.license_still_active,
    ia.idp_account_status,
    ia.last_access_date,
    DATEDIFF(CURRENT_DATE, u.termination_date) as days_since_termination
FROM bronze_users u
JOIN bronze_license_assignments la ON u.user_id = la.user_id
LEFT JOIN bronze_idp_accounts ia ON u.user_id = ia.user_id
WHERE u.employment_status = 'Terminated'
    AND la.license_still_active = TRUE;

L2 Analytics (Silver→Gold)​

-- Prioritize terminated employee license reclamation
CREATE VIEW gold_terminated_license_cleanup AS
SELECT
    user_name,
    user_email,
    department,
    termination_date,
    days_since_termination,
    product_name,
    license_cost_annual,
    idp_account_status,
    last_access_date,
    CASE
        WHEN idp_account_status = 'Active' THEN 'Critical - Active Account'
        WHEN days_since_termination > 30 THEN 'Overdue Cleanup'
        WHEN days_since_termination > 7 THEN 'Standard Cleanup'
        ELSE 'Recent Termination'
    END as cleanup_priority,
    CASE
        WHEN idp_account_status = 'Active' AND last_access_date > DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
        THEN 'Security Risk - Recent Access'
        WHEN days_since_termination > 60
        THEN 'Process Failure - Missed Offboarding'
        ELSE 'Standard Reclamation'
    END as issue_category,
    offboarding_status
FROM silver_terminated_employee_licenses
ORDER BY cleanup_priority, days_since_termination DESC;

-- Calculate cost recovery from terminated employee licenses
CREATE VIEW gold_terminated_license_value AS
SELECT
    product_name,
    COUNT(DISTINCT user_id) as terminated_user_count,
    SUM(license_cost_annual) as total_recovery_value,
    AVG(days_since_termination) as avg_days_since_termination,
    COUNT(DISTINCT CASE WHEN idp_account_status = 'Active' THEN user_id END) as active_account_count,
    COUNT(DISTINCT CASE WHEN days_since_termination > 30 THEN user_id END) as overdue_count
FROM silver_terminated_employee_licenses
GROUP BY product_name
ORDER BY total_recovery_value DESC;

Cerebro Enhancement​

  • Memory: Historical offboarding compliance rates, license cleanup timing patterns, security incident history from terminated employee access, process gap identification
  • Intelligence: Offboarding workflow gap detection, automated deprovisioning effectiveness, early warning for upcoming terminations (license forecasting), security risk scoring
  • Reasoning: Automated license reclamation workflows, offboarding process improvement recommendations, security incident prevention, cost recovery prioritization

UI Representation​

Component: License Management - Terminated Employee Cleanup

Visualization:

┌─────────────────────────────────────────────────────────────┐
│ Terminated Employee License Cleanup        Value: $42K/yr   │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  🔴 Critical - Active Accounts (Security Risk)              │
│  ┌────────────────────────────────────────────────┐        │
│  │ John Smith (Engineering)                        │        │
│  │   Terminated: 45 days ago  │  Account: Active  │        │
│  │   Last Access: 2 days ago  │  5 licenses: $8.2K│        │
│  │   Products: GitHub, AWS, Salesforce, Slack, Figma      │
│  │   🚨 Action: Immediate account suspension       │  [Act] │
│  │                                                 │        │
│  │ Sarah Lee (Sales)                               │        │
│  │   Terminated: 18 days ago  │  Account: Active  │        │
│  │   Last Access: 5 days ago  │  3 licenses: $4.8K│        │
│  │   Products: Salesforce, Outreach, LinkedIn Sales       │
│  │   🚨 Action: Immediate account suspension       │  [Act] │
│  └────────────────────────────────────────────────┘        │
│                                                              │
│  🟡 Overdue Cleanup (>30 days)                              │
│  │ Mike Chen (Marketing)       62 days  │  $6.4K  │  [Recl]│
│  │ Emma Wilson (Product)       48 days  │  $5.1K  │  [Recl]│
│  │ + 8 more users...                            │  [All] │
│                                                              │
│  🟢 Recent Terminations (&lt;7 days)                           │
│  │ Alex Kim (Design)            4 days  │  $2.8K  │  [Recl]│
│  │ Jordan Taylor (Support)      6 days  │  $1.9K  │  [Recl]│
│                                                              │
│  [Bulk Reclaim] [Suspend Accounts] [Offboarding Report]    │
└─────────────────────────────────────────────────────────────┘

Interactions:

  • Click user to see complete license assignment list and account status across all systems
  • Initiate immediate account suspension for critical security risks
  • Bulk reclaim licenses from terminated employees by department or termination date range
  • Generate offboarding compliance report showing missed deprovisioning steps
  • Set automated deprovisioning workflows triggered by HRIS termination events
  • Track offboarding SLA compliance and process improvement metrics

Copilot Query Example:

"Show me all terminated employees who still have active accounts and licenses, prioritized by security risk" Answer: "2 terminated employees have active accounts with recent access (HIGH SECURITY RISK): (1) John Smith - terminated 45 days ago, account active, last login 2 days ago, has access to GitHub (code), AWS (infrastructure), Salesforce (customer data), Slack, Figma - $8.2K in licenses, (2) Sarah Lee - terminated 18 days ago, account active, last login 5 days ago, has Salesforce + Outreach (customer data, email access) - $4.8K in licenses. CRITICAL ACTION REQUIRED: Suspend both accounts immediately and investigate recent access activity. Also found 10 terminated employees with inactive accounts but active licenses (>30 days), total recovery value $17.8K - recommend bulk reclamation. Process gap: automated offboarding not triggering properly for Engineering and Sales departments."