You are correct the translation from Sigma to KQL (or any language) requires two things:
- The right data
- A understanding of best practise with KQL, the translation is often basic and needs to be refined.
Two tricks that can help is to fake the data until you have it
- See datatable operator : https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/datatableoperator?pivots=azuremonitor
- Also try adding column_ifexists - essentially using another column in the meantime - it won't fix it but can allow you to develop some of the rest of the query. You can also pass in data at that point (see second code block below): https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/columnifexists
// using another column like "Computer"
Event
| extend ScriptBlockText=column_ifexists("ScriptBlockText",Computer)
| where (ScriptBlockText contains 'Clear-History' or ((ScriptBlockText contains 'Remove-Item' or ScriptBlockText contains 'rm')
and (ScriptBlockText contains 'ConsoleHost_history.txt' or ScriptBlockText contains '(Get-PSReadlineOption).HistorySavePath')))
| limit 10
or
// using fake data
Event
| extend ScriptBlockText=column_ifexists("ScriptBlockText","Clear-History")
| where (ScriptBlockText contains 'Clear-History' or ((ScriptBlockText contains 'Remove-Item' or ScriptBlockText contains 'rm')
and (ScriptBlockText contains 'ConsoleHost_history.txt' or ScriptBlockText contains '(Get-PSReadlineOption).HistorySavePath')))
| limit 10
If this is helpful, please Accept the answer?