New executable via Office FileUploaded Operation

// a threshold can be enabled, see commented line below for PrevSeenCount
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 | where TimeGenerated >= ago(endtime)
// Limited to File Uploads due to potential noise, comment out the Operation statement below to include any operation type
// Additional, but potentially noisy operation types that include Uploads and Downloads can be included by adding the following - Operation contains "upload" or Operation contains "download"
| where Operation =~ uploadOp
| where SourceFileExtension has_any (execExt)
| 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
// To exclude previous matches when only above a specific count, change threshold above and uncomment the line below
//| where PrevSeenCount > threshold
| mvexpand SourceRelativeUrl, UserId
| extend SourceRelativeUrl = tostring(SourceRelativeUrl), UserId = tostring(UserId)
) on SourceFileName, SourceRelativeUrl, UserId
| extend SiteUrlUserFolder = tolower(split(Site_Url, '/')[-2])
| extend UserIdUserFolderFormat = tolower(replace_regex(UserId, '@|\\.', '_'))
// identify when UserId is not a match to the specific site url personal folder reference
| 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(SourceFileName, 100000)
by OfficeWorkload, RecordType, Operation, UserType, UserKey, UserId, ClientIP, Site_Url, SiteUrlUserFolder, UserIdUserFolderFormat, UserIdDiffThanUserFolder
| extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])

entityMappings:
- fieldMappings:
  - columnName: UserId
    identifier: FullName
  - columnName: AccountName
    identifier: Name
  - columnName: AccountUPNSuffix
    identifier: UPNSuffix
  entityType: Account
- fieldMappings:
  - columnName: ClientIP
    identifier: Address
  entityType: IP
- fieldMappings:
  - columnName: Site_Url
    identifier: Url
  entityType: URL
- fieldMappings:
  - columnName: FileNames
    identifier: Name
  entityType: File
severity: Low
name: New executable via Office FileUploaded Operation
triggerThreshold: 0
triggerOperator: gt
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft 365/Analytic Rules/Office_Uploaded_Executables.yaml
id: d722831e-88f5-4e25-b106-4ef6e29f8c13
kind: Scheduled
status: Available
queryFrequency: 1d
relevantTechniques:
- T1105
- T1570
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 users workspace.
  This may be indication of a staging location for malware or other malicious activity.'  
query: |
  // a threshold can be enabled, see commented line below for PrevSeenCount
  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 | where TimeGenerated >= ago(endtime)
  // Limited to File Uploads due to potential noise, comment out the Operation statement below to include any operation type
  // Additional, but potentially noisy operation types that include Uploads and Downloads can be included by adding the following - Operation contains "upload" or Operation contains "download"
  | where Operation =~ uploadOp
  | where SourceFileExtension has_any (execExt)
  | 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
  // To exclude previous matches when only above a specific count, change threshold above and uncomment the line below
  //| where PrevSeenCount > threshold
  | mvexpand SourceRelativeUrl, UserId
  | extend SourceRelativeUrl = tostring(SourceRelativeUrl), UserId = tostring(UserId)
  ) on SourceFileName, SourceRelativeUrl, UserId
  | extend SiteUrlUserFolder = tolower(split(Site_Url, '/')[-2])
  | extend UserIdUserFolderFormat = tolower(replace_regex(UserId, '@|\\.', '_'))
  // identify when UserId is not a match to the specific site url personal folder reference
  | 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(SourceFileName, 100000)
  by OfficeWorkload, RecordType, Operation, UserType, UserKey, UserId, ClientIP, Site_Url, SiteUrlUserFolder, UserIdUserFolderFormat, UserIdDiffThanUserFolder
  | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])  
version: 2.0.5
tactics:
- CommandAndControl
- LateralMovement
queryPeriod: 8d
requiredDataConnectors:
- dataTypes:
  - OfficeActivity (SharePoint)
  connectorId: Office365