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.
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 |