{"id":8128,"date":"2024-07-18T01:46:03","date_gmt":"2024-07-18T01:46:03","guid":{"rendered":"https:\/\/pariswells.com\/blog\/?p=8128"},"modified":"2024-07-18T01:46:05","modified_gmt":"2024-07-18T01:46:05","slug":"advanced-hunting-to-find-word-and-excel-macros-in-environment-with-defender","status":"publish","type":"post","link":"https:\/\/pariswells.com\/blog\/research\/advanced-hunting-to-find-word-and-excel-macros-in-environment-with-defender","title":{"rendered":"Advanced Hunting to Find Word and Excel Macros in Environment with Defender"},"content":{"rendered":"\n
\/\/Summarize macro usage on your devies by creating a list all macros used, a count of how many users are using each one and the account names\n\n\/\/Data connector required for this query - M365 Defender - Device* tables\n\n\/\/Macro usage may be double counted if the same file is executed from two locations, i.e from a network share and a local drive.\n\/\/Microsoft Sentinel query\nunion DeviceFileEvents, DeviceNetworkEvents\n| where TimeGenerated > ago(30d)\n| project InitiatingProcessCommandLine, InitiatingProcessAccountName\n| where InitiatingProcessCommandLine startswith '\"EXCEL.EXE'  \n| where InitiatingProcessCommandLine endswith '.xltm\"' or InitiatingProcessCommandLine endswith '.xlsm\"'\n\/\/Retrieve distinct values for process, hash and account\n| distinct InitiatingProcessCommandLine, InitiatingProcessAccountName\n\/\/Parse the file path and file name from the process\n| parse-where InitiatingProcessCommandLine with * '\"EXCEL.EXE\" \"' ['Macro Filename'] '\"' *\n\/\/Summarize the list of macro files by which users have used them\n| summarize ['List of Users']=make_set(InitiatingProcessAccountName), ['Count of Users']=dcount(InitiatingProcessAccountName) by ['Macro Filename']\n| sort by ['Count of Users'] desc \n\n\/\/Advanced Hunting query\n\n\/\/Data connector required for this query - Advanced Hunting license\n\nunion DeviceFileEvents, DeviceNetworkEvents\n| where Timestamp > ago(30d)\n| project InitiatingProcessCommandLine, InitiatingProcessAccountName\n| where InitiatingProcessCommandLine startswith '\"EXCEL.EXE'  \n| where InitiatingProcessCommandLine endswith '.xltm\"' or InitiatingProcessCommandLine endswith '.xlsm\"'\n\/\/Retrieve distinct values for process, hash and account\n| distinct InitiatingProcessCommandLine, InitiatingProcessAccountName\n\/\/Parse the file path and file name from the process\n| parse-where InitiatingProcessCommandLine with * '\"EXCEL.EXE\" \"' ['Macro Filename'] '\"' *\n\/\/Summarize the list of macro files by which users have used them\n| summarize ['List of Users']=make_set(InitiatingProcessAccountName), ['Count of Users']=dcount(InitiatingProcessAccountName) by ['Macro Filename']\n| sort by ['Count of Users'] desc <\/code><\/pre>\n\n\n\n
\/\/Detect when an Excel macro connects to the internet. \n\/\/Some IPs returned shown may be Microsoft telemetry but these events are still worth investigating.\n\n\/\/Data connector required for this query - M365 Defender - Device* tables\n\n\/\/Microsoft Sentinel query\nDeviceNetworkEvents\n| where InitiatingProcessFileName contains \"excel.exe\"\n| where InitiatingProcessCommandLine contains \".xlsm\" or InitiatingProcessCommandLine contains \".xltm\"\n\/\/Exclude Microsoft telemetry endpoints \n| where RemoteUrl !endswith \"outlook.com\" \n    and RemoteUrl !endswith \"office.com\"\n    and RemoteUrl !endswith \"microsoft.com\"\n    and RemoteUrl !endswith \"office365.com\"\n    and RemoteUrl !endswith \"live.com\"\n    and RemoteUrl !endswith \"office.net\"\n| where RemoteIPType == \"Public\"\n| project\n    TimeGenerated,\n    DeviceName,\n    InitiatingProcessCommandLine,\n    LocalIP,\n    RemoteIP,\n    RemotePort,\n    RemoteUrl\n\n\/\/Advanced Hunting query\n\n\/\/Data connector required for this query - Advanced Hunting license\n\nDeviceNetworkEvents\n| where InitiatingProcessFileName contains \"excel.exe\"\n| where InitiatingProcessCommandLine contains \".xlsm\" or InitiatingProcessCommandLine contains \".xltm\"\n| where RemoteIPType == \"Public\"\n\/\/Exclude Microsoft telemetry endpoints \n| where RemoteUrl !endswith \"outlook.com\" \n    and RemoteUrl !endswith \"office.com\"\n    and RemoteUrl !endswith \"microsoft.com\"\n    and RemoteUrl !endswith \"office365.com\"\n    and RemoteUrl !endswith \"live.com\"\n    and RemoteUrl !endswith \"office.net\"\n| project\n    Timestamp,\n    DeviceName,\n    InitiatingProcessCommandLine,\n    LocalIP,\n    RemoteIP,\n    RemotePort,\n    RemoteUrl<\/code><\/pre>\n\n\n\n
\/\/\"Now we know that every time a user clicks on 'Enable Editing; or 'Enable \/\/Content', Microsoft Office will add the path to the document as a Registry \/\/value under the program's TrustRecords key.\n\nDeviceRegistryEvents\n| where RegistryKey has @\"SOFTWARE\\Microsoft\\Office\\16.0\\Excel\\Security\\Trusted Documents\\TrustRecords\"\n| where RegistryValueName has \"xlsm\"\n| project Timestamp, DeviceName, RegistryValueName\n\nDeviceRegistryEvents\n| where RegistryKey has @\"SOFTWARE\\Microsoft\\Office\\16.0\\Word\\Security\\Trusted Documents\\TrustRecords\"\n| where RegistryValueName has \"docm\"\n| project Timestamp, DeviceName, RegistryValueName<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-8128","post","type-post","status-publish","format-standard","hentry","category-research"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t