3

I am performing a join between two events as follows. How can I get distinct rows in my final output?

let fromdate = "2017-04-26 23:00";

let fileEvents = (Events
| where Timestamp > todatetime(fromdate) 
| project fileId, fileName, Application);

fileEvents | join (Events
    | where Timestamp > todatetime(fromdate) and Data.Size > 1024
    | project fileId) on fileId
| project fileId,Application, fileName;

Query output

1 , Web , Agreement
1 , Web , Agreement
2 , Api , Contract
2 , Api , Contract
1 , Web , Agreement
2 , Api , Contract

I want the output to be 

1 , Web , Agreement
2 , Api , Contract
Praveen Reddy
  • 7,295
  • 2
  • 21
  • 43

1 Answers1

3

Use the summarize operator to summarize by all result columns:

let fromdate = "2017-04-26 23:00";

let fileEvents = (Events
| where Timestamp > todatetime(fromdate) 
| project fileId, fileName, Application);

fileEvents
| join (Events
| where Timestamp > todatetime(fromdate) and Data.Size > 1024
| project fileId) on fileId
| summarize by fileId, Application, fileName

An equivalent of the above will be:

let fromdate = "2017-04-26 23:00";
Events
| where Timestamp > todatetime(fromdate) 
| project fileId, fileName, Application
| join (
    Events
    | where Timestamp > todatetime(fromdate) and Data.Size > 1024
    | project fileId) on fileId
| summarize by fileId, Application, fileName
yonisha
  • 2,956
  • 2
  • 25
  • 32
  • Thanks it works. But I am getting a Bad argument error when I include some of the columns. Is there a way to overcome that problem? Will it be a problem if some of the columns do not have values in some instances? – Praveen Reddy Apr 28 '17 at 22:28
  • Can you share the error? Empty values should not be an issue – yonisha Apr 28 '17 at 22:42
  • Here is my complete [code](https://gist.github.com/codingdawg/c8fa3bbf73064bb6a8c7eb895898b29b) .. Added the error message at the bottom. In the code I have commented the problematic code on Line 28. – Praveen Reddy Apr 28 '17 at 22:50
  • Make sure to explicitly cast dynamic values before using them for summarization - same as you've casted 'envId' – yonisha Apr 28 '17 at 23:05