Forum Discussion

abon13's avatar
abon13
Brass Contributor
Oct 12, 2022

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_Watson's avatar
    Clive_Watson
    Bronze Contributor
    trying adding a column_ifexists

    ...
    | extend Url = column_ifexists("Url","")
    | mv-expand todynamic(Url)
    | where isnotempty(Url)
    | project TimeGenerated, SystemAlertId, AlertName, Url
    • abon13's avatar
      abon13
      Brass Contributor
      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 ?
      • Clive_Watson's avatar
        Clive_Watson
        Bronze Contributor
        if its empty/blank, then the query stops at the mv-expand line

Share

Resources