SOLVED

Kusto Query to extract the number of exploitable vulnerabilities

Frequent Contributor

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

7 Replies
Just based on your description:

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

@rodtrent 

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!
Have you manually queried against DeviceTvmSoftwareVulnerabilitiesKB in your environment to see if it actually contains data?

@rodtrent 

Yes, I always check first on the data on 365 Defender interface. Screenshot below confirms that.

Screenshot_1.png

So I guess it's something that is not being correctly parsed on the query.

best response confirmed by rodtrent (Microsoft)
Solution

@rodtrent 

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.

 

@dmarquesgn 

This now leads me to an issue. I've got other query which I would like to run, which is this:

AlertInfo 
    | where Title == "Email reported by user as malware or phish"
    | where Timestamp > ago(30d)
    | count 

And in this case I cannot remove the " nor replace it on the variable by '. 
So I don't know if there's any way to escape the ".