azure-data-explorerkqlazure-sentinelmicrosoft365-defenderazure-defender

Azure Sentinel (KQL)

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

Solution

  • you could try using the pivot plugin.

    for example:

    EmailEvents
| where EmailDirection == "Inbound"
| summarize count() by EmailActionPolicy, startofday(TimeGenerated)
| evaluate pivot(EmailActionPolicy, sum(count_))