0

I am using the following API call in Power BI to access Advanced Hunting data from 365.

https://api.securitycenter.windows.com/api/advancedqueries

Using this API works with some tables but not others in Power BI.

For example:

DeviceEvents | limit 10 will bring back 10 results.

AlertInfo | limit 10 returns a 400 Bad Request.

Both the above queries work successfully in the advanced hunting tool within 365 itself.

I have found that this is the same with a lot of other tables within the Advanced Hunting Schema as well, such as IdentityInfo and EmailEvents to name a few more.

Is there something obvious that I am missing? I thought perhaps that it was the number of items being returned, hence the limit 10 but that didn't resolve the issue either.

This is an example of Microsofts Documentation around the Power BI specific query methods, but has not helped to resolve the issue.

    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

Thanks

Hartraft
  • 11
  • 5

1 Answers1

1

I got in touch with Microsoft who confirmed that only some tables were accessiable via the API that is in the documentation linked in my post. They provded a work around which was to use a slightly different API which I did not come across in my travels. The exerpt from the response is below:

Therefore, I changed the Advanced hunting URL from https://api.securitycenter.microsoft.com/api/advancedqueries to https://api.security.microsoft.com/api/advancedhunting from Power BI advanced Editor. (This may need you to sign in again from Power BI in Organizational account)

I can confirm that using this second API within Power BI has given me access to the additional Tables I was after.

Hartraft
  • 11
  • 5