Hunting query which works on the Portal but not on Powershell

[dmarquesgn]

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

[#2]

@dmarquesgn If I read https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/kql-quick-reference correctly,

join

Merges the rows of two tables to form a new table by matching values of the specified column(s) from each table. Supports a full range of join types: flouter, inner, innerunique, leftanti, leftantisemi, leftouter, leftsemi, rightanti, rightantisemi, rightouter, rightsemi

LeftTable | join [JoinParameters] ( RightTable ) on Attributes

Shouldn't it be like this? 

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

(Not an expert in this ;) )

[#3]

@Harm_Veenstra 

Thanks for your reply. I've tried that as well, but still get a (400) Bad Request.

So something is still not being parsed correctly for Defender.

[#4]

Hi @dmarquesgn 

Do you have the Permissions on the API and send the Bearer Token in the Header?

Maybe this Article helps you on your way...

https://blog.icewolf.ch/archive/2023/04/21/microsoft-365-defender-advanced-hunting-with-powershell/

Regards

Andres