Forum Discussion
abon13
Oct 12, 2022Brass Contributor
mv-expand error on Security Alert
Hi,
I have below query which I am using to perform a URL search in Security Alert table. This query works fine as long as the search value is there in the given timeframe (the below query search of 10days gives the intended results)
SecurityAlert
| where TimeGenerated >= ago(10d)
| where Entities has 'kh9bk.xyz'
| extend SecurityAlert_entities = todynamic(Entities)
| mv-expand SecurityAlert_entities
| evaluate bag_unpack(SecurityAlert_entities, columnsConflict='replace_source')
| mv-expand todynamic(Url)
| where isnotempty(Url)
| project TimeGenerated, SystemAlertId, AlertName, Url
However, if I shorten the search timeframe (lets say for 1day) and if the URL search value is not there in the table then this query throw's an error 'mvexpand' operator: Failed to resolve scalar expression named 'Url'
how can I improvise on the query so that it doesn't throw an error even if the search value is not found?
- trying adding a column_ifexists
...
| extend Url = column_ifexists("Url","")
| mv-expand todynamic(Url)
| where isnotempty(Url)
| project TimeGenerated, SystemAlertId, AlertName, Url
- Clive_WatsonBronze Contributortrying adding a column_ifexists
...
| extend Url = column_ifexists("Url","")
| mv-expand todynamic(Url)
| where isnotempty(Url)
| project TimeGenerated, SystemAlertId, AlertName, Url- abon13Brass Contributorthanks. this works
Curious to understand why KQL proceeds to the next query line when the where clause (line 3) comes up with blank results ?- Clive_WatsonBronze Contributorif its empty/blank, then the query stops at the mv-expand line