0

I am currently learning KQL and have only a beginner level of knowledge with SQL.

I have a table called Dynamics365Activities which has the following columns:

  1. UserID (username of the person performing an action)
  2. SystemUserID (Object ID of the username performing an action)
  3. Operation (Type of operation performed by the user)
  4. EntityID (SystemUserID [or object id] of the person on which the operation was performed)

I would like to get a username of the person on which the operation was performed, rather than the object ID. Below is the KQL I have constructed:

Dynamics365Activity
| project TimeGenerated, UserId, SystemUserId, Message, EntityId
| join  (
Dynamics365Activity
| project UserId, EntityId, SystemUserId
) on $left.EntityId == $right.SystemUserId
| project TimeGenerated, UserId, Message, UserId1
| order by TimeGenerated desc

This KQL is returning redundant results. Any help would be highly appreciated

Ray
  • 11
  • 3
  • You can use [distinct](https://stackoverflow.com/questions/62291692/using-both-distinct-and-project) and [arg_max()](https://learn.microsoft.com/en-us/azure/data-explorer/dealing-with-duplicates#solution-2-handle-duplicate-rows-during-query) to get the unique results. – Ecstasy Nov 04 '21 at 05:27
  • 2
    Please edit the question and elaborate what you mean by "returning redundant results", and ping here when done. – Slavik N Nov 04 '21 at 07:19

0 Answers0