Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Help connecting to Defender - Advanced Hunting schema

Hello, 

 

Need some advice.  I am trying to connect power bi in order to get some reporting from Defender.  I have read where you can use Advanced hunting queries in a blank query connection.  This has been kind of hit and miss.  I can connect to the "Alerts" table and to the "DeviceEvents" table for example, but I cannot connect to a lot of other tables in the schema.  For example while trying to connect to "

IdentityLogonEvents"  I get the following error:
 
 
 
 
Here is the query I am trying in the connection: 
 
 

let
AdvancedHuntingQuery = "IdentityLogonEvents | limit 100",

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

Need Help

4 Replies

  • v-yingjl's avatar
    v-yingjl
    Community Support
    4 years ago

    Hi Anonymous ,

    Please note this limitation about Advacned Hunting API:

    The maximum query result size of a single request cannot exceed 124 MB. If exceeded, HTTP 400 Bad Request with the message "Query execution has exceeded the allowed result size. Optimize your query by limiting the amount of results and try again" will appear.

     

    So you need to consider whether the current API returns more than 124MB data in Power Query Editor.

    Refer: IdentityLogonEvents 

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi v-yingjl

       

      Thanks for replying.  I have tried to limit the results.  For example this query runs fine on entities in the schema like the device tables and I am limiting the results, in this case to 100 rows.  But for some reason, the tables relating to Identity, logon events, even email events all get the same error.   I can query these tables fine if I go to https://security.microsoft.com/v2/advanced-hunting

      but I cannot figure out why power bi cannot connect to them. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I have one more update.  I tried registering an app and grant permissions to it.  Then Power BI, I use the following query and specify my App Key Name.   It then prompts me to enter my app secret value and when I hit connect I get the following error now. 

     

    DataFormat.Error: We found extra characters at the end of JSON input.
    Details:
    Value=
    Position=4

     

    Here is what I'm testing, can anyone tell me what I'm doing wrong here? 

     

    let
    AdvancedHuntingQuery = "MyTableName | limit 20",

    HuntingUrl = "https://security.microsoft.com/v2/advanced-hunting",

    Response = Json.Document(Web.Contents(HuntingUrl, [Query=[key=AdvancedHuntingQuery],ApiKeyName="MYAPIKEYNAME"])),
    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

    • v-yingjl's avatar
      v-yingjl
      Community Support
      4 years ago

      Hi Anonymous ,

      You may need to check whether this part could return the correct query or the url is available:

      let
AdvancedHuntingQuery = "MyTableName | limit 20",

HuntingUrl = "https://security.microsoft.com/v2/advanced-hunting",

Response = Json.Document(Web.Contents(HuntingUrl, [Query=[key=AdvancedHuntingQuery],ApiKeyName="MYAPIKEYNAME"])),

      In addition, here are some similar threads that you could refer:

      1. DataFormat.Error: We found extra characters at the end of JSON input. Details: Value= Position=0 
      2. Get authorization code in JSON format 

       

      Best Regards,
      Community Support Team _ Yingjie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.