Forum Discussion

dmarquesgn's avatar
dmarquesgn
Iron Contributor
Jan 03, 2023

Kusto Query to extract the number of exploitable vulnerabilities

Hi,

 

I need to build up a Kusto Query to extract the total number of Exploitable Vulnerabilities. The vulnerabilities are on the DeviceTvmSoftwareVulnerabilities table with their CVEID and the Exploitable vulnerabilities are on the DeviceTvmSoftwareVulnerabilitiesKB table where we have a field named "IsExploitAvailable".
I need to join the results of these 2 tables by the CVEID and "IsExploitAvailable" equals to 1, and then count, but I'm kind of new to Kusto Query.
Can anyone guide me on how to build a query like this?

Thanks

  • Rod_Trent 

    Thanks for the reference. But I found out what was the issue. Now it works as expected, like this:

    $vulnUrl = '{ "query": "DeviceTvmSoftwareVulnerabilities | join (DeviceTvmSoftwareVulnerabilitiesKB) on CveId | where IsExploitable == 1 | count" }'
    $vulnUrlUri = "https://graph.microsoft.com/beta/security/runHuntingQuery"
    $vulnResponse = Invoke-WebRequest -Method Post -Uri $vulnUrlUri -Body $vulnUrl -Headers $headers -ErrorAction Stop

    The difference was the " surrounding the 1 value. So I guess it doesn't deal well with multiple " on the variable.

     

  • Just based on your description:

    DeviceTvmSoftwareVulnerabilitiesKB
    | join (DeviceTvmSoftwareVulnerabilities) on CveId
    | where IsExploitAvailable == "1"
    | count
    • dmarquesgn's avatar
      dmarquesgn
      Iron Contributor

      Rod_Trent 

      Hi,

      Thanks, that was about what I need, but reversing the tables:

      DeviceTvmSoftwareVulnerabilities
      | join (DeviceTvmSoftwareVulnerabilitiesKB) on CveId
      | where IsExploitAvailable == "1"
      | count

       

      One other question, just to give a next step. The goal is to use this programatically to extract this value into a set of values for reporting. For that I have a Powershell to run this queries and extract the values to be used on a report. But while some other queries are working fine, this one is outputing "Bad Request" error.

      My script is more or less this (the part that matters for the case):

      $vulnUrl = '{ "query": "DeviceTvmSoftwareVulnerabilities | join (DeviceTvmSoftwareVulnerabilitiesKB) on CveId | where IsExploitable == "1" | count" }'
      $vulnUrlUri = "https://graph.microsoft.com/beta/security/runHuntingQuery"
      $vulnResponse = Invoke-WebRequest -Method Post -Uri $vulnUrlUri -Body $vulnUrl -Headers $headers -ErrorAction Stop

      And the error is: Invoke-WebRequest : The remote server returned an error: (400) Bad Request.

      But for example, this ones work fine:

      $vulnUrl = '{ "query": "DeviceTvmSoftwareVulnerabilities | distinct CveId | count" }'
      $vulnUrl = '{ "query": "DeviceTvmSoftwareVulnerabilities | summarize count() by  VulnerabilitySeverityLevel" }'
       
      Do you have an idea why?
      Thanks again!
      • Rod_Trent's avatar
        Rod_Trent
        Icon for Microsoft rankMicrosoft
        Have you manually queried against DeviceTvmSoftwareVulnerabilitiesKB in your environment to see if it actually contains data?

Resources