Forum Discussion

dmarquesgn's avatar
dmarquesgn
Iron Contributor
Dec 06, 2023

Hunting query which works on the Portal but not on Powershell

Hi,

I have a need to extract all my vulnerabilities from Defender TVM and export do JSON or CSV. I've built an hunting query which gives at least the results consistently. I've got more than 200k vulnerabilities.

This is the simple KQL for it:

 

DeviceTvmSoftwareVulnerabilities
| join kind=leftouter DeviceTvmSoftwareVulnerabilitiesKB on CveId

 

 

On Powershell, I extract this info like this:

 

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

 

I always get a 400 error, bad request.

But if I run this one:

 

$vulnUrl = '{ "query": "DeviceTvmSoftwareVulnerabilities | join (DeviceTvmSoftwareVulnerabilitiesKB) on CveId" }'

 

It works fine. So I guess something is not right with the "join kind=leftouter".

Anyone has faced a similar issues or knows what's wrong with this query?

 

Thanks

Resources