Forum Discussion
Issue with Capturing Email Subject from URL Click Events
Hi Sentinel Community,
I am encountering an issue where the email subject line is not being captured correctly for certain records in my URL click event table. My goal is to get the URLs clicked by users along with the corresponding email subject lines. To achieve this, I applied a left outer join between the URL click event table and the email event table based on the network message ID.
However, after running the query, I notice that the subject column is empty for some records where users have clicked the URL, which was originally sent via email
the query which i used
UrlClickEvents
| search "email address removed for privacy reasons" // user name
| join kind= leftouter EmailEvents on $left.NetworkMessageId== $right.NetworkMessageId
//| where RecipientEmailAddress=="example.com"
| join kind= leftouter EmailPostDeliveryEvents on $left.NetworkMessageId== $right.NetworkMessageId
//|where isempty( Subject) and Workload =~"email"
| project TimeGenerated,Subject,SenderFromAddress=hash_sha256(SenderFromAddress),RecipientEmailAddress=hash_sha256(RecipientEmailAddress),Url=hash_md5(Url),Workload,NetworkMessageId=hash_md5(NetworkMessageId)
2 Replies
- On line 3 you are using a leftouter join. Using no join type or inner would resolve this
https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/join-leftouter
The docs say:"Rows: All records from the left table and only matching rows from the right table."
So in your case all the NetworkMesageIDs are being shown even if they DONT have a match- Hi Clive_Watson,
You are right. I can apply an inner join, but in that case, I will only get the common records from both tables (the email event table and the URL click event table). However, my motive is different i need to get all url from url click event table and there mail details if the work load in email .
Let's assume user "abc" clicked 6 URLs. Out of these 6 URLs, 3 came from the team application. When I tried to join both tables based on the network message ID, I couldn't get the subject and sender details. The remaining 3 URLs are showing the workload as "email," which indicates that the URLs came from an email.
When I searched for the email in the email event table based on the network message ID, there was no result. It seems the network message ID has only been captured in the URL click event table. Is there any specific reason why the email details were not captured in the email event table, even though the workload is mentioned as "email" in the URL click event table?