advanced hunting query join

[Yangadou]

hi guys,

I got a problem on the query.

if I want to join two tables like this in 

| join IdentityInfo on $left.InitiatingProcessAccountUpn==$right.AccountUpn

it always say error.   I tried to use | join networkevent on deviceid , it works, could you please help.

[Yangadou]

"DeviceEvents
| where Timestamp > ago(15d)
| where RemoteUrl has 'xxxxxx'
| where DeviceName !=''
| where ActionType =='BrowserLaunchedToOpenUrl'
| project DeviceName,Timestamp, InitiatingProcessAccountUpn, DeviceId, ActionType, RemoteUrl
| join IdentityInfo on $left.InitiatingProcessAccountUpn==$right.AccountUpn

I can run this script successfully in advanced hunting query, but if I bring to power BI, it doesn't work.

let
AdvancedHuntingQuery = "DeviceEvents
| where Timestamp > ago(15d)
| where RemoteUrl has 'xxxxxxxxxx'
| where DeviceName !=''
| where ActionType =='BrowserLaunchedToOpenUrl'
| project DeviceName,Timestamp, InitiatingProcessAccountUpn, DeviceId, ActionType, RemoteUrl
| join IdentityInfo on $left.InitiatingProcessAccountUpn==$right.AccountUpn
| summarize by DeviceName,Timestamp, InitiatingProcessAccountUpn, DeviceId, ActionType, RemoteUrl
",

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

[artemus]

You might be better off submitting a ticket to the owners of the export to power bi button. As I don't use thier service, I cannot easily diagnonis this.

Maybe if you can post the error message (after removing any PII) I might have a better idea why this would happen.

[artemus]

I'm not familar with the advanced hunting schema. Have you tried to make sure the column types match? You should be able to use

| getschema

to see what the table/query schema is. You may need to use a

| extend SomeColumn = tostring(SomeColumn)

to get them to match.