Device-SummarizeMacroUsage.kql

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