Advanced Hunting API fails for DeviceTvmSoftwareVulnerabilities table

%3CLINGO-SUB%20id%3D%22lingo-sub-3065226%22%20slang%3D%22en-US%22%3EAdvanced%20Hunting%20API%20fails%20for%20DeviceTvmSoftwareVulnerabilities%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3065226%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20automated%20script%20which%20has%20been%20working%20well%20for%20a%20number%20of%20months.%20During%20December%20I%20started%20getting%20intermittent%20failures%2C%20which%20I%20ignored%2C%20but%20now%20they%20have%20become%20persistent.%20The%20problem%20is%20occurring%20for%20any%20queries%20of%20the%26nbsp%3BDeviceTvmSoftwareVulnerabilities%20table.%20Even%20a%20simple%20one.%20The%20query%20I%20am%20submitting%20through%20the%20API%20work%20fine%20in%20the%20Advanced%20Hunting%20section%20of%20the%20Microsoft%20365%20Defender%20Portal.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20code%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%20%24Body%20%3D%20%5BOrdered%5D%20%40%7B%0A%20%20%20%20%20resource%20%3D%20%22https%3A%2F%2Fapi.securitycenter.microsoft.com%22%0A%20%20%20%20%20client_id%20%3D%20%22%24AppId%22%0A%20%20%20%20%20client_secret%20%3D%20%22%24AppSecret%22%0A%20%20%20%20%20grant_type%20%3D%20'client_credentials'%0A%20%20%20%20%7D%0A%24Response%20%3D%20Invoke-RestMethod%20-Method%20Post%20-Uri%20%24OauthUri%20-Body%20%24Body%20-ErrorAction%20Stop%0A%24AadToken%20%3D%20%24response.access_token%0A%0A%24Url%20%3D%20%22https%3A%2F%2Fapi.securitycenter.microsoft.com%2Fapi%2Fadvancedqueries%2Frun%22%0A%24Headers%20%3D%20%40%7B%0A%20%20%20%20%20%20%20%20'Content-Type'%20%3D%20'application%2Fjson'%0A%20%20%20%20%20%20%20%20Accept%20%3D%20'application%2Fjson'%0A%20%20%20%20%20%20%20%20Authorization%20%3D%20%22Bearer%20%24AadToken%22%0A%20%20%20%20%7D%0A%24Query%20%3D%20%22DeviceTvmSoftwareVulnerabilities%20%7C%20take%201%22%0A%24Body%20%3D%20ConvertTo-Json%20-InputObject%20%40%7B%20'Query'%20%3D%20%24Query%7D%0A%24WebResponse%20%3D%20Invoke-WebRequest%20-Method%20Post%20-Uri%20%24url%20-Headers%20%24headers%20-Body%20%24body%20-ErrorAction%20Stop%20-UseBasicParsing%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThe%20returned%20result%20is%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EAPI%20call%20returned%20an%20error%3A%20%7B%22error%22%3A%7B%22code%22%3A%22BadRequest%22%2C%22message%22%3A%22'project'%20operator%3A%20Failed%20to%20resolve%20scalar%20expression%20named%20'RecommendedSecurityUpdate'.%20Fi%0Ax%20semantic%20errors%20in%20your%20query%22%2C%22target%22%3A%227c97d065-2c2a-4a55-8d63-068d2855813b%22%7D%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EWhich%20is%20strange%2C%20because%20'RecommendedSecurityUpdate'%20is%20the%20name%20of%20one%20of%20the%20returned%20columns.%20If%20I%20run%20the%20exact%20same%20code%20with%20the%20query%20'DeviceTvmSoftwareInventory%20%7C%20take%201'%20it%20works%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHas%20something%20changed%3F%20Is%20this%20an%20issue%20in%20the%20platform%20like%20it%20seems%20to%20be%3F%20If%20so%2C%20how%20do%20I%20get%20support%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have an automated script which has been working well for a number of months. During December I started getting intermittent failures, which I ignored, but now they have become persistent. The problem is occurring for any queries of the DeviceTvmSoftwareVulnerabilities table. Even a simple one. The query I am submitting through the API work fine in the Advanced Hunting section of the Microsoft 365 Defender Portal.

 

Here is the code:

 $Body = [Ordered] @{
	    resource = "https://api.securitycenter.microsoft.com"
	    client_id = "$AppId"
	    client_secret = "$AppSecret"
	    grant_type = 'client_credentials'
    }
$Response = Invoke-RestMethod -Method Post -Uri $OauthUri -Body $Body -ErrorAction Stop
$AadToken = $response.access_token

$Url = "https://api.securitycenter.microsoft.com/api/advancedqueries/run"
$Headers = @{
        'Content-Type' = 'application/json'
        Accept = 'application/json'
        Authorization = "Bearer $AadToken"
    }
$Query = "DeviceTvmSoftwareVulnerabilities | take 1"
$Body = ConvertTo-Json -InputObject @{ 'Query' = $Query}
$WebResponse = Invoke-WebRequest -Method Post -Uri $url -Headers $headers -Body $body -ErrorAction Stop -UseBasicParsing

The returned result is:

API call returned an error: {"error":{"code":"BadRequest","message":"'project' operator: Failed to resolve scalar expression named 'RecommendedSecurityUpdate'. Fi
x semantic errors in your query","target":"7c97d065-2c2a-4a55-8d63-068d2855813b"}}

Which is strange, because 'RecommendedSecurityUpdate' is the name of one of the returned columns. If I run the exact same code with the query 'DeviceTvmSoftwareInventory | take 1' it works fine.

 

Has something changed? Is this an issue in the platform like it seems to be? If so, how do I get support?

4 Replies

I am having the exact same issue with the API call using DeviceTvmSoftwareVulnerabilities

@Jeremy Hagan 

 

I have a number of PowerBI report which are now failing with a (400): Bad Request DataSource.Error: Web.Contents failed to get contents from 'https://api.securitycenter.microsoft.com/api/advancedqueries.

 

The report had been working fine since they were created about 6 months ago and started failing late December.

 

I have recreated the Query with a very basic lookup but anything from DeviceTvmSoftwareVulnerabilities fails.   All other queries to any other Schema works fine and using the DeviceTvmSoftwareVulnerabilities  schema direct from Advanced Hunting works fine so something must have changed with the API.

I've logged a ticket with MS Support and they have escalated internally. Will try to remember to post back here if/when it is resolved.
This appears to be fixed, although MS didn't say they had done anything.

@Jeremy Hagan 

 

I can confirm this is fixed on my end now as well.  So I suspect it was an issue on their end that they have resolved.