Summary

Microsoft Defender for Endpoint tracks CVEs in your environment, but I get asked a lot do we have a way to track what CVEs are new and/or resolved? Answer is out of the box...no, however you do have the ability to pull the data from Microsoft Graph.

I was able to get a free Azure SQL Database but since it's my lab and not a lot of endpoints, I didn't need anything to large. You could easily change the source location in the Power BI to a on-prem SQL Database or other cloud database solutions.

Screenshots of the finished setup: Overview:

One of the CVE Details Tab:

CVE-EvidenceBeta:

Setup

1. Create an Azure SQL Database, I created one called mdvmdatabase/MDVM

2. Create a table in that database. You can create the table using a variety of different tools. I just used the browser in the portal

Code for the table creation:

CREATE TABLE dbo.MDVMArchive
(
    [Date] DATETIME2 NOT NULL,
    DeviceId NVARCHAR(255) NULL,
    DeviceName NVARCHAR(255) NULL,
    OSPlatform NVARCHAR(255) NULL,
    OSVersion NVARCHAR(255) NULL,
    SoftwareVendor NVARCHAR(255) NULL,
    SoftwareName NVARCHAR(255) NULL,
    SoftwareVersion NVARCHAR(255) NULL,
    CVEId NVARCHAR(255) NULL,
    VulnerabilitySeverityLevel NVARCHAR(255) NULL,
    CVSSScore NVARCHAR(255) NULL,
    PublishedDate DATETIME2 NULL
);

3. Create an app registration in Entra.

4. Copy the Client ID, TenantID for the PowerShell script in the next step.

5. Create a client secret

6. Set the API permissions: you need readall on applicaiton for ThreatHunting.Read.All

7. Now we need to create an Automation Account

8. Turn on System Assigned Identity

9. Create Runebook

10. Click on Edit > Edit in portal

11. Paste the PowerShell script into the Window.

• Note: Remember to change the Variables at the top of the script

# =========================================================
# VARIABLES (AUTOMATION VARIABLES)
# =========================================================

$TenantId     = "11111111-1111-1111-1111-111111111111"
$ClientId     = "11111111-1111-1111-1111-111111111111"
$ClientSecret = "Client Secret Value"
$SqlServer    = "Databasename.database.windows.net"
$SqlDatabase  = "MDVM"

$TableName = "MDVMArchive"

# =========================================================
# FUNCTIONS
# =========================================================

function Get-SqlTypeFromValue {
    param ($Value)

    if ($null -eq $Value) { return "nvarchar(max)" }

    switch ($Value.GetType().Name) {
        "DateTime" { "datetime2" }
        "Int32"    { "int" }
        "Int64"    { "bigint" }
        "Double"   { "float" }
        "Boolean"  { "bit" }
        default    { "nvarchar(max)" }
    }
}

function New-CreateTableStatement {
    param (
        [string]$TableName,
        [object]$SampleRow
    )

    $Columns = foreach ($Property in $SampleRow.PSObject.Properties) {
        $SqlType = Get-SqlTypeFromValue $Property.Value
        "[$($Property.Name)] $SqlType NULL"
    }

@"
IF NOT EXISTS (
    SELECT 1 FROM sys.tables WHERE name = '$TableName'
)
BEGIN
    CREATE TABLE [$TableName] (
        $(($Columns -join ",`n        "))
    )
END
"@
}

function New-InsertStatement {
    param (
        [string]$TableName,
        [object]$SampleRow
    )

    $Columns     = $SampleRow.PSObject.Properties.Name
    $ColumnList  = ($Columns | ForEach-Object { "[$_]" }) -join ", "
    $ParamList   = ($Columns | ForEach-Object { "@$_" }) -join ", "

@"
INSERT INTO [$TableName] ($ColumnList)
VALUES ($ParamList)
"@
}

function Invoke-SqlNonQuery {
    param (
        [string]$Server,
        [string]$Database,
        [string]$Query,
        [hashtable]$Parameters
    )

    # -------------------------
    # Use Managed Identity to get SQL access token
    # -------------------------
    Connect-AzAccount -Identity

    $SqlToken = (Get-AzAccessToken -ResourceUrl "https://database.windows.net/").Token

    # -------------------------
    # Connect to Azure SQL
    # -------------------------
    $Conn = New-Object System.Data.SqlClient.SqlConnection
    $Conn.ConnectionString = "Server=$Server;Database=$Database"
    $Conn.AccessToken = $SqlToken
    $Conn.Open()

    $Cmd = $Conn.CreateCommand()
    $Cmd.CommandText = $Query

    if ($Parameters) {
        foreach ($Key in $Parameters.Keys) {
            $Param = $Cmd.Parameters.Add("@$Key", [System.Data.SqlDbType]::NVarChar)
            $Param.Value = $Parameters[$Key] ?? [DBNull]::Value
        }
    }

    $Cmd.ExecuteNonQuery()
    $Conn.Close()
}

# =========================================================
# MICROSOFT GRAPH AUTH (CLIENT SECRET)
# =========================================================
$TokenBody = @{
    client_id     = $ClientId
    scope         = "https://graph.microsoft.com/.default"
    client_secret = $ClientSecret
    grant_type    = "client_credentials"
}

$TokenResponse = Invoke-RestMethod `
    -Method POST `
    -Uri "https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token" `
    -Body $TokenBody `
    -ContentType "application/x-www-form-urlencoded"

$AccessToken = $TokenResponse.access_token

# =========================================================
# ADVANCED HUNTING QUERY
# =========================================================
$HuntingQuery = @"
DeviceTvmSoftwareVulnerabilities 
| join kind=inner ( DeviceTvmSoftwareVulnerabilitiesKB) on CveId, VulnerabilitySeverityLevel 
| distinct Date = format_datetime(now(), 'MM/dd/yyyy'),DeviceId, DeviceName, OSPlatform, OSVersion, SoftwareVendor, SoftwareName, SoftwareVersion, CveId, VulnerabilitySeverityLevel, CvssScore, PublishedDate
"@

$HuntingBody = @{ query = $HuntingQuery } | ConvertTo-Json -Depth 5

$HuntingResponse = Invoke-RestMethod `
    -Method POST `
    -Uri "https://graph.microsoft.com/v1.0/security/runHuntingQuery" `
    -Headers @{ Authorization = "Bearer $AccessToken" } `
    -Body $HuntingBody `
    -ContentType "application/json"

$Results = $HuntingResponse.results

if (-not $Results -or $Results.Count -eq 0) {
    Write-Output "No hunting results returned."
    return
}

# =========================================================
# AUTO-CREATE TABLE
# =========================================================
$SampleRow = $Results | Select-Object -First 1

$CreateTableQuery = New-CreateTableStatement `
    -TableName $TableName `
    -SampleRow $SampleRow

Invoke-SqlNonQuery `
    -Server $SqlServer `
    -Database $SqlDatabase `
    -Query $CreateTableQuery

# =========================================================
# INSERT RESULTS
# =========================================================
$InsertQuery = New-InsertStatement `
    -TableName $TableName `
    -SampleRow $SampleRow

foreach ($Row in $Results) {

    $Params = @{}
    foreach ($Prop in $Row.PSObject.Properties) {
        $Params[$Prop.Name] = $Prop.Value
    }

    Invoke-SqlNonQuery `
        -Server $SqlServer `
        -Database $SqlDatabase `
        -Query $InsertQuery `
        -Parameters $Params
}

Write-Output "Inserted $($Results.Count) rows into $TableName"

12. Click a Schedules > Add a schedule

13. Click on Schedule

14. Click on Add a Schedule, then in the blade enter the Name, change the date and time to when you want to start importing the data, change the radio button to recurring, and finally set to the recur to every 1 day.

15. Download the Power BI Template: MDVMDashboard-AzureSQLDB
16. Open MDVMDashboard-AzureSQLDB.pbit
17. Login with your Enterprise account, then when prompted enter the database connection and name we setup in the first few steps. It will also ask for TenantID, ClientID, and Secret from steps 3-5.

18. Click on Connect for the Graph connection, it will be using the client secret supplied in step 17.

19. It will probably ask about privacy levels, I changed mine to organizational.

20. Optional prompt: This will be for if you are not logged into Power BI with an enterprise account. Click Microsoft Account then click Sign In