💬 EN

Can you please write me some Kusto (KQL) or other (query/queries) that will help me figure out which Entra users who have an active Visual Studio Professional/Enterprise license, how much money they actually use per month in their included Visual Studio Azure subscription over the last 5 years (min, max, median, mean), and their plan level in Azure DevOps

// PART 1: Get Visual Studio subscribers from Entra ID (requires AzureDiagnostics or AuditLogs)
let VSSubscribers = AuditLogs
| where OperationName in ("Add license to user", "Update license for user")
| where TargetResources.any(t, t.modifiedProperties.any(mp, mp.displayName == "LicenseAssignmentList"))
| extend LicenseSKU = tostring(parse_json(tostring(TargetResources[^1_0].modifiedProperties[^1_1].newValue))[^1_0].SkuPartNumber)
| where LicenseSKU in ("VISIOPRO", "VISUAL_STUDIO_ENTERPRISE")  // Adjust SKUs as needed
| distinct UserPrincipalName = tostring(TargetResources[^1_0].userPrincipalName), LicenseSKU;

// PART 2: Get Azure consumption data (requires AzureCostManagement schema)
let ConsumptionData = AzureCostManagement
| where TimeGenerated between (ago(5y) .. now())
| extend UserPrincipalName = tostring(Properties.ConsumedService)
    // Note: Actual user mapping might require custom logic depending on your Azure setup
    // This example assumes costs can be associated via resource groups/tags
| summarize 
    TotalCost=sum(todouble(Properties.PretaxCost)), 
    MonthlyCosts=make_list(todouble(Properties.PretaxCost)) 
    by UserPrincipalName, bin(TimeGenerated, 30d);

// PART 3: Get Azure DevOps access levels (requires custom logging of DevOps audit events)
let DevOpsAccessLevels = AzureDiagnostics
| where ResourceType == "AZUREDEVOPS"
| where OperationName == "User access changed"
| extend UserPrincipalName = tostring(Properties.UserPrincipalName),
         AccessLevel = tostring(Properties.AccessLevel)
| distinct UserPrincipalName, AccessLevel;

// COMBINE RESULTS
VSSubscribers
| join kind=leftouter (ConsumptionData) on UserPrincipalName
| join kind=leftouter (DevOpsAccessLevels) on UserPrincipalName
| summarize 
    MinCost=min(TotalCost),
    MaxCost=max(TotalCost),
    MedianCost=percentile(TotalCost, 50),
    MeanCost=avg(TotalCost),
    PlanLevels=make_set(AccessLevel)
    by UserPrincipalName, LicenseSKU
| project 
    UserPrincipalName,
    LicenseSKU,
    MinCost,
    MaxCost,
    MedianCost,
    MeanCost,
    DevOpsPlanLevels=PlanLevels

   // For actual cost-to-user mapping, add resource tagging strategy:
   Resources
   | project ResourceId, Tags
   | where Tags has "OwnerEmail"  // Your actual cost ownership tag
   | join kind=inner (AzureCostManagement) on $left.ResourceId == $right.ResourceId

   | where TimeGenerated > ago(5y)  // Match 5-year requirement

   | where UserPrincipalName !contains "svc_"

AuditLogs
| where OperationName == "Add license to user"
| extend License = tostring(TargetResources[^1_0].modifiedProperties[^1_1].newValue)
| where License contains "Visual Studio"
| project UserPrincipalName=tostring(TargetResources[^1_0].userPrincipalName), License

AzureCostManagement
| where TimeGenerated > ago(5y)
| extend Owner = todynamic(Tags.OwnerEmail)  // Your actual tag name
| summarize 
    TotalSpent=sum(todouble(Properties.PretaxCost)), 
    MonthlyBreakdown=make_bag(pack_bin(TimeGenerated, 30d), Properties.PretaxCost)
    by UserPrincipalName=tostring(Owner)

# Use Azure DevOps REST API instead
GET https://vsaex.dev.azure.com/{org}/_apis/userentitlements?api-version=6.1-preview.3
# Parse response for accessLevel and license types[^1_1]

| render timechart 
    title="Monthly Costs for VS Licensed Users" 
    kind=line 
    with (legend=visible)

UserPrincipalName          LicenseSKU                  MinCost  MaxCost  Median  Mean  DevOpsPlanLevels
[email protected]          VISUAL_STUDIO_ENTERPRISE    $0       $850     $120    $210  ["Basic", "Test Plans"]
[email protected]          VISIOPRO                    $50      $300     $150    $175  ["Stakeholder"]

AuditLogs
| where OperationName == "Add license to user"
| extend License = tostring(TargetResources[^1_0].modifiedProperties[^1_1].newValue)
| distinct License

Here’s a Kusto Query Language (KQL) solution combining Azure cost analysis and Entra ID/Azure DevOps data: Important Implementation Notes:

1. Data Source Requirements:
   • Azure Cost Management data ingestion must be enabled
   • Entra ID audit logs must be collected
   • Azure DevOps audit logging requires custom configuration¹²
2. Modifications Needed:
3. Visual Studio License Detection:
   • Actual SKU names may vary by tenant¹³
   • Consider adding license assignment date filtering:
4. Azure DevOps Plan Levels:
   • Actual access levels show as “Basic”, “Stakeholder”, or “VS Enterprise” based on licensing¹³
   • Service accounts may need exclusion: Alternative Approach for Missing Data: For organizations without centralized logging, use these separate queries: A. List VS Licensed Users: B. Get Azure Costs per User (requires resource tagging): C. DevOps Access Levels (API alternative): Visualization Suggestion: Key Challenges:
5. User-Cost Mapping: Azure costs aren’t natively user-specific - requires resource tagging strategy
6. License Tracking: Visual Studio license assignment logs might need extended retention
7. Historical Data: 5-year data retention needs explicit configuration in Log Analytics
8. DevOps Plan Detection: Access level logging requires premium DevOps features¹² For production use, consider:
9. Creating Azure Policy to enforce cost ownership tagging
10. Setting up DevOps audit streaming to Log Analytics
11. Using Azure Workbooks to combine these data sources visually Sample Output Structure: Remember to:
12. Validate SKU names in your tenant using:
13. Adjust time ranges based on your actual data retention policies
14. Consider dollar amount formatting in final presentation layers For organizations using GitHub Enterprise licenses with Azure DevOps, note that GitHub licenses automatically grant Basic access¹, which would need additional filtering in the DevOps access level query.

How can I filter Entra users with active Visual Studio Professional/Enterprise licenses

# Install module if needed
Install-Module Microsoft.Graph

# Connect to Microsoft Graph
Connect-MgGraph -Scopes "User.Read.All"

# Get users with any Visual Studio license (adjust SKUs as needed)
$vsUsers = Get-MgUser -All -Property Id,DisplayName,AssignedLicenses | 
    Where-Object {
        $_.AssignedLicenses.SkuId -in @(
            "c5928f49-12ba-48f7-ada3-0d743a3601d5",  # VS Professional (example)
            "ef6b3985-d0ab-41b0-8d82-9a2b5cfe6d1a"   # VS Enterprise (example)
        )
    }

# Export results
$vsUsers | Select DisplayName,UserPrincipalName | Export-CSV "VS_Users.csv"

Get-MgSubscribedSku | Where-Object SkuPartNumber -like "*VISUAL_STUDIO*"

AuditLogs
| where OperationName in ("Add license to user", "Update license for user")
| extend LicenseSKU = tostring(parse_json(tostring(TargetResources[^2_0].modifiedProperties[^2_1].newValue))[^2_0].SkuPartNumber)
| where LicenseSKU has_any ("VISIOPRO", "VISUAL_STUDIO_ENTERPRISE")  // Adjust SKUs
| distinct 
    UserPrincipalName=tostring(TargetResources[^2_0].userPrincipalName), 
    LicenseSKU,
    AssignmentTime=TimeGenerated

   user.assignedPlans -any (assignedPlan.servicePlanId -eq "YOUR_VS_SERVICE_PLAN_ID")

   Get-MgSubscribedSku | Where SkuPartNumber -eq "VISUAL_STUDIO_ENTERPRISE" | 
     Select -ExpandProperty ServicePlans

https://manage.visualstudio.com

Here’s how to filter Entra users with active Visual Studio licenses using PowerShell and KQL:

PowerShell Method

Note: Actual SKU IDs vary by tenant. Find yours via:

KQL Method

Key Considerations

1. SKU Identification Use Get-MgSubscribedSku (PowerShell) or check Microsoft 365 Admin Center > Billing > Licenses for exact Visual Studio SKU names/IDs.
2. Assignment vs Activation The above methods show license assignments. Actual Visual Studio activation requires additional telemetry from:
   • Visual Studio Usage Reports
   • Azure AD Sign-in Logs (filter AppId: 4e2505d7-1e49-4806-9c46-87a5d3d4f615 for VS IDE logins)
3. Dynamic Groups For automatic filtering, create an Entra ID dynamic group with a rule like: Find service plan IDs via: —

Data Validation

Confirm results against the Visual Studio Subscriptions Admin Portal: Users assigned via Entra groups will appear under “Group Management” with their subscription level⁴. — For Azure DevOps access levels tied to these licenses, users automatically get appropriate access when licenses are properly assigned via Entra ID groups⁵⁴. Use AzureDiagnostics with DevOps audit logging to verify actual access levels.

How can I identify the plan level of each user in Azure DevOps using KQL

// SAMPLE STRUCTURE AFTER INGESTING EXPORTED CSV
let DevOpsUsers = datatable(UserPrincipalName:string, AccessLevel:string)
[
    "[email protected]", "Basic",
    "[email protected]", "Stakeholder",
    "[email protected]", "VS Enterprise"
];

AzureDiagnostics
| where ResourceType == "AZUREDEVOPS"
| where OperationName == "User access changed"
| extend UserPrincipalName = tostring(Properties.UserPrincipalName),
         AccessLevel = tostring(Properties.AccessLevel)
| distinct UserPrincipalName, AccessLevel

$org = "your-org-name"
$pat = "your-pat-token"
$base64Auth = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(":$pat"))

$uri = "https://vsaex.dev.azure.com/$org/_apis/userentitlements?api-version=6.1-preview.3"
$response = Invoke-RestMethod -Uri $uri -Method Get -Headers @{Authorization=("Basic {0}" -f $base64Auth)}

$users = $response.value | Select-Object @{n='User';e={$_.user.principalName}}, @{n='AccessLevel';e={$_.accessLevel.accountLicenseType}}

   // Typical access levels
   let AccessLevels = datatype(string) [
       "Basic",
       "Basic + Test Plans",
       "Stakeholder",
       "VS Enterprise"
   ];

let DevOpsUsers = ...; // From ingested data
let EntraUsers = ...;  // Your existing Entra license query

DevOpsUsers
| join kind=inner EntraUsers on UserPrincipalName
| project UserPrincipalName, AccessLevel, LicenseSKU

az devops user list --org https://dev.azure.com/yourorg --output table

To identify Azure DevOps user access levels using KQL, you’ll need to combine Azure DevOps audit logs with KQL capabilities. However, there are limitations to consider, as Azure DevOps access levels aren’t natively available in standard KQL tables without custom logging configurations. Here’s the most effective approach using available tools:

1. Export Azure DevOps Access Levels

First, export user access levels through Azure DevOps’ native functionality (as shown in search results⁶):

1. Navigate to Organization Settings > Users > Export Users
2. This CSV contains User Email and Access Level columns

2. Ingest into Log Analytics

If you need KQL analysis:

3. Query with KQL (If Logs Exist)

For organizations streaming DevOps audit logs to Log Analytics:

4. Alternative: REST API with PowerShell

When KQL isn’t feasible, use the Azure DevOps REST API⁷:

Critical Implementation Notes

1. Data Requirements:
   • Azure DevOps audit streaming to Log Analytics requires premium features
   • Historical data retention must be explicitly configured
   • Service principal access needs separate handling⁸
2. Access Level Types:
3. Combining with Entra ID Data: For organizations without audit log streaming, you must:
4. Use the exported CSV as your source of truth
5. Schedule regular exports via Azure DevOps CLI: Key Limitations:

• Native KQL access requires custom log ingestion
• Access level changes don’t always appear in real-time logs
• Multi-organization users require special billing consideration⁸

For any given Visual Studio typed Azure subscription attached to my Entra tenant, how do I find out which Entra user’s VS subscription it exists because of?

AzureCostManagement
| where ResourceId contains "/subscriptions/YOUR_VS_SUBSCRIPTION_ID"
| extend UserPrincipalName = tostring(Tags.OwnerEmail)  // Requires tagging strategy
| summarize TotalSpent=sum(todouble(Properties.PretaxCost)) by UserPrincipalName

AuditLogs
| where OperationName in ("Add license to user", "Activate service plan")
| where TargetResources.any(t, t.modifiedProperties.any(mp, mp.displayName == "LicenseAssignmentList"))
| extend VSSubscription = tostring(parse_json(tostring(TargetResources[^4_0].modifiedProperties[^4_1].newValue))[^4_0].SkuPartNumber)
| where VSSubscription has_any ("VISIOPRO", "VS_ENTERPRISE")
| project 
    UserPrincipalName=tostring(TargetResources[^4_0].userPrincipalName), 
    AssignmentTime=TimeGenerated,
    VSSubscription

# Get all users with Visual Studio licenses
Connect-MgGraph -Scopes "User.Read.All"
$vsUsers = Get-MgUser -All -Property AssignedLicenses | 
    Where-Object { $_.AssignedLicenses.SkuId -in @("VISIOPRO_SKU_ID", "VS_ENTERPRISE_SKU_ID") }

# Export for manual comparison with Azure subscription owners
$vsUsers | Select UserPrincipalName | Export-CSV "VS_Users.csv"

   az policy definition create --name 'require-owner-tag' \
     --rules 'https://raw.githubusercontent.com/Azure/azure-policy/master/samples/Tags/enforce-tag-rg.json' \
     --params '{"tagName": {"value": "OwnerEmail"}}'

az role assignment list --scope /subscriptions/YOUR_SUB_ID --query "[?roleDefinitionName=='Owner'].principalName"

To determine which Entra user’s Visual Studio subscription is associated with a specific Azure subscription, follow these steps:

1. Manual Verification via Visual Studio Subscriptions Admin Portal

1. Navigate to https://manage.visualstudio.com/subscribers
2. Search for users with assigned Visual Studio subscriptions (Professional/Enterprise)
3. Check subscription details:
   • Each user’s subscription includes Azure credits

   • The Azure subscription ID is not directly listed here but is linked to the user’s Microsoft account/Entra ID

2. Azure Cost Analysis (Indirect Method)

For Azure subscriptions using Visual Studio credits: Note: Requires prior resource tagging to map costs to users⁹. —

3. Entra ID Audit Log Correlation

Cross-reference license assignments with Azure subscription creation times: Compare timestamps with Azure subscription creation logs. —

4. Direct API Query (Advanced)

Use Microsoft Graph API to find licensed users and cross-reference with Azure subscriptions:

Key Challenges

1. No Direct Mapping: Azure subscriptions don’t inherently track which Visual Studio license provisioned them
2. Shared Credentials: If users share login credentials for Azure, attribution becomes impossible without tagging

3. Historical Data: Older subscriptions may lack proper audit trails

Recommended Solution

1. Implement Mandatory Tagging:
2. Use Azure Cost Management with the CreatedBy field (if available)

3. Leverage Azure Policy to enforce owner email tagging on all new resources

Alternative for Existing Subscriptions

Contact Azure Support with:

• Azure Subscription ID
• Timeframe of subscription creation Request correlation with Visual Studio license activation logs (available to Microsoft internally). —

What other Visual Studio license benefits might I want to see if licenseholders are actually using?

AzureCostManagement | join kind=inner AuditLogs on $left.UserPrincipalName == $right.TargetResources[^5_0].userPrincipalName

Here’s a breakdown of key Visual Studio license benefits to monitor for actual usage, based on available subscription features:

Core Benefits to Audit

1. Monthly Azure Credits
   • Check: Azure consumption patterns in the associated subscription
   • Tool: Azure Cost Management + Billing⁹¹⁰
   • Why: Credits expire monthly - unused credits indicate wasted value
2. Azure Dev/Test Pricing
   • Verify: Resources deployed using DevTest Labs or special pricing tiers
   • Method: Azure Policy to enforce DevTest deployment models¹¹[^6]
3. Training Platforms
   • Track: Activation status of:
     • Pluralsight (6-month subscription)
     • LinkedIn Learning (6-month premium)
     • DataCamp (3-month access)
     • New: Dometrain Pro \& DevPass Business⁹
   • Method: Check redemption status via [My Visual Studio Benefits Portal]⁹[^5]
4. Software Licenses
   • Monitor: Downloads of:
     • Visual Studio IDE versions
     • Windows/Office development tools
     • Partner tools (WhiteSource Bolt, Parasoft)¹¹[^4]
   • Data Source: My.visualstudio.com download logs[^5]
5. Azure DevOps Features
   • Confirm: Usage of:
     • Basic + Test Plans access
     • Advanced pipeline capabilities
     • Artifacts storage¹¹[^4]
   • Audit: Azure DevOps audit logs[^previous]
6. Event Discounts
   • Track: Redemptions for:
     • Visual Studio LIVE! conferences
     • Microsoft Build/Inspire discounts⁹

   • Method: Cross-reference event registrations with subscriber list

New Benefits (2024)⁹

1. Dometrain Pro Access
   • 50% discount on 380+ hours of technical courses
   • Validate via unique subscription codes
2. DevPass Business Edition
   • 50% off Tim Corey’s developer training
   • Requires manual activation through benefits portal
3. Enhanced Conference Discounts
   • Exclusive pricing for AI/cloud-focused events

   • Requires subscriber email verification

Enterprise-Specific Features[^3][^4]

1. Advanced Security Tools
   • Microsoft Threat Modeling
   • Code scanning integrations
2. Architecture Validation
   • Layer diagram usage
   • Dependency tracking
3. Premium Support
   • 4 incidents/year for Enterprise subscribers

   • Ticket history analysis

GitHub Enterprise Integration¹¹

For subscriptions with GitHub Enterprise:

• Audit: Repository creation rates
• Check: Advanced security usage

• Monitor: Actions minutes consumption

Implementation Tools

• PowerShell Scripts: Check license assignments and benefit status
• KQL Queries: Cross-reference Azure activity logs with licensed users
• API Integration: Microsoft Graph API for license inventory[^previous]

Pro Tip: Create an Azure Workbook combining: To correlate spending patterns with license assignment timelines.