GSA Enriched Office 365 - New Executable via Office FileUploaded Operation

// Set query parameters
let threshold = 2;
let uploadOp = 'FileUploaded';
// Extensions that are interesting. Add/Remove to this list as you see fit
let execExt = dynamic(['exe', 'inf', 'gzip', 'cmd', 'bat']);
let starttime = 8d;
let endtime = 1d;

// OfficeActivity Query
let OfficeEvents = OfficeActivity
    | where TimeGenerated >= ago(endtime)
    | where Operation =~ uploadOp
    | where SourceFileExtension has_any (execExt)
    | extend RecordType = coalesce(tostring(RecordType), "UnknownRecordType")  // Ensure RecordType is a string
    | project TimeGenerated, OfficeId, OfficeWorkload, RecordType, Operation, UserType, UserKey, UserId, ClientIP, UserAgent, Site_Url, SourceRelativeUrl, SourceFileName
    | join kind=leftanti (
        OfficeActivity
        | where TimeGenerated between (ago(starttime) .. ago(endtime))
        | where Operation =~ uploadOp
        | where SourceFileExtension has_any (execExt)
        | summarize SourceRelativeUrl = make_set(SourceRelativeUrl, 100000), UserId = make_set(UserId, 100000), PrevSeenCount = count() by SourceFileName
        // Uncomment the line below to enforce the threshold
        //| where PrevSeenCount > threshold
        | mvexpand SourceRelativeUrl, UserId
        | extend SourceRelativeUrl = tostring(SourceRelativeUrl), UserId = tostring(UserId)  // Ensure consistent types for SourceRelativeUrl and UserId
    ) on SourceFileName, SourceRelativeUrl, UserId
    | extend SiteUrlUserFolder = tolower(split(Site_Url, '/')[-2])
    | extend UserIdUserFolderFormat = tolower(replace_regex(UserId, '@|\\.', '_'))
    | extend UserIdDiffThanUserFolder = iff(Site_Url has '/personal/' and SiteUrlUserFolder != UserIdUserFolderFormat, true, false)
    | summarize TimeGenerated = make_list(TimeGenerated, 100000), StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), UserAgents = make_list(UserAgent, 100000), OfficeIds = make_list(OfficeId, 100000), SourceRelativeUrls = make_list(SourceRelativeUrl, 100000), FileNames = make_list(tostring(SourceFileName), 100000)  // Cast FileNames to string
    by OfficeWorkload, RecordType, Operation, UserType, UserKey, UserId, ClientIP, Site_Url, SiteUrlUserFolder, UserIdUserFolderFormat, UserIdDiffThanUserFolder
    | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1]);

// EnrichedMicrosoft365AuditLogs Query
let EnrichedEvents = EnrichedMicrosoft365AuditLogs
    | where TimeGenerated >= ago(endtime)
    | where Operation == uploadOp
    | extend SourceFileExtension = extract(@"\.([^\./]+)$", 1, tostring(parse_json(tostring(AdditionalProperties)).SourceFileName))  // Extract file extension
    | where SourceFileExtension in (execExt)
    | extend Site_Url = tostring(parse_json(tostring(AdditionalProperties)).SiteUrl)
    | extend SourceRelativeUrl = tostring(parse_json(tostring(AdditionalProperties)).SourceRelativeUrl)
    | extend SourceFileName = tostring(parse_json(tostring(AdditionalProperties)).SourceFileName)
    | extend RecordType = coalesce(tostring(RecordType), "UnknownRecordType")  // Ensure RecordType is a string
    | project TimeGenerated, Id, Workload, RecordType, Operation, UserType, UserKey, UserId, ClientIp, UserAgent = tostring(parse_json(tostring(AdditionalProperties)).UserAgent), Site_Url, SourceRelativeUrl, SourceFileName
    | join kind=leftanti (
        EnrichedMicrosoft365AuditLogs
        | where TimeGenerated between (ago(starttime) .. ago(endtime))
        | where Operation == uploadOp
        | extend SourceFileExtension = extract(@"\.([^\./]+)$", 1, tostring(parse_json(tostring(AdditionalProperties)).SourceFileName))  // Extract file extension
        | where SourceFileExtension in (execExt)
        | extend SourceRelativeUrl = tostring(parse_json(tostring(AdditionalProperties)).SourceRelativeUrl)
        | summarize SourceRelativeUrl = make_set(SourceRelativeUrl, 100000), UserId = make_set(UserId, 100000), PrevSeenCount = count() by SourceFileName = tostring(parse_json(tostring(AdditionalProperties)).SourceFileName)
        // Uncomment the line below to enforce the threshold
        //| where PrevSeenCount > threshold
        | mvexpand SourceRelativeUrl, UserId
        | extend SourceRelativeUrl = tostring(SourceRelativeUrl), UserId = tostring(UserId)  // Ensure consistent types for SourceRelativeUrl and UserId
    ) on SourceFileName, SourceRelativeUrl, UserId
    | extend SiteUrlUserFolder = tolower(split(Site_Url, '/')[-2])
    | extend UserIdUserFolderFormat = tolower(replace_regex(UserId, '@|\\.', '_'))
    | extend UserIdDiffThanUserFolder = iff(Site_Url has '/personal/' and SiteUrlUserFolder != UserIdUserFolderFormat, true, false)
    | summarize TimeGenerated = make_list(TimeGenerated, 100000), StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), UserAgents = make_list(UserAgent, 100000), Ids = make_list(Id, 100000), SourceRelativeUrls = make_list(tostring(SourceRelativeUrl), 100000), FileNames = make_list(tostring(SourceFileName), 100000)  // Cast FileNames to string
    by Workload, RecordType, Operation, UserType, UserKey, UserId, ClientIp, Site_Url, SiteUrlUserFolder, UserIdUserFolderFormat, UserIdDiffThanUserFolder
    | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1]);

// Combine Office and Enriched Logs
let CombinedEvents = EnrichedEvents
    | union isfuzzy=true OfficeEvents
    | summarize arg_min(StartTime, *) by tostring(FileNames), UserId, Site_Url, tostring(RecordType)  // Ensure FileNames and RecordType are strings
    | order by StartTime desc;

// Final Output
CombinedEvents
    | project StartTime, EndTime, Workload, RecordType, Operation, UserType, UserKey, UserId, ClientIP, Site_Url, SiteUrlUserFolder, UserIdUserFolderFormat, UserIdDiffThanUserFolder, FileNames, UserAgents, AccountName, AccountUPNSuffix;

status: Available
triggerOperator: gt
triggerThreshold: 0
name: GSA Enriched Office 365 - New Executable via Office FileUploaded Operation
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Global Secure Access/Analytic Rules/Office 365 - Office_Uploaded_Executables.yaml
queryPeriod: 8d
severity: Low
kind: Scheduled
entityMappings:
- entityType: Account
  fieldMappings:
  - columnName: UserId
    identifier: FullName
  - columnName: AccountName
    identifier: Name
  - columnName: AccountUPNSuffix
    identifier: UPNSuffix
- entityType: IP
  fieldMappings:
  - columnName: ClientIP
    identifier: Address
- entityType: URL
  fieldMappings:
  - columnName: Site_Url
    identifier: Url
- entityType: File
  fieldMappings:
  - columnName: FileNames
    identifier: Name
queryFrequency: 1d
relevantTechniques:
- T1105
- T1570
requiredDataConnectors:
- dataTypes:
  - EnrichedMicrosoft365AuditLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - OfficeActivity (SharePoint)
  connectorId: Office365
description: |
  Identifies when executable file types are uploaded to Office services such as SharePoint and OneDrive.
  List currently includes exe, inf, gzip, cmd, bat file extensions.
  Additionally, identifies when a given user is uploading these files to another user's workspace.
  This may be an indication of a staging location for malware or other malicious activity.  
tactics:
- CommandAndControl
- LateralMovement
query: |
  // Set query parameters
  let threshold = 2;
  let uploadOp = 'FileUploaded';
  // Extensions that are interesting. Add/Remove to this list as you see fit
  let execExt = dynamic(['exe', 'inf', 'gzip', 'cmd', 'bat']);
  let starttime = 8d;
  let endtime = 1d;

  // OfficeActivity Query
  let OfficeEvents = OfficeActivity
      | where TimeGenerated >= ago(endtime)
      | where Operation =~ uploadOp
      | where SourceFileExtension has_any (execExt)
      | extend RecordType = coalesce(tostring(RecordType), "UnknownRecordType")  // Ensure RecordType is a string
      | project TimeGenerated, OfficeId, OfficeWorkload, RecordType, Operation, UserType, UserKey, UserId, ClientIP, UserAgent, Site_Url, SourceRelativeUrl, SourceFileName
      | join kind=leftanti (
          OfficeActivity
          | where TimeGenerated between (ago(starttime) .. ago(endtime))
          | where Operation =~ uploadOp
          | where SourceFileExtension has_any (execExt)
          | summarize SourceRelativeUrl = make_set(SourceRelativeUrl, 100000), UserId = make_set(UserId, 100000), PrevSeenCount = count() by SourceFileName
          // Uncomment the line below to enforce the threshold
          //| where PrevSeenCount > threshold
          | mvexpand SourceRelativeUrl, UserId
          | extend SourceRelativeUrl = tostring(SourceRelativeUrl), UserId = tostring(UserId)  // Ensure consistent types for SourceRelativeUrl and UserId
      ) on SourceFileName, SourceRelativeUrl, UserId
      | extend SiteUrlUserFolder = tolower(split(Site_Url, '/')[-2])
      | extend UserIdUserFolderFormat = tolower(replace_regex(UserId, '@|\\.', '_'))
      | extend UserIdDiffThanUserFolder = iff(Site_Url has '/personal/' and SiteUrlUserFolder != UserIdUserFolderFormat, true, false)
      | summarize TimeGenerated = make_list(TimeGenerated, 100000), StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), UserAgents = make_list(UserAgent, 100000), OfficeIds = make_list(OfficeId, 100000), SourceRelativeUrls = make_list(SourceRelativeUrl, 100000), FileNames = make_list(tostring(SourceFileName), 100000)  // Cast FileNames to string
      by OfficeWorkload, RecordType, Operation, UserType, UserKey, UserId, ClientIP, Site_Url, SiteUrlUserFolder, UserIdUserFolderFormat, UserIdDiffThanUserFolder
      | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1]);

  // EnrichedMicrosoft365AuditLogs Query
  let EnrichedEvents = EnrichedMicrosoft365AuditLogs
      | where TimeGenerated >= ago(endtime)
      | where Operation == uploadOp
      | extend SourceFileExtension = extract(@"\.([^\./]+)$", 1, tostring(parse_json(tostring(AdditionalProperties)).SourceFileName))  // Extract file extension
      | where SourceFileExtension in (execExt)
      | extend Site_Url = tostring(parse_json(tostring(AdditionalProperties)).SiteUrl)
      | extend SourceRelativeUrl = tostring(parse_json(tostring(AdditionalProperties)).SourceRelativeUrl)
      | extend SourceFileName = tostring(parse_json(tostring(AdditionalProperties)).SourceFileName)
      | extend RecordType = coalesce(tostring(RecordType), "UnknownRecordType")  // Ensure RecordType is a string
      | project TimeGenerated, Id, Workload, RecordType, Operation, UserType, UserKey, UserId, ClientIp, UserAgent = tostring(parse_json(tostring(AdditionalProperties)).UserAgent), Site_Url, SourceRelativeUrl, SourceFileName
      | join kind=leftanti (
          EnrichedMicrosoft365AuditLogs
          | where TimeGenerated between (ago(starttime) .. ago(endtime))
          | where Operation == uploadOp
          | extend SourceFileExtension = extract(@"\.([^\./]+)$", 1, tostring(parse_json(tostring(AdditionalProperties)).SourceFileName))  // Extract file extension
          | where SourceFileExtension in (execExt)
          | extend SourceRelativeUrl = tostring(parse_json(tostring(AdditionalProperties)).SourceRelativeUrl)
          | summarize SourceRelativeUrl = make_set(SourceRelativeUrl, 100000), UserId = make_set(UserId, 100000), PrevSeenCount = count() by SourceFileName = tostring(parse_json(tostring(AdditionalProperties)).SourceFileName)
          // Uncomment the line below to enforce the threshold
          //| where PrevSeenCount > threshold
          | mvexpand SourceRelativeUrl, UserId
          | extend SourceRelativeUrl = tostring(SourceRelativeUrl), UserId = tostring(UserId)  // Ensure consistent types for SourceRelativeUrl and UserId
      ) on SourceFileName, SourceRelativeUrl, UserId
      | extend SiteUrlUserFolder = tolower(split(Site_Url, '/')[-2])
      | extend UserIdUserFolderFormat = tolower(replace_regex(UserId, '@|\\.', '_'))
      | extend UserIdDiffThanUserFolder = iff(Site_Url has '/personal/' and SiteUrlUserFolder != UserIdUserFolderFormat, true, false)
      | summarize TimeGenerated = make_list(TimeGenerated, 100000), StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), UserAgents = make_list(UserAgent, 100000), Ids = make_list(Id, 100000), SourceRelativeUrls = make_list(tostring(SourceRelativeUrl), 100000), FileNames = make_list(tostring(SourceFileName), 100000)  // Cast FileNames to string
      by Workload, RecordType, Operation, UserType, UserKey, UserId, ClientIp, Site_Url, SiteUrlUserFolder, UserIdUserFolderFormat, UserIdDiffThanUserFolder
      | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1]);

  // Combine Office and Enriched Logs
  let CombinedEvents = EnrichedEvents
      | union isfuzzy=true OfficeEvents
      | summarize arg_min(StartTime, *) by tostring(FileNames), UserId, Site_Url, tostring(RecordType)  // Ensure FileNames and RecordType are strings
      | order by StartTime desc;

  // Final Output
  CombinedEvents
      | project StartTime, EndTime, Workload, RecordType, Operation, UserType, UserKey, UserId, ClientIP, Site_Url, SiteUrlUserFolder, UserIdUserFolderFormat, UserIdDiffThanUserFolder, FileNames, UserAgents, AccountName, AccountUPNSuffix;  
id: 178c62b4-d5e5-40f5-8eab-7fccd0051e7a
version: 2.0.7

{
  "$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/178c62b4-d5e5-40f5-8eab-7fccd0051e7a')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/178c62b4-d5e5-40f5-8eab-7fccd0051e7a')]",
      "properties": {
        "alertRuleTemplateName": "178c62b4-d5e5-40f5-8eab-7fccd0051e7a",
        "customDetails": null,
        "description": "Identifies when executable file types are uploaded to Office services such as SharePoint and OneDrive.\nList currently includes exe, inf, gzip, cmd, bat file extensions.\nAdditionally, identifies when a given user is uploading these files to another user's workspace.\nThis may be an indication of a staging location for malware or other malicious activity.\n",
        "displayName": "GSA Enriched Office 365 - New Executable via Office FileUploaded Operation",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "UserId",
                "identifier": "FullName"
              },
              {
                "columnName": "AccountName",
                "identifier": "Name"
              },
              {
                "columnName": "AccountUPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "ClientIP",
                "identifier": "Address"
              }
            ]
          },
          {
            "entityType": "URL",
            "fieldMappings": [
              {
                "columnName": "Site_Url",
                "identifier": "Url"
              }
            ]
          },
          {
            "entityType": "File",
            "fieldMappings": [
              {
                "columnName": "FileNames",
                "identifier": "Name"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Global Secure Access/Analytic Rules/Office 365 - Office_Uploaded_Executables.yaml",
        "query": "// Set query parameters\nlet threshold = 2;\nlet uploadOp = 'FileUploaded';\n// Extensions that are interesting. Add/Remove to this list as you see fit\nlet execExt = dynamic(['exe', 'inf', 'gzip', 'cmd', 'bat']);\nlet starttime = 8d;\nlet endtime = 1d;\n\n// OfficeActivity Query\nlet OfficeEvents = OfficeActivity\n    | where TimeGenerated >= ago(endtime)\n    | where Operation =~ uploadOp\n    | where SourceFileExtension has_any (execExt)\n    | extend RecordType = coalesce(tostring(RecordType), \"UnknownRecordType\")  // Ensure RecordType is a string\n    | project TimeGenerated, OfficeId, OfficeWorkload, RecordType, Operation, UserType, UserKey, UserId, ClientIP, UserAgent, Site_Url, SourceRelativeUrl, SourceFileName\n    | join kind=leftanti (\n        OfficeActivity\n        | where TimeGenerated between (ago(starttime) .. ago(endtime))\n        | where Operation =~ uploadOp\n        | where SourceFileExtension has_any (execExt)\n        | summarize SourceRelativeUrl = make_set(SourceRelativeUrl, 100000), UserId = make_set(UserId, 100000), PrevSeenCount = count() by SourceFileName\n        // Uncomment the line below to enforce the threshold\n        //| where PrevSeenCount > threshold\n        | mvexpand SourceRelativeUrl, UserId\n        | extend SourceRelativeUrl = tostring(SourceRelativeUrl), UserId = tostring(UserId)  // Ensure consistent types for SourceRelativeUrl and UserId\n    ) on SourceFileName, SourceRelativeUrl, UserId\n    | extend SiteUrlUserFolder = tolower(split(Site_Url, '/')[-2])\n    | extend UserIdUserFolderFormat = tolower(replace_regex(UserId, '@|\\\\.', '_'))\n    | extend UserIdDiffThanUserFolder = iff(Site_Url has '/personal/' and SiteUrlUserFolder != UserIdUserFolderFormat, true, false)\n    | summarize TimeGenerated = make_list(TimeGenerated, 100000), StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), UserAgents = make_list(UserAgent, 100000), OfficeIds = make_list(OfficeId, 100000), SourceRelativeUrls = make_list(SourceRelativeUrl, 100000), FileNames = make_list(tostring(SourceFileName), 100000)  // Cast FileNames to string\n    by OfficeWorkload, RecordType, Operation, UserType, UserKey, UserId, ClientIP, Site_Url, SiteUrlUserFolder, UserIdUserFolderFormat, UserIdDiffThanUserFolder\n    | extend AccountName = tostring(split(UserId, \"@\")[0]), AccountUPNSuffix = tostring(split(UserId, \"@\")[1]);\n\n// EnrichedMicrosoft365AuditLogs Query\nlet EnrichedEvents = EnrichedMicrosoft365AuditLogs\n    | where TimeGenerated >= ago(endtime)\n    | where Operation == uploadOp\n    | extend SourceFileExtension = extract(@\"\\.([^\\./]+)$\", 1, tostring(parse_json(tostring(AdditionalProperties)).SourceFileName))  // Extract file extension\n    | where SourceFileExtension in (execExt)\n    | extend Site_Url = tostring(parse_json(tostring(AdditionalProperties)).SiteUrl)\n    | extend SourceRelativeUrl = tostring(parse_json(tostring(AdditionalProperties)).SourceRelativeUrl)\n    | extend SourceFileName = tostring(parse_json(tostring(AdditionalProperties)).SourceFileName)\n    | extend RecordType = coalesce(tostring(RecordType), \"UnknownRecordType\")  // Ensure RecordType is a string\n    | project TimeGenerated, Id, Workload, RecordType, Operation, UserType, UserKey, UserId, ClientIp, UserAgent = tostring(parse_json(tostring(AdditionalProperties)).UserAgent), Site_Url, SourceRelativeUrl, SourceFileName\n    | join kind=leftanti (\n        EnrichedMicrosoft365AuditLogs\n        | where TimeGenerated between (ago(starttime) .. ago(endtime))\n        | where Operation == uploadOp\n        | extend SourceFileExtension = extract(@\"\\.([^\\./]+)$\", 1, tostring(parse_json(tostring(AdditionalProperties)).SourceFileName))  // Extract file extension\n        | where SourceFileExtension in (execExt)\n        | extend SourceRelativeUrl = tostring(parse_json(tostring(AdditionalProperties)).SourceRelativeUrl)\n        | summarize SourceRelativeUrl = make_set(SourceRelativeUrl, 100000), UserId = make_set(UserId, 100000), PrevSeenCount = count() by SourceFileName = tostring(parse_json(tostring(AdditionalProperties)).SourceFileName)\n        // Uncomment the line below to enforce the threshold\n        //| where PrevSeenCount > threshold\n        | mvexpand SourceRelativeUrl, UserId\n        | extend SourceRelativeUrl = tostring(SourceRelativeUrl), UserId = tostring(UserId)  // Ensure consistent types for SourceRelativeUrl and UserId\n    ) on SourceFileName, SourceRelativeUrl, UserId\n    | extend SiteUrlUserFolder = tolower(split(Site_Url, '/')[-2])\n    | extend UserIdUserFolderFormat = tolower(replace_regex(UserId, '@|\\\\.', '_'))\n    | extend UserIdDiffThanUserFolder = iff(Site_Url has '/personal/' and SiteUrlUserFolder != UserIdUserFolderFormat, true, false)\n    | summarize TimeGenerated = make_list(TimeGenerated, 100000), StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), UserAgents = make_list(UserAgent, 100000), Ids = make_list(Id, 100000), SourceRelativeUrls = make_list(tostring(SourceRelativeUrl), 100000), FileNames = make_list(tostring(SourceFileName), 100000)  // Cast FileNames to string\n    by Workload, RecordType, Operation, UserType, UserKey, UserId, ClientIp, Site_Url, SiteUrlUserFolder, UserIdUserFolderFormat, UserIdDiffThanUserFolder\n    | extend AccountName = tostring(split(UserId, \"@\")[0]), AccountUPNSuffix = tostring(split(UserId, \"@\")[1]);\n\n// Combine Office and Enriched Logs\nlet CombinedEvents = EnrichedEvents\n    | union isfuzzy=true OfficeEvents\n    | summarize arg_min(StartTime, *) by tostring(FileNames), UserId, Site_Url, tostring(RecordType)  // Ensure FileNames and RecordType are strings\n    | order by StartTime desc;\n\n// Final Output\nCombinedEvents\n    | project StartTime, EndTime, Workload, RecordType, Operation, UserType, UserKey, UserId, ClientIP, Site_Url, SiteUrlUserFolder, UserIdUserFolderFormat, UserIdDiffThanUserFolder, FileNames, UserAgents, AccountName, AccountUPNSuffix;\n",
        "queryFrequency": "P1D",
        "queryPeriod": "P8D",
        "severity": "Low",
        "status": "Available",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "CommandAndControl",
          "LateralMovement"
        ],
        "techniques": [
          "T1105",
          "T1570"
        ],
        "templateVersion": "2.0.7",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}