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

[mkjit256]

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 | Micr...
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

[v-hashadapu]

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

[mkjit256]

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?

[v-hashadapu]

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 F...

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 Defender XDR advanced hunting API - Microsoft Defender XDR | Microsoft Learn

Connector overview - Microsoft Fabric | Microsoft Learn

[v-hashadapu]

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.

[v-hashadapu]

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.

[mkjit256 · Accepted solution]

I ended  up using notebooks instead of  Dataflow Gen2. 

Thanks

[v-hashadapu]

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.