GSA Enriched Office 365 - PowerShell or non-browser mailbox login activity

let starttime = todatetime('{{StartTimeISO}}');
    let endtime = todatetime('{{EndTimeISO}}');
    // EnrichedMicrosoft365AuditLogs query
    let EnrichedMailboxLogin = EnrichedMicrosoft365AuditLogs
        | where TimeGenerated between (starttime .. endtime)
        | where Workload == "Exchange" and Operation == "MailboxLogin"
        | extend ClientApplication = tostring(parse_json(AdditionalProperties).ClientInfoString)
        | where ClientApplication == "Client=Microsoft.Exchange.Powershell; Microsoft WinRM Client"
        | extend TenantName = tostring(parse_json(AdditionalProperties).TenantName)
        | extend MailboxOwner = tostring(parse_json(AdditionalProperties).MailboxOwnerUPN)
        | extend LogonType = tostring(parse_json(AdditionalProperties).LogonType)
        | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), Count = count() by Operation, TenantName, UserType, UserId, MailboxOwner, LogonType, ClientApplication
        | extend AccountName = iff(UserId contains '@', tostring(split(UserId, '@')[0]), UserId)
        | extend AccountUPNSuffix = iff(UserId contains '@', tostring(split(UserId, '@')[1]), '')
        | extend AccountName = iff(UserId contains '\\', tostring(split(UserId, '\\')[1]), AccountName)
        | extend AccountNTDomain = iff(UserId contains '\\', tostring(split(UserId, '\\')[0]), '');
    // OfficeActivity query
    let OfficeMailboxLogin = OfficeActivity
        | where TimeGenerated between (starttime .. endtime)
        | where OfficeWorkload == "Exchange" and Operation == "MailboxLogin"
        | where ClientInfoString == "Client=Microsoft.Exchange.Powershell; Microsoft WinRM Client"
        | extend LogonType = "Unknown"  // If LogonType does not exist, create a placeholder
        | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), Count = count() by Operation, OrganizationName, UserType, UserId, MailboxOwnerUPN, LogonType, ClientInfoString
        | extend AccountName = iff(UserId contains '@', tostring(split(UserId, '@')[0]), UserId)
        | extend AccountUPNSuffix = iff(UserId contains '@', tostring(split(UserId, '@')[1]), '')
        | extend AccountName = iff(UserId contains '\\', tostring(split(UserId, '\\')[1]), AccountName)
        | extend AccountNTDomain = iff(UserId contains '\\', tostring(split(UserId, '\\')[0]), '');
    // Combine Enriched and Office queries
    let CombinedMailboxLogin = EnrichedMailboxLogin
        | union OfficeMailboxLogin
        | summarize arg_min(StartTime, *) by UserId, Operation
        | project StartTime, EndTime, Operation, TenantName, OrganizationName, UserType, UserId, MailboxOwner, LogonType, ClientApplication, ClientInfoString, Count, AccountName, AccountUPNSuffix, AccountNTDomain;
    // Final output
    CombinedMailboxLogin
        | order by StartTime desc;

OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Global Secure Access/Hunting Queries/powershell_or_nonbrowser_MailboxLogin.yaml
name: GSA Enriched Office 365 - PowerShell or non-browser mailbox login activity
entityMappings:
- fieldMappings:
  - identifier: Name
    columnName: AccountName
  - identifier: UPNSuffix
    columnName: AccountUPNSuffix
  - identifier: NTDomain
    columnName: AccountNTDomain
  entityType: Account
description-detailed: |
  'This will help you determine if mailbox login was done from Exchange PowerShell session. 
  By default, all accounts you create in Office 365 are allowed to use Exchange Online PowerShell. 
  Administrators can use Exchange Online PowerShell to enable or disable a user's ability to connect to Exchange Online PowerShell.
  Whitelist any benign scheduled activities using exchange PowerShell if applicable in your environment.
  References: https://docs.microsoft.com/powershell/exchange/exchange-online/connect-to-exchange-online-powershell/connect-to-exchange-online-powershell?view=exchange-ps'  
kind: Scheduled
id: 49a4f65a-fe18-408e-afec-042fde93d3ce
requiredDataConnectors:
- dataTypes:
  - EnrichedMicrosoft365AuditLogs
  connectorId: AzureActiveDirectory
description: |
    'Detects mailbox login from Exchange PowerShell. All accounts can use it by default, but admins can change it. Whitelist benign activities.'
tactics:
- Execution
- Persistence
- Collection
query: |
  let starttime = todatetime('{{StartTimeISO}}');
      let endtime = todatetime('{{EndTimeISO}}');
      // EnrichedMicrosoft365AuditLogs query
      let EnrichedMailboxLogin = EnrichedMicrosoft365AuditLogs
          | where TimeGenerated between (starttime .. endtime)
          | where Workload == "Exchange" and Operation == "MailboxLogin"
          | extend ClientApplication = tostring(parse_json(AdditionalProperties).ClientInfoString)
          | where ClientApplication == "Client=Microsoft.Exchange.Powershell; Microsoft WinRM Client"
          | extend TenantName = tostring(parse_json(AdditionalProperties).TenantName)
          | extend MailboxOwner = tostring(parse_json(AdditionalProperties).MailboxOwnerUPN)
          | extend LogonType = tostring(parse_json(AdditionalProperties).LogonType)
          | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), Count = count() by Operation, TenantName, UserType, UserId, MailboxOwner, LogonType, ClientApplication
          | extend AccountName = iff(UserId contains '@', tostring(split(UserId, '@')[0]), UserId)
          | extend AccountUPNSuffix = iff(UserId contains '@', tostring(split(UserId, '@')[1]), '')
          | extend AccountName = iff(UserId contains '\\', tostring(split(UserId, '\\')[1]), AccountName)
          | extend AccountNTDomain = iff(UserId contains '\\', tostring(split(UserId, '\\')[0]), '');
      // OfficeActivity query
      let OfficeMailboxLogin = OfficeActivity
          | where TimeGenerated between (starttime .. endtime)
          | where OfficeWorkload == "Exchange" and Operation == "MailboxLogin"
          | where ClientInfoString == "Client=Microsoft.Exchange.Powershell; Microsoft WinRM Client"
          | extend LogonType = "Unknown"  // If LogonType does not exist, create a placeholder
          | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), Count = count() by Operation, OrganizationName, UserType, UserId, MailboxOwnerUPN, LogonType, ClientInfoString
          | extend AccountName = iff(UserId contains '@', tostring(split(UserId, '@')[0]), UserId)
          | extend AccountUPNSuffix = iff(UserId contains '@', tostring(split(UserId, '@')[1]), '')
          | extend AccountName = iff(UserId contains '\\', tostring(split(UserId, '\\')[1]), AccountName)
          | extend AccountNTDomain = iff(UserId contains '\\', tostring(split(UserId, '\\')[0]), '');
      // Combine Enriched and Office queries
      let CombinedMailboxLogin = EnrichedMailboxLogin
          | union OfficeMailboxLogin
          | summarize arg_min(StartTime, *) by UserId, Operation
          | project StartTime, EndTime, Operation, TenantName, OrganizationName, UserType, UserId, MailboxOwner, LogonType, ClientApplication, ClientInfoString, Count, AccountName, AccountUPNSuffix, AccountNTDomain;
      // Final output
      CombinedMailboxLogin
          | order by StartTime desc;  
relevantTechniques:
- T1059
- T1098
- T1114
version: 2.0.2

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "apiVersion": "2024-01-01-preview",
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/49a4f65a-fe18-408e-afec-042fde93d3ce')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/49a4f65a-fe18-408e-afec-042fde93d3ce')]",
      "properties": {
        "alertRuleTemplateName": "49a4f65a-fe18-408e-afec-042fde93d3ce",
        "customDetails": null,
        "description": "'Detects mailbox login from Exchange PowerShell. All accounts can use it by default, but admins can change it. Whitelist benign activities.'\n",
        "description-detailed": "'This will help you determine if mailbox login was done from Exchange PowerShell session. \nBy default, all accounts you create in Office 365 are allowed to use Exchange Online PowerShell. \nAdministrators can use Exchange Online PowerShell to enable or disable a user's ability to connect to Exchange Online PowerShell.\nWhitelist any benign scheduled activities using exchange PowerShell if applicable in your environment.\nReferences: https://docs.microsoft.com/powershell/exchange/exchange-online/connect-to-exchange-online-powershell/connect-to-exchange-online-powershell?view=exchange-ps'\n",
        "displayName": "GSA Enriched Office 365 - PowerShell or non-browser mailbox login activity",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "AccountName",
                "identifier": "Name"
              },
              {
                "columnName": "AccountUPNSuffix",
                "identifier": "UPNSuffix"
              },
              {
                "columnName": "AccountNTDomain",
                "identifier": "NTDomain"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Global Secure Access/Hunting Queries/powershell_or_nonbrowser_MailboxLogin.yaml",
        "query": "let starttime = todatetime('{{StartTimeISO}}');\n    let endtime = todatetime('{{EndTimeISO}}');\n    // EnrichedMicrosoft365AuditLogs query\n    let EnrichedMailboxLogin = EnrichedMicrosoft365AuditLogs\n        | where TimeGenerated between (starttime .. endtime)\n        | where Workload == \"Exchange\" and Operation == \"MailboxLogin\"\n        | extend ClientApplication = tostring(parse_json(AdditionalProperties).ClientInfoString)\n        | where ClientApplication == \"Client=Microsoft.Exchange.Powershell; Microsoft WinRM Client\"\n        | extend TenantName = tostring(parse_json(AdditionalProperties).TenantName)\n        | extend MailboxOwner = tostring(parse_json(AdditionalProperties).MailboxOwnerUPN)\n        | extend LogonType = tostring(parse_json(AdditionalProperties).LogonType)\n        | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), Count = count() by Operation, TenantName, UserType, UserId, MailboxOwner, LogonType, ClientApplication\n        | extend AccountName = iff(UserId contains '@', tostring(split(UserId, '@')[0]), UserId)\n        | extend AccountUPNSuffix = iff(UserId contains '@', tostring(split(UserId, '@')[1]), '')\n        | extend AccountName = iff(UserId contains '\\\\', tostring(split(UserId, '\\\\')[1]), AccountName)\n        | extend AccountNTDomain = iff(UserId contains '\\\\', tostring(split(UserId, '\\\\')[0]), '');\n    // OfficeActivity query\n    let OfficeMailboxLogin = OfficeActivity\n        | where TimeGenerated between (starttime .. endtime)\n        | where OfficeWorkload == \"Exchange\" and Operation == \"MailboxLogin\"\n        | where ClientInfoString == \"Client=Microsoft.Exchange.Powershell; Microsoft WinRM Client\"\n        | extend LogonType = \"Unknown\"  // If LogonType does not exist, create a placeholder\n        | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), Count = count() by Operation, OrganizationName, UserType, UserId, MailboxOwnerUPN, LogonType, ClientInfoString\n        | extend AccountName = iff(UserId contains '@', tostring(split(UserId, '@')[0]), UserId)\n        | extend AccountUPNSuffix = iff(UserId contains '@', tostring(split(UserId, '@')[1]), '')\n        | extend AccountName = iff(UserId contains '\\\\', tostring(split(UserId, '\\\\')[1]), AccountName)\n        | extend AccountNTDomain = iff(UserId contains '\\\\', tostring(split(UserId, '\\\\')[0]), '');\n    // Combine Enriched and Office queries\n    let CombinedMailboxLogin = EnrichedMailboxLogin\n        | union OfficeMailboxLogin\n        | summarize arg_min(StartTime, *) by UserId, Operation\n        | project StartTime, EndTime, Operation, TenantName, OrganizationName, UserType, UserId, MailboxOwner, LogonType, ClientApplication, ClientInfoString, Count, AccountName, AccountUPNSuffix, AccountNTDomain;\n    // Final output\n    CombinedMailboxLogin\n        | order by StartTime desc;\n",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Collection",
          "Execution",
          "Persistence"
        ],
        "techniques": [
          "T1059",
          "T1098",
          "T1114"
        ],
        "templateVersion": "2.0.2"
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}