Forum Discussion
Solved
Help with table join in Log Analytics
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
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.
2 Replies
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.
- 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
