Jan 03 2023 10:01 AM
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
Jan 03 2023 10:30 AM
Jan 04 2023 01:16 AM
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:
Jan 04 2023 07:16 AM
Jan 04 2023 08:31 AM
Yes, I always check first on the data on 365 Defender interface. Screenshot below confirms that.
So I guess it's something that is not being correctly parsed on the query.
Jan 04 2023 08:37 AM
Jan 05 2023 03:06 AM
SolutionThanks 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.
Jan 05 2023 05:04 AM
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 ".
Jan 05 2023 03:06 AM
SolutionThanks 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.