Help connecting to Defender - Advanced Hunting schema

[Anonymous]

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:

DataSource.Error: Web.Contents failed to get contents from 'https://api.securitycenter.microsoft.com/api/advancedqueries?key=IdentityLogonEvents%20%20%7C%20limi...' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.securitycenter.microsoft.com/api/advancedqueries
Url=https://api.securitycenter.microsoft.com/api/advancedqueries?key=IdentityLogonEvents%20%20%7C%20limi...

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

[Anonymous]

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]

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.

[v-yingjl]

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]

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.