Help with table join in Log Analytics

[AndrewX]

Hello, can i please have some help to get this join to work?

From the OfficeActivity table, if there is a result i want to check if there is also a row in the SecurityAlert table and join, probably based on the username.

OfficeActivity
| extend dtUTC = format_datetime(TimeGenerated,'yyyy-MM-dd hh:mm')
| extend dtAU = format_datetime(TimeGenerated +10h,'yyyy-MM-dd hh:mm')
| extend Folder = parse_json(Folder).Path
| extend DestFolder = parse_json(DestFolder).Path
| extend MessageID = parse_json(AffectedItems)[0].InternetMessageId
| extend Subject = parse_json(AffectedItems)[0].Subject
| where Subject == "New sign-on notification"
| project TimeGenerated,dtUTC,dtAU,UserId,Operation,ResultStatus,Client_IPAddress,ClientInfoString,ClientProcessName,ClientVersion,Subject,Folder,DestFolder,MessageID,OfficeObjectId
| join kind= inner
(SecurityAlert
| where UserId == json_parse(ExtendedProperties)['User Account']
) on UserId 

[#2 · Accepted solution]

Hi,

I think you mainly figured it out:

OfficeActivity
| extend dtUTC = format_datetime(TimeGenerated,'yyyy-MM-dd hh:mm')
| extend dtAU = format_datetime(TimeGenerated +10h,'yyyy-MM-dd hh:mm')
| extend Folder = parse_json(Folder).Path
| extend DestFolder = parse_json(DestFolder).Path
| extend MessageID = parse_json(AffectedItems)[0].InternetMessageId
| extend Subject = parse_json(AffectedItems)[0].Subject
| where Subject == "New sign-on notification"
| project TimeGenerated,dtUTC,dtAU,UserId,Operation,ResultStatus,Client_IPAddress,ClientInfoString,ClientProcessName,ClientVersion,Subject,Folder,DestFolder,MessageID,OfficeObjectId
| join kind= inner
(SecurityAlert
| extend UserId = json_parse(ExtendedProperties)['User Account']
) on UserId 

I do not have environment to check if fully work but it should be from query perspective. Let me know if this helps.

[#3]

Hmm thank you for this, with this slight mod, namely casting it tostring() - it is now working.

| join kind= inner
(SecurityAlert
| extend UserId = tostring(parse_json(ExtendedProperties)['User Account'])
) on UserId