FROM logs-windows.powershell_operational* metadata _id, _version, _index
| WHERE event.code == "4104"

// Look for scripts with more than 500 chars that contain a related keyword
| EVAL script_len = LENGTH(powershell.file.script_block_text)
| WHERE script_len > 500
| WHERE powershell.file.script_block_text LIKE "*{0}*"

// Replace string format expressions with 🔥 to enable counting the occurrence of the patterns we are looking for
// The emoji is used because it's unlikely to appear in scripts and has a consistent character length of 1
| EVAL replaced_with_fire = REPLACE(powershell.file.script_block_text, """((\{\d+\}){2,}["']\s?-f|::Format[^\{]+(\{\d+\}){2,})""", "🔥")

// Count how many patterns were detected by calculating the number of 🔥 characters inserted
| EVAL count = LENGTH(replaced_with_fire) - LENGTH(REPLACE(replaced_with_fire, "🔥", ""))

// Keep the fields relevant to the query, although this is not needed as the alert is populated using _id
| KEEP count, replaced_with_fire, powershell.file.script_block_text, powershell.file.script_block_id, file.path, powershell.sequence, powershell.total, _id, _index, host.name, agent.id, user.id
| WHERE count > 3