topic Re: Power Query Defender Advanced hunting bad request 400 for some tables. in Developer

Power Query Defender Advanced hunting bad request 400 for some tables.

mkjit256 — Mon, 22 Sep 2025 20:58:50 GMT

Hello i am trying to connect to Defender XRD api using a service principal authentication following the example that has been provided in MS documentation. Microsoft Defender for Endpoint APIs connection to Power BI - Microsoft Defender for Endpoint | Microsoft Learn.
I am connecting from a DataFlow Gen2 in Fabric.
For DeviceEvents table:

let AdvancedHuntingQuery = "DeviceEvents | where ActionType contains 'Anti' | limit 20", HuntingUrl = "https://api.securitycenter.microsoft.com/api/advancedqueries", Response = Json.Document(Web.Contents(HuntingUrl, [Query=[key=AdvancedHuntingQuery]])), TypeMap = #table( { "Type", "PowerBiType" }, { { "Double", Double.Type }, { "Int64", Int64.Type }, { "Int32", Int32.Type }, { "Int16", Int16.Type }, { "UInt64", Number.Type }, { "UInt32", Number.Type }, { "UInt16", Number.Type }, { "Byte", Byte.Type }, { "Single", Single.Type }, { "Decimal", Decimal.Type }, { "TimeSpan", Duration.Type }, { "DateTime", DateTimeZone.Type }, { "String", Text.Type }, { "Boolean", Logical.Type }, { "SByte", Logical.Type }, { "Guid", Text.Type } }), Schema = Table.FromRecords(Response[Schema]), TypedSchema = Table.Join(Table.SelectColumns(Schema, {"Name", "Type"}), {"Type"}, TypeMap , {"Type"}), Results = Response[Results], Rows = Table.FromRecords(Results, Schema[Name]), Table = Table.TransformColumnTypes(Rows, Table.ToList(TypedSchema, (c) => {c{0}, c{2}})) in Table

The request suceed to connect with no issues.
For EmailEvents table
However when i try to change the query in the AdvancedHuntingQuery Step:

let AdvancedHuntingQuery = "EmailEvents | limit 20", HuntingUrl = "https://api.securitycenter.microsoft.com/api/advancedqueries", Response = Json.Document(Web.Contents(HuntingUrl, [Query=[key=AdvancedHuntingQuery]])), TypeMap = #table( { "Type", "PowerBiType" }, { { "Double", Double.Type }, { "Int64", Int64.Type }, { "Int32", Int32.Type }, { "Int16", Int16.Type }, { "UInt64", Number.Type }, { "UInt32", Number.Type }, { "UInt16", Number.Type }, { "Byte", Byte.Type }, { "Single", Single.Type }, { "Decimal", Decimal.Type }, { "TimeSpan", Duration.Type }, { "DateTime", DateTimeZone.Type }, { "String", Text.Type }, { "Boolean", Logical.Type }, { "SByte", Logical.Type }, { "Guid", Text.Type } }), Schema = Table.FromRecords(Response[Schema]), TypedSchema = Table.Join(Table.SelectColumns(Schema, {"Name", "Type"}), {"Type"}, TypeMap , {"Type"}), Results = Response[Results], Rows = Table.FromRecords(Results, Schema[Name]), Table = Table.TransformColumnTypes(Rows, Table.ToList(TypedSchema, (c) => {c{0}, c{2}})) in Table

i am getting a Web.Contents failed to get contents from 'https://api.securitycenter.microsoft.com/api/advancedqueries?key=EmailEvents%7C%20limit%2020' (400): Bad Request.

Any help is appreciated in explaining or solving the issue is well appreciated. Thanks

Re: Power Query Defender Advanced hunting bad request 400 for some tables.

v-hashadapu — Tue, 23 Sep 2025 10:47:35 GMT

Hi mkjit256 , Thank you for reaching out to the Microsoft Fabric Community Forum.

DeviceEvents is part of Microsoft Defender for Endpoint (MDE), which uses the endpoint https://api.securitycenter.microsoft.com/api/advancedqueries, while EmailEvents belongs to Microsoft 365 Defender (XDR), which requires the endpoint https://api.security.microsoft.com/api/advancedqueries. If you send an EmailEvents query to the MDE endpoint, you’ll always get a 400 error because that table doesn’t exist there. Point your query at the Microsoft 365 Defender endpoint and ensure your service principal has the corresponding Microsoft 365 Defender API permission in Entra ID.

Advanced Hunting API - Microsoft Defender for Endpoint | Microsoft Learn

Microsoft Defender XDR advanced hunting API - Microsoft Defender XDR | Microsoft Learn

Supported Microsoft Defender for Endpoint APIs - Microsoft Defender for Endpoint | Microsoft Learn

Use the Microsoft Graph security API - Microsoft Graph v1.0 | Microsoft Learn

Re: Power Query Defender Advanced hunting bad request 400 for some tables.

mkjit256 — Tue, 23 Sep 2025 19:08:34 GMT

Thanks for your help,

you were right that the API endpoint is not the correct one, but it wasn't a simple switch, cause the second url apperantly needs to be passed a token, which needs to be called from power query itself:

Scope = "https://api.security.microsoft.com/.default", // Construct the body as a record (Power Query will handle encoding) TokenRequestBody = [ grant_type = "client_credentials", client_id = ClientId, client_secret = ClientSecret, scope = Scope ], // Convert the record to form-urlencoded text FormBody = Text.ToBinary(Uri.BuildQueryString(TokenRequestBody)), // Make the token request TokenResponse = Json.Document( Web.Contents( "https://login.microsoftonline.com/" & TenantId & "/oauth2/v2.0/token", [ Content = FormBody, Headers = [ #"Content-Type" = "application/x-www-form-urlencoded", Accept = "application/json" ] ] ) ), // Extract the access token AccessToken = TokenResponse[access_token],

ones the token is obtained,
you will proceed and call the api, passing the token

AdvancedHuntingQuery = Json.FromValue([Query = FullQuery]), HuntingResponse = Json.Document( Web.Contents( HuntingUrl, [ Content=AdvancedHuntingQuery, Headers = [ Authorization = "Bearer " & AccessToken, #"Content-Type" = "application/json", Accept = "application/json" ] ] ) ), TypeMap = #table( { "Type", "PowerBiType" }, { { "Double", Double.Type }, { "Int64", Int64.Type }, { "Int32", Int32.Type }, { "Int16", Int16.Type }, { "UInt64", Number.Type }, { "UInt32", Number.Type }, { "UInt16", Number.Type }, { "Byte", Byte.Type }, { "Single", Single.Type }, { "Decimal", Decimal.Type }, { "TimeSpan", Duration.Type }, { "DateTime", DateTimeZone.Type }, { "String", Text.Type }, { "Boolean", Logical.Type }, { "SByte", Logical.Type }, { "Guid", Text.Type } }), Schema = Table.FromRecords(HuntingResponse[Schema]), TypedSchema = Table.Join(Table.SelectColumns(Schema, {"Name", "Type"}), {"Type"}, TypeMap , {"Type"}), Results = HuntingResponse[Results], Rows = Table.FromRecords(Results, Schema[Name]), Table = Table.TransformColumnTypes(Rows, Table.ToList(TypedSchema, (c) => {c{0}, c{2}})) in Table

The only withdraw that i am looking to solve now is the authentication. I am unable to use a service principal authentication, but i am hard coding the parameters needed by the query, Tenant_ID, Client_ID and Client_Secret.
Is there another way to authenticate and get the token without hard coding the service principal parameters into the dataflow?

Re: Power Query Defender Advanced hunting bad request 400 for some tables.

v-hashadapu — Thu, 25 Sep 2025 06:13:10 GMT

Hi mkjit256 , Thank you for reaching out to the Microsoft Fabric Community Forum.

Right now, your code works because you hard-coded the tenant, client id and secret but that’s not a safe way to keep it running long-term. The proper way in Fabric is to avoid putting secrets directly into your M script. Store the client secret in Azure Key Vault and let your Fabric workspace managed identity pull it at runtime. That way the query code stays the same but instead of writing the secret, you just call the Key Vault reference. Secrets can then be rotated or revoked without you touching the dataflow.

If you can’t set up Key Vault yet, the next-best option is to create a connection in Fabric that holds the service principal details. Your dataflow then uses that connection, so the credentials live in Fabric’s secure store and not in your script. Either way, the token flow you built stays the same, the only change is where the secret comes from. This keeps your setup secure and much easier to maintain.

Authenticate to Fabric data connections using Azure Key Vault stored secrets (Preview) | Microsoft Fabric Blog | Microsoft Fabric

Configure AKV references - Microsoft Fabric | Microsoft Learn

Azure Key Vault Reference overview (Preview) - Microsoft Fabric | Microsoft Learn

OAuth 2.0 client credentials flow on the Microsoft identity platform - Microsoft identity platform | Microsoft Learn

Microsoft Defender XDR advanced hunting API - Microsoft Defender XDR | Microsoft Learn

Connector overview - Microsoft Fabric | Microsoft Learn

Re: Power Query Defender Advanced hunting bad request 400 for some tables.

v-hashadapu — Mon, 29 Sep 2025 08:47:24 GMT

Hi mkjit256 , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

Re: Power Query Defender Advanced hunting bad request 400 for some tables.

v-hashadapu — Fri, 03 Oct 2025 12:03:59 GMT

Hi mkjit256 , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

Re: Power Query Defender Advanced hunting bad request 400 for some tables.

mkjit256 — Fri, 03 Oct 2025 15:10:05 GMT

I ended up using notebooks instead of Dataflow Gen2.

Thanks

Re: Power Query Defender Advanced hunting bad request 400 for some tables.

v-hashadapu — Mon, 06 Oct 2025 04:15:10 GMT

Hi mkjit256 ,

Thank you for the update; utilizing notebooks is a wise decision.

If you have any further questions, please do not hesitate to create a new post. We are always available to support you.

Thank you.