Device Summarizemacrousage

KQL detection rule by reprise99 · MIT

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

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

[[ currentRule.title && currentRule.title.length > 60 ? currentRule.title.slice(0, 60) + '…' : currentRule.title ]]

5ac75021-f620-44c4-bd03-0cf6991b68e0

Propose edits, view related rules, and engage with the community through comments.

Executable code, not a declarative rule

This submission is arbitrary [[ currentRule.format ? currentRule.format.toUpperCase() : '' ]] code, not a declarative detection rule — review it as you would any script before running it, and note that it cannot be safely aggregated or auto-executed alongside YARA/Sigma/Suricata/CRS rules.

Rejected pattern present

[[ reason ]]

What does this mean?

Format & documentation suggestions

Not a security issue — this rule is valid and was accepted as-is. These are optional fields/conventions its own format recommends, mainly affecting its quality score and how well it documents itself for other reviewers.

[[ reason ]]

What do these messages mean?

[[ currentRule.title && currentRule.title.length > 60 ? currentRule.title.slice(0, 60) + '…' : currentRule.title ]]

[[ currentRule.title ]]

Community Discussion