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

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

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?

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.

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.