Microsoft Entra Suite Tech Accelerator
Aug 14 2024, 07:00 AM - 09:30 AM (PDT)
Microsoft Tech Community
SOLVED

mv-expand error on Security Alert

Brass Contributor

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?

 

 

3 Replies
best response confirmed by abon13 (Brass Contributor)
Solution
trying adding a column_ifexists

...
| extend Url = column_ifexists("Url","")
| mv-expand todynamic(Url)
| where isnotempty(Url)
| project TimeGenerated, SystemAlertId, AlertName, Url
thanks. this works

Curious to understand why KQL proceeds to the next query line when the where clause (line 3) comes up with blank results ?
if its empty/blank, then the query stops at the mv-expand line
1 best response

Accepted Solutions
best response confirmed by abon13 (Brass Contributor)
Solution
trying adding a column_ifexists

...
| extend Url = column_ifexists("Url","")
| mv-expand todynamic(Url)
| where isnotempty(Url)
| project TimeGenerated, SystemAlertId, AlertName, Url

View solution in original post