Vulnerability Statistics Sentinel Workbook
Vulnerability Statistics Logic App Sentinel Workbook
Query
{
"version": "Notebook/1.0",
"items": [
{
"type": 9,
"content": {
"version": "KqlParameterItem/1.0",
"parameters": [
{
"id": "timerange-param",
"version": "KqlParameterItem/1.0",
"name": "TimeRange",
"label": "Time Range",
"type": 4,
"isRequired": true,
"value": {
"durationMs": 15552000000
},
"typeSettings": {
"selectableValues": [
{
"durationMs": 7776000000
},
{
"durationMs": 15552000000
},
{
"durationMs": 31536000000
},
{
"durationMs": 63072000000
}
],
"allowCustom": false
}
}
],
"style": "pills",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
"name": "parameters"
},
{
"type": 1,
"content": {
"json": "# TVM Vulnerability Stats — Aktuelle Übersicht\n---"
},
"name": "title"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "Vulnerabilitystatistics_CL\n| where TimeGenerated > ago(30d)\n| where DeviceCategory in (\"Windows Clients\", \"macOS\", \"Windows Server\", \"Linux Server\", \"Network Components\", \"Others / Unknown\")\n| project TimeGenerated, DeviceCategory, Vulnerability_Count, Unique_CVEs, Unique_CVEs_With_Exploit, Affected_Devices, Avg_CVSS_Score, Avg_Vulnerabilities_Per_Device\n| sort by DeviceCategory asc, TimeGenerated desc\n| summarize arg_max(TimeGenerated, *) by DeviceCategory\n| project DeviceCategory, Vulnerability_Count, Unique_CVEs, Unique_CVEs_With_Exploit, Affected_Devices, Avg_CVSS_Score, Avg_Vulnerabilities_Per_Device\n| sort by Vulnerability_Count desc",
"size": 3,
"title": "Current Values by Device Category",
"timeContext": {
"durationMs": 2592000000
},
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"visualization": "tiles",
"tileSettings": {
"titleContent": {
"columnMatch": "DeviceCategory",
"formatter": 1
},
"leftContent": {
"columnMatch": "Vulnerability_Count",
"formatter": 12,
"formatOptions": {
"palette": "redGreen",
"aggregation": "Sum"
},
"numberFormat": {
"unit": 17,
"options": {
"style": "decimal",
"maximumFractionDigits": 0
}
}
},
"secondaryContent": {
"columnMatch": "Avg_CVSS_Score",
"formatter": 1,
"formatOptions": {
"aggregation": "Avg"
},
"numberFormat": {
"unit": 17,
"options": {
"style": "decimal",
"maximumFractionDigits": 2
}
}
},
"showBorder": true,
"size": "auto"
}
},
"name": "tiles-overview"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "Vulnerabilitystatistics_CL\n| where TimeGenerated > ago(30d)\n| where DeviceCategory in (\"Windows Clients\", \"macOS\", \"Windows Server\", \"Linux Server\", \"Network Components\", \"Others / Unknown\")\n| project TimeGenerated, DeviceCategory, Vulnerability_Count, Unique_CVEs, Unique_CVEs_With_Exploit, Affected_Devices, Avg_CVSS_Score, Avg_Vulnerabilities_Per_Device\n| summarize arg_max(TimeGenerated, *) by DeviceCategory\n| project ['Category'] = DeviceCategory, ['Vulnerabilities'] = Vulnerability_Count, ['Unique CVEs'] = Unique_CVEs, ['CVEs with Exploit'] = Unique_CVEs_With_Exploit, ['Affected Devices'] = Affected_Devices, ['Avg CVSS Score'] = Avg_CVSS_Score, ['Avg Vulns / Device'] = Avg_Vulnerabilities_Per_Device\n| sort by Vulnerabilities desc",
"size": 1,
"title": "Detail Table — Current Snapshot",
"timeContext": {
"durationMs": 604800000
},
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"visualization": "table",
"gridSettings": {
"formatters": [
{
"columnMatch": "Vulnerabilities",
"formatter": 18,
"formatOptions": {
"thresholdsOptions": "colors",
"thresholdsGrid": [
{
"operator": ">=",
"thresholdValue": "100000",
"representation": "red",
"text": "{0}"
},
{
"operator": ">=",
"thresholdValue": "10000",
"representation": "orange",
"text": "{0}"
},
{
"operator": "Default",
"thresholdValue": null,
"representation": "yellow",
"text": "{0}"
}
]
}
},
{
"columnMatch": "CVEs with Exploit",
"formatter": 8,
"formatOptions": {
"palette": "orange",
"aggregation": "Sum"
}
},
{
"columnMatch": "Affected Devices",
"formatter": 8,
"formatOptions": {
"palette": "blue",
"aggregation": "Sum"
}
},
{
"columnMatch": "Avg CVSS Score",
"formatter": 18,
"formatOptions": {
"thresholdsOptions": "colors",
"thresholdsGrid": [
{
"operator": ">=",
"thresholdValue": "9",
"representation": "red",
"text": "{0}"
},
{
"operator": ">=",
"thresholdValue": "5",
"representation": "orange",
"text": "{0}"
},
{
"operator": "Default",
"thresholdValue": null,
"representation": "yellow",
"text": "{0}"
}
]
}
}
]
},
"sortBy": []
},
"name": "table-detail"
},
{
"type": 1,
"content": {
"json": "---\n## Vulnerability Trend by Device Category"
},
"name": "section-trend"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "Vulnerabilitystatistics_CL\n| where TimeGenerated {TimeRange}\n| extend DeviceCategory = trim(@\" \", tostring(DeviceCategory))\n| where DeviceCategory in (\"Windows Clients\", \"macOS\", \"Windows Server\", \"Linux Server\", \"Network Components\", \"Others / Unknown\")\n| summarize Vulnerability_Count=max(toint(Vulnerability_Count)) by TimeGenerated, DeviceCategory\n| render timechart with (xcolumn=TimeGenerated, ycolumns=Vulnerability_Count, series=DeviceCategory)",
"size": 0,
"title": "Vulnerability Count — Trend Over Time",
"timeContextFromParameter": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"visualization": "timechart"
},
"name": "linechart-vulns"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "Vulnerabilitystatistics_CL\n| where TimeGenerated {TimeRange}\n| extend DeviceCategory = trim(@\" \", tostring(DeviceCategory))\n| where DeviceCategory in (\"Windows Clients\", \"macOS\", \"Windows Server\", \"Linux Server\", \"Network Components\", \"Others / Unknown\")\n| summarize Avg_CVSS_Score=avg(todouble(Avg_CVSS_Score)) by TimeGenerated, DeviceCategory\n| render timechart with (xcolumn=TimeGenerated, ycolumns=Avg_CVSS_Score, series=DeviceCategory)",
"size": 0,
"aggregation": 3,
"title": "Average CVSS Score — Trend Over Time",
"timeContextFromParameter": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"visualization": "timechart"
},
"name": "linechart-cvss"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "Vulnerabilitystatistics_CL\n| where TimeGenerated {TimeRange}\n| extend DeviceCategory = trim(@\" \", tostring(DeviceCategory))\n| where DeviceCategory in (\"Windows Clients\", \"macOS\", \"Windows Server\", \"Linux Server\", \"Network Components\", \"Others / Unknown\")\n| summarize Unique_CVEs_With_Exploit=max(toint(Unique_CVEs_With_Exploit)) by TimeGenerated, DeviceCategory\n| render timechart with (xcolumn=TimeGenerated, ycolumns=Unique_CVEs_With_Exploit, series=DeviceCategory)",
"size": 0,
"title": "CVEs with Available Exploit — Trend Over Time",
"timeContextFromParameter": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"visualization": "timechart"
},
"name": "linechart-exploits"
}
],
"fallbackResourceIds": [
"/subscriptions/378670e9-7fc1-464c-ad69-22d320c5417a/resourcegroups/p-rgr-mgmtsecurity-01/providers/microsoft.operationalinsights/workspaces/p-loa-securityhuntingenrichments-02"
],
"fromTemplateId": "sentinel-UserWorkbook",
"$schema": "https://github.com/Microsoft/Application-Insights-Workbooks/blob/master/schema/workbook.json"
}About this query
Explanation
This query and its associated components are part of a system designed to provide a weekly overview of vulnerabilities across various device categories using Microsoft Defender data. Here's a simplified breakdown:
-
Logic App:
- Schedule: Runs every week.
- Data Collection: Executes an advanced query using Microsoft Graph to gather vulnerability data from devices categorized as Windows servers, Windows clients, Linux, macOS, and network components.
- Metrics: Collects data on total vulnerabilities, unique CVEs (Common Vulnerabilities and Exposures), exploitable CVEs, affected devices, and average CVSS (Common Vulnerability Scoring System) scores.
- Data Ingestion: Sends the gathered data to Azure Monitor using the Log Ingestion API.
- Success Check: Confirms successful data ingestion by checking for a 2xx HTTP response status. If successful, it logs the success; otherwise, it logs the error and marks the run as failed.
- Authentication: Uses Managed Identity for secure access without embedded credentials.
-
Sentinel Workbook:
- Overview: Provides a consolidated view of vulnerability exposure across device categories.
- KPI Tiles and Detail Table: Displays the latest snapshot of key metrics for each category, such as total vulnerabilities, unique CVEs, exploitable CVEs, affected devices, average CVSS score, and average vulnerabilities per device.
- Trend Analysis: Visualizes trends over time for vulnerability volume, average severity, and exploitable CVEs, helping to identify risk areas and prioritize remediation efforts.
Overall, this system automates the collection, analysis, and visualization of vulnerability data to help organizations monitor and manage their security posture effectively.
Details
Benjamin Zulliger
Released: July 13, 2026
Tables
DeviceInfoDeviceTvmSoftwareVulnerabilitiesKBDeviceTvmSoftwareVulnerabilitiesVulnerabilitystatistics_CL
Keywords
Vulnerability Statistics Sentinel WorkbookLogic AppSentinel WorkbookMicrosoft DefenderAdvanced HuntingMicrosoft GraphDevicesWindows ServersWindows ClientsLinuxmacOSNetwork ComponentsSecurity MetricsVulnerabilitiesCVEsCVSSAzure MonitorLog Ingestion APIManaged IdentityTVMKPITrendRiskRemediationDevice CategoriesVulnerability ExposureSnapshotTrend SectionComparisonPrioritization
Operators
letsummarizearg_maxextendcasehas_anystartswithprojectjoinkindondcountdcountifroundtorealnowsortdescwhereinagoascbymaxtointrendertimecharttrimtostringavgtodouble