1

I'm looking for a KQL query to transform data from the query:

EmailEvents | where EmailDirection=="Inbound"

such that the sample results (below) are transformed into the ideal results (further below)

Sample results:

TimeGenerated [UTC]      -      EmailActionPolicy
4/24/2023, 10:55:47:00          Anti-Phishing
4/24/2023, 10:55:48:00          Anti-Phishing
4/24/2023, 10:55:49:00          Anti-Phishing graph impersonation
4/24/2023, 10:55:50:00          Anti-Phishing
4/24/2023, 10:55:52:00          Anti-Phishing graph impersonation
4/24/2023, 10:55:52:00          Anti-Phishing Spoof

Ideal Results:

Date               Anti-Phishing    Anti-Phishing Spoof   Anti-Phishing graph impersonation   
4/24/2023          0                11                    75                                    
4/23/2023          55               24                    42
4/22/2023          25               44                    86
4/21/2023          23               22                    32
4/20/2023          8                67                    53

So the idea is to transform the data into a daily view of the count of each of these results for a specific field

Any help would appreciated - if there are any more conventional ways to format the ideal results, I would also be interested. Many thanks

cokeburger
  • 17
  • 2
  • chat gpt gave the following answer, but when executing, pivot is an invalid operation: EmailEvents | where EmailDirection == "Inbound" | summarize count() by bin(TimeGenerated, 1d), EmailActionPolicy | pivot EmailActionPolicy, sum(count_) | extend Date = format_datetime(TimeGenerated, 'yyyy-MM-dd') | project Date, * – cokeburger Apr 24 '23 at 11:48
  • the following gets very close, but does not give a view of the distinct email action policies in columns EmailEvents | where EmailDirection == "Inbound" | summarize count() by bin(TimeGenerated, 1d), EmailActionPolicy – cokeburger Apr 24 '23 at 11:52

1 Answers1

0

you could try using the pivot plugin.

for example:

EmailEvents
| where EmailDirection == "Inbound"
| summarize count() by EmailActionPolicy, startofday(TimeGenerated)
| evaluate pivot(EmailActionPolicy, sum(count_))
Yoni L.
  • 22,627
  • 2
  • 29
  • 48