Query Details
Device Summarize Macro Usage
Query
//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
Explanation
This query is used to summarize the usage of macros on devices. It retrieves a list of all macros used, the number of users using each macro, and the account names. The query looks at data from the M365 Defender - Device* tables or requires an Advanced Hunting license. It filters for events related to Excel macros and parses the file path and name from the process. The results are then summarized by macro filename, showing the list of users and the count of users for each macro. The final output is sorted in descending order based on the count of users.
Details
Matt Zorich
Released: June 17, 2022
Tables
DeviceFileEventsDeviceNetworkEvents
Keywords
Keywords:Devices,Intune,User,Query,Macro,Usage,List,Macros,Count,Users,Account,DataConnector,M365Defender,Device*tables,DoubleCounted,MicrosoftSentinel,Union,DeviceFileEvents,DeviceNetworkEvents,TimeGenerated,InitiatingProcessCommandLine,InitiatingProcessAccountName,EXCEL.EXE,.xltm,.xlsm,Retrieve,Distinct,Process,Hash,Parse,FilePath,FileName,['MacroFilename'],Summarize,['ListofUsers'],make_set,['CountofUsers'],dcount,Sort,AdvancedHunting,Timestamp
Operators
unionwhereprojectdistinctparse-wheresummarizemake_setdcountsort by