CODEWITHSUNDEEP
azure-data-explorerkqlazure-sentinel
user7696812
user7696812

Reputation: 15

How to correlate two entries when one of them is a number and the other is a range

I have two tables in Sentinel with data in them. I have field A in Table A that is a number. And I have two fields B and C in Table B which are also numbers but they represent a range. Like Table A field A contains the number '9', Table B field B contains the number '3' and Table B field C contains number '18'.

I would like to go through all of the entries in Table A and find the matching logs for these entries in Table B. An entry is matching if the value from field A is in the range of field B and C (between B and C). So, in the example above the number 9 is between 3 and 18, so these two entries from the two tables would match.

Because they are not exact matches I can't use join to find matching entries.

Is there a way to do this with KQL (Kusto) somehow? I tried multiple solutions but none of them worked out so far. I tried to use user-defined functions but I got an 'Tabular expression is not expected in the current context' error.

Upvotes: 1

Views: 529

Answers (1)

Avnera
Avnera

Reputation: 7618

A naive way would be to use Cartisian product and apply a filter, here is an example:

let A = datatable(a:int) [9, 25, 2];
let B = datatable(b:int, c:int) [3,13, 1,2];
A
| extend dummy = 1
| join kind=inner (B | extend dummy =1) on dummy
| where a between (b .. c)
| project-away dummy*
a b c
9 3 13
2 1 2

Upvotes: 3

Related Questions