Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
"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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.