CODEWITHSUNDEEP
azure-data-explorerkql
Sean21735
Sean21735

Reputation: 35

Improve Kusto Query - mailbox audit log search

I am trying to identify shared mailboxes that aren't in use. Checked "Search-MailboxAuditLog" already and some mailboxes do not return any results even tho auditing enabled, but can see activity in Azure sentinel.

  1. Is there a way to improve below Kusto code? (During testing tried mailboxes with activities but sometimes do not get any results from the query)
  2. With Kusto, Is there a way to loop through "mbs" like powershell "foreach ( $item in $mbs)"?

Thanks,

let mbs = datatable (name: string)
[
"[email protected]",
"[email protected]",
"[email protected]",
];
OfficeActivity
| where OfficeWorkload == "Exchange" and TimeGenerated > ago(30d)
| where MailboxOwnerUPN in~ (mbs)
| distinct MailboxOwnerUPN

Update : Need help with the query

Upvotes: 0

Views: 1157

Answers (1)

Isaac Rosado
Isaac Rosado

Reputation: 1029

"in" doesn't work on datatables (tabular inputs) like that; it is not a "filter", it is an "operator". The "where" is effectively the "foreach" you are referring to.

Given the sample input, the query could probably be written as:

OfficeActivity //tabular input with many records
| TimeGenerated > ago(30d) //Filter records to window of interest first
| where OfficeWorkload == "Exchange" //foreach row
| where MailboxOwnerUPN in~ ( //foreach row
    "[email protected]","[email protected]","[email protected]"
)
| distinct MailboxOwnerUPN

You can see it in the docs at https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/inoperator#arguments where "col" is the "column to filter"

Upvotes: 0

Related Questions