SOLVED

Help with table join in Log Analytics

%3CLINGO-SUB%20id%3D%22lingo-sub-324062%22%20slang%3D%22en-US%22%3EHelp%20with%20table%20join%20in%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-324062%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20can%20i%20please%20have%20some%20help%20to%20get%20this%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Ejoin%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eto%20work%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EOfficeActivity%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Etable%2C%20if%20there%20is%20a%20result%20i%20want%20to%20check%20if%20there%20is%20also%20a%20row%20in%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ESecurityAlert%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Etable%20and%20join%2C%20probably%20based%20on%20the%20username.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CPRE%3E%3CSPAN%3EOfficeActivity%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20dtUTC%20%3D%20format_datetime(TimeGenerated%2C%3C%2FSPAN%3E%3CSPAN%3E'yyyy-MM-dd%20hh%3Amm'%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20dtAU%20%3D%20format_datetime(TimeGenerated%20%2B%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%3Eh%2C%3C%2FSPAN%3E%3CSPAN%3E'yyyy-MM-dd%20hh%3Amm'%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Folder%20%3D%20parse_json(Folder).Path%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20DestFolder%20%3D%20parse_json(DestFolder).Path%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20MessageID%20%3D%20parse_json(AffectedItems)%5B%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%5D.InternetMessageId%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Subject%20%3D%20parse_json(AffectedItems)%5B%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%5D.Subject%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Subject%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22New%20sign-on%20notification%22%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%2CdtUTC%2CdtAU%2CUserId%2COperation%2CResultStatus%2CClient_IPAddress%2CClientInfoString%2CClientProcessName%2CClientVersion%2CSubject%2CFolder%2CDestFolder%2CMessageID%2COfficeObjectId%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ejoin%3C%2FSPAN%3E%3CSPAN%3E%20kind%3D%20inner%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E(SecurityAlert%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20UserId%20%3D%3D%20json_parse(ExtendedProperties)%5B%3C%2FSPAN%3E%3CSPAN%3E'User%20Account'%3C%2FSPAN%3E%3CSPAN%3E%5D%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E)%20%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E%20UserId%20%3C%2FSPAN%3E%3C%2FPRE%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-324062%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-326002%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20table%20join%20in%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-326002%22%20slang%3D%22en-US%22%3EHmm%20thank%20you%20for%20this%2C%20with%20this%20slight%20mod%2C%20namely%20casting%20it%20tostring()%20-%20it%20is%20now%20working.%3CBR%20%2F%3E%3CBR%20%2F%3E%7C%20join%20kind%3D%20inner%3CBR%20%2F%3E(SecurityAlert%3CBR%20%2F%3E%7C%20extend%20UserId%20%3D%20tostring(parse_json(ExtendedProperties)%5B'User%20Account'%5D)%3CBR%20%2F%3E)%20on%20UserId%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-324794%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20table%20join%20in%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-324794%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EI%20think%20you%20mainly%20figured%20it%20out%3A%3C%2FP%3E%0A%3CPRE%3EOfficeActivity%0A%7C%20extend%20dtUTC%20%3D%20format_datetime(TimeGenerated%2C'yyyy-MM-dd%20hh%3Amm')%0A%7C%20extend%20dtAU%20%3D%20format_datetime(TimeGenerated%20%2B10h%2C'yyyy-MM-dd%20hh%3Amm')%0A%7C%20extend%20Folder%20%3D%20parse_json(Folder).Path%0A%7C%20extend%20DestFolder%20%3D%20parse_json(DestFolder).Path%0A%7C%20extend%20MessageID%20%3D%20parse_json(AffectedItems)%5B0%5D.InternetMessageId%0A%7C%20extend%20Subject%20%3D%20parse_json(AffectedItems)%5B0%5D.Subject%0A%7C%20where%20Subject%20%3D%3D%20%22New%20sign-on%20notification%22%0A%7C%20project%20TimeGenerated%2CdtUTC%2CdtAU%2CUserId%2COperation%2CResultStatus%2CClient_IPAddress%2CClientInfoString%2CClientProcessName%2CClientVersion%2CSubject%2CFolder%2CDestFolder%2CMessageID%2COfficeObjectId%0A%7C%20join%20kind%3D%20inner%0A(SecurityAlert%0A%7C%20extend%20UserId%20%3D%20json_parse(ExtendedProperties)%5B'User%20Account'%5D%0A)%20on%20UserId%20%3C%2FPRE%3E%0A%3CP%3EI%20do%20not%20have%20environment%20to%20check%20if%20fully%20work%20but%20it%20should%20be%20from%20query%20perspective.%20Let%20me%20know%20if%20this%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

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 Replies
best response confirmed by Andrew Huddleston (Frequent Contributor)
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.

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