Mail Items Accessed
Query
union OfficeActivity, CloudAppEvents
| where TimeGenerated > ago(30d)
| extend Operation = coalesce(ActionType, Operation)
| where Operation == "MailItemsAccessed"
| summarize TotalEvents = count(), TotalCloudAppsEvents = countif(Type == "CloudAppEvents"), TotalUALEvents = countif(Type == "OfficeActivity") by bin(TimeGenerated, 1d)
| extend EqualLogs = iff(TotalCloudAppsEvents == TotalUALEvents, true, false)About this query
This query compares the MailItemsAccessed from OfficeActivity and CloudAppEvents and returns the column EqualLogs that shows if the logs have the same amount of entries. This should be the case, but does not seem to be the case in multiple environment. The CloudAppEvents table seems to log a few duplicate entries.
Also see: https://x.com/BertJanCyber/status/1806350833505775775
Explanation
This query is designed to compare the number of "MailItemsAccessed" events recorded in two different data sources: OfficeActivity and CloudAppEvents. It checks if both sources have logged the same number of these events each day over the past 30 days. Here's a breakdown of what the query does:
-
Combine Data: It merges data from the OfficeActivity and CloudAppEvents tables.
-
Filter by Time and Operation: It filters the combined data to include only events from the last 30 days where the operation is "MailItemsAccessed".
-
Standardize Operation Names: It ensures that the operation name is consistent by using the
coalescefunction to handle different naming conventions in the two tables. -
Count Events: It counts the total number of "MailItemsAccessed" events each day, as well as separately counting how many of these events come from each table (OfficeActivity and CloudAppEvents).
-
Compare Counts: It adds a column called
EqualLogsthat indicates whether the number of events from CloudAppEvents matches the number from OfficeActivity for each day. If they match, it showstrue; otherwise, it showsfalse.
The query is used to identify discrepancies between the two data sources, as ideally, they should log the same number of events. However, the CloudAppEvents table appears to sometimes log duplicate entries, leading to mismatches.
Details
Bert-Jan Pals
Released: October 20, 2024
Tables
Keywords
Operators