//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

//Data connector required for this query - M365 Defender - Device* tables

//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.
//Microsoft Sentinel query
union DeviceFileEvents, DeviceNetworkEvents
| where TimeGenerated > ago(30d)
| project InitiatingProcessCommandLine, InitiatingProcessAccountName
| where InitiatingProcessCommandLine startswith '"EXCEL.EXE'  
| where InitiatingProcessCommandLine endswith '.xltm"' or InitiatingProcessCommandLine endswith '.xlsm"'
//Retrieve distinct values for process, hash and account
| distinct InitiatingProcessCommandLine, InitiatingProcessAccountName
//Parse the file path and file name from the process
| parse-where InitiatingProcessCommandLine with * '"EXCEL.EXE" "' ['Macro Filename'] '"' *
//Summarize the list of macro files by which users have used them
| summarize ['List of Users']=make_set(InitiatingProcessAccountName), ['Count of Users']=dcount(InitiatingProcessAccountName) by ['Macro Filename']
| sort by ['Count of Users'] desc 

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

union DeviceFileEvents, DeviceNetworkEvents
| where Timestamp > ago(30d)
| project InitiatingProcessCommandLine, InitiatingProcessAccountName
| where InitiatingProcessCommandLine startswith '"EXCEL.EXE'  
| where InitiatingProcessCommandLine endswith '.xltm"' or InitiatingProcessCommandLine endswith '.xlsm"'
//Retrieve distinct values for process, hash and account
| distinct InitiatingProcessCommandLine, InitiatingProcessAccountName
//Parse the file path and file name from the process
| parse-where InitiatingProcessCommandLine with * '"EXCEL.EXE" "' ['Macro Filename'] '"' *
//Summarize the list of macro files by which users have used them
| summarize ['List of Users']=make_set(InitiatingProcessAccountName), ['Count of Users']=dcount(InitiatingProcessAccountName) by ['Macro Filename']
| sort by ['Count of Users'] desc 

//Detect when an Excel macro connects to the internet. 
//Some IPs returned shown may be Microsoft telemetry but these events are still worth investigating.

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceNetworkEvents
| where InitiatingProcessFileName contains "excel.exe"
| where InitiatingProcessCommandLine contains ".xlsm" or InitiatingProcessCommandLine contains ".xltm"
//Exclude Microsoft telemetry endpoints 
| where RemoteUrl !endswith "outlook.com" 
    and RemoteUrl !endswith "office.com"
    and RemoteUrl !endswith "microsoft.com"
    and RemoteUrl !endswith "office365.com"
    and RemoteUrl !endswith "live.com"
    and RemoteUrl !endswith "office.net"
| where RemoteIPType == "Public"
| project
    TimeGenerated,
    DeviceName,
    InitiatingProcessCommandLine,
    LocalIP,
    RemoteIP,
    RemotePort,
    RemoteUrl

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceNetworkEvents
| where InitiatingProcessFileName contains "excel.exe"
| where InitiatingProcessCommandLine contains ".xlsm" or InitiatingProcessCommandLine contains ".xltm"
| where RemoteIPType == "Public"
//Exclude Microsoft telemetry endpoints 
| where RemoteUrl !endswith "outlook.com" 
    and RemoteUrl !endswith "office.com"
    and RemoteUrl !endswith "microsoft.com"
    and RemoteUrl !endswith "office365.com"
    and RemoteUrl !endswith "live.com"
    and RemoteUrl !endswith "office.net"
| project
    Timestamp,
    DeviceName,
    InitiatingProcessCommandLine,
    LocalIP,
    RemoteIP,
    RemotePort,
    RemoteUrl

//"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.

DeviceRegistryEvents
| where RegistryKey has @"SOFTWARE\Microsoft\Office\16.0\Excel\Security\Trusted Documents\TrustRecords"
| where RegistryValueName has "xlsm"
| project Timestamp, DeviceName, RegistryValueName

DeviceRegistryEvents
| where RegistryKey has @"SOFTWARE\Microsoft\Office\16.0\Word\Security\Trusted Documents\TrustRecords"
| where RegistryValueName has "docm"
| project Timestamp, DeviceName, RegistryValueName