topic issues with defender API calls in Power Query (odata) in Developer

issues with defender API calls in Power Query (odata)

Anonymous — Fri, 20 Aug 2021 10:38:07 GMT

In Power Query:

Why does the first query work and the second query does not? If the field types are integer or Boolean the filter fails. Why?

Query 1 (works) :

Query = "Vulnerabilities?$filter=severity eq 'Critical' and cvssV3 eq 9.8",

Source = OData.Feed(https://api.securitycenter.microsoft.com/api/ & Query, null, [Implementation="2.0", MoreColumns=true])

Query 2 (fails) :

Query = "Vulnerabilities?$filter=severity eq 'Critical' and exposedMachines gt 0",

Source = OData.Feed(https://api.securitycenter.microsoft.com/api/ & Query, null, [Implementation="2.0", MoreColumns=true])

Re: issues with defender API calls in Power Query (odata)

lbendlin — Sun, 22 Aug 2021 00:37:01 GMT

Is exposedMachines an actual column in your source data or are you trying to run an aggregation of sorts?

Re: issues with defender API calls in Power Query (odata)

Anonymous — Tue, 24 Aug 2021 06:42:34 GMT

Hi Anonymous ,

The field type applied to the filter can be a number, datetime, or string and the type used must match the type set in the dataset. However, Boolean type is not included in them. Could you please change Query 2 to the following form: equal to a fixed value already in the dataset and check if it works after updated?

Query = "Vulnerabilities?$filter=severity eq 'Critical' and exposedMachines eq [static value]",

Best Regards