Create a dynamic column with values that match a criteria - KQL

Question

I am trying to add values from a dynamic array of integers called utfChars to a new dynamic array called suspiciousCharactersDetected but only when they match a specific criteria.

The criteria is that if the value is in the range defined by the suspiciousCharacters variable then it should be in suspiciousCharactersDetected else nothing.

How would I do this?

Here is what I have tried so far. When I run this, it prints out all of the values in the dynamic array and not just the "suspicious" ones.

let suspiciousCharacters = range(126,1327,1);
datatable(Column1: dynamic)
[
    dynamic([1,2,3,4,5,150,160])
]
| where Column1 has_any (suspiciousCharacters)
//Missing something here?
| mv-expand suspiciousCharactersDetected = Column1 to typeof(int)
| summarize SuspiciousCharacterDetected=makeset(suspiciousCharactersDetected)

Answer 1

you could use the set_intersect() function.

for example:

let suspiciousCharacters = range(126, 1327, 1);
datatable(utfChars: dynamic)
[
    dynamic([1, 2, 3, 4, 5, 150, 160]),
    dynamic([1, 2, 3, 4, 5, 15, 16]),
    dynamic([1300, 1400, -1700])
]
| extend suspiciousCharactersDetected = set_intersect(suspiciousCharacters, utfChars)
| where array_length(suspiciousCharactersDetected) > 0

utfChars	suspiciousCharactersDetected
[ 1, 2, 3, 4, 5, 150, 160 ]	[ 150, 160 ]
[ 1300, 1400, -1700 ]	[ 1300 ]

or, you could use the mv-apply operator, and the make_set() aggregation function.

for example:

let suspiciousCharacters = range(126, 1327, 1);
datatable(utfChars: dynamic)
[
    dynamic([1, 2, 3, 4, 5, 150, 160]),
    dynamic([1, 2, 3, 4, 5, 15, 16]),
    dynamic([1300, 1400, -1700])
]
| where utfChars has_any (suspiciousCharacters)
| mv-apply c = utfChars to typeof(int) on (
    where c in(suspiciousCharacters)
    | summarize suspiciousCharactersDetected = make_set(c)
)

utfChars	suspiciousCharactersDetected
[ 1, 2, 3, 4, 5, 150, 160 ]	[ 150, 160 ]
[ 1300, 1400, -1700 ]	[ 1300 ]