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, probab...

azure monitor

Query Language

Stanislav_Zhelyazkov

Jan 24, 2019

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.

Stanislav_Zhelyazkov

MVP

Jan 24, 2019

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.

AndrewX

Iron Contributor

Jan 26, 2019

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

Forum Discussion

Help with table join in Log Analytics

Resources