KQL Search

Query Details

Missing Logs In Email Events

Query

let query_start = ago(30d);
let query_end = ago(1h);
union
    (EmailUrlInfo
    | where Timestamp between (query_start .. query_end)
    ),
    (EmailAttachmentInfo
    | where Timestamp between (query_start .. query_end)
    )
| join kind=leftanti (
    EmailEvents
    | where Timestamp between (query_start .. query_end)
    ) on NetworkMessageId
| summarize arg_min(Timestamp, *) by NetworkMessageId
| summarize count() by bin(Timestamp, 5m)
| render columnchart

Explanation

This KQL query is designed to analyze email data over a specified time range and visualize the results. Here's a simple summary:

  1. Time Range Definition: The query looks at data from 30 days ago up to 1 hour ago.
  2. Data Sources: It pulls data from two tables: EmailUrlInfo and EmailAttachmentInfo.
  3. Filtering: It filters records in both tables to only include those within the specified time range.
  4. Exclusion: It excludes any records that have a matching NetworkMessageId in the EmailEvents table within the same time range.
  5. Aggregation: It finds the earliest (arg_min) timestamp for each unique NetworkMessageId.
  6. Counting: It counts the number of records in 5-minute intervals.
  7. Visualization: Finally, it displays the results as a column chart.

In essence, the query identifies and visualizes email-related activities that are present in the EmailUrlInfo and EmailAttachmentInfo tables but not in the EmailEvents table, aggregated in 5-minute intervals over the past 30 days up to 1 hour ago.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: October 2, 2024

Tables

EmailUrlInfoEmailAttachmentInfoEmailEvents

Keywords

EmailUrlInfoAttachmentEvents

Operators

letagounionbetween..|wherejoinkindleftantionsummarizearg_min*bybinrendercolumnchart

Actions

GitHub