Forum Discussion
dmarquesgn
Dec 06, 2023Iron Contributor
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
- Andres-BohrenSteel Contributor
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
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 😉 )
- dmarquesgnIron Contributor
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.