KQL exclude blank rows from query output

Question

How would I exclude blank rows from the my virtualmachine column in my

securityresources
| where type == "microsoft.security/assessments"
| project id = tostring(id),
          //DisplayName = properties.displayName,
          Description = properties.metadata.description,
          Severity = properties.metadata.severity,
          Remediations = properties.metadata.remediationDescription
          //UserImpact = properties.metadata.userImpact
| parse kind=regex id with '/virtualmachines/' virtualmachine '/providers/'
| project virtualmachine, Severity, Description,  Remediations

you can make use of the function `isnotemtpy(columnName)` and for more information you can refer to this documentation https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/isnotemptyfunction — VenkateshDodda, Aug 01 '22 at 07:12

score 0 · Answer 1 · answered Aug 01 '22 at 07:44

Use parse-where to keep only the rows that were parsed correctly.

In addition -

your parse expression needed * at the beginning and at the end.
No need for regex.

securityresources
| where type == "microsoft.security/assessments"
| project id = tostring(id),
          DisplayName = properties.displayName,
          Description = properties.metadata.description,
          Severity = properties.metadata.severity,
          Remediations = properties.metadata.remediationDescription,
          UserImpact = properties.metadata.userImpact
| parse-where id with * "/virtualmachines/" virtualmachine "/providers/" *
| project virtualmachine, Severity, Description,  Remediations

KQL exclude blank rows from query output

1 Answers1