CODEWITHSUNDEEP
azure-data-explorerexclude-constraint
Bala
Bala

Reputation: 37

Kusto How to filter Datetime, Integer, Double Values

We have requirement that we need to exclude the values which presents in the particular rows through Kusto query.

We need to exclude the values for Datetime, Integer and Double Dynamic dictionary through Kusto query.

For example we have below sample data for datetime

**Date Time** :- {"A": "2024-08-12T02:33:33.6740000Z","B": "2024-08-12T02:39:33.6740000Z",  "C": "2024-08-12T02:44:33.6740000Z"
},
{"A": "2024-08-13T02:33:33.6740000Z","B": "2024-08-13T02:39:33.6740000Z",   "C": "2024-08-13T02:44:33.6740000Z"
}

If we EXCEPT these value **"2024-08-12 02:33:33.6740000"** We need to exclude(First row - Datetime) whole set of data which contains the given value. In other words we need to exclude first row and return second row only.

 For example we have below sample data for Integer

**Integer:**- { "A": 2001,  "B": 2002,  "C": 2001,  "D": 2004,
    "E": 2005}
{   "A": 1000,  "B": 1002,  "C": 1001,  "D": 1004,  "E": 1005}

If we EXCEPT the value **"2001"** We need to exclude whole set of data(First row integer) which contains the given value. In other words we need to exclude first row and return second row only.

For example we have below sample data for Double

**Double** { "T1": 10.297,  "T2": -30.76217,    "T3": 9.3046781,"T4": 1.3959,   "T5": -5.2594 }
{   "T1": 1.297,    "T2": -1.76217,     "T3": 0.3046781,    "T4": 1.3959, 
    "T5": -2.2594
}
If we EXCEPT the value **"10.297"** We need to exclude whole set of data(First row double) which contains the given value. In other words we need to exclude first row and return second row only.

Please help in this regard.

Note:- All are dynamic data types.

All key values are string and values are corresponding to given datatypes as datetime, integer and double.

Thanks in advance.

Upvotes: 1

Views: 92

Answers (1)

Rakesh Govindula
Rakesh Govindula

Reputation: 11329

You can try the below approach for all of your data types.

This is for Datetime type data:

let result = 
print myDynamicValue = dynamic(
[
    {"A": "2024-08-12T02:33:33.6740000Z","B": "2024-08-12T02:39:33.6740000Z",   "C": "2024-08-12T02:44:33.6740000Z"},
    {"A": "2024-08-13T02:33:33.6740000Z","B": "2024-08-13T02:39:33.6740000Z",   "C": "2024-08-13T02:44:33.6740000Z"},
    {"A": "2024-08-14T02:33:33.6740000Z","B": "2024-08-14T02:39:33.6740000Z",   "C": "2024-08-14T02:44:33.6740000Z"}
]) 
| mvexpand myDynamicValue 
| evaluate bag_unpack(myDynamicValue);

result
| extend ar=pack_array(A,B,C)
| mv-apply Value = ar on (
    summarize check = countif(not(todatetime(Value) > datetime(2024-08-12 02:33:33.6740000) and todatetime(Value) < datetime(2024-08-13T02:44:33.6740000Z)))
)
| where check == array_length(ar)
| project-away ar,check;

The above code packs the values of the required columns and creates an array column. Then, each element in the array will be checked for the given range condition. At the end the if the value of the check is the same as the array length, then that particular row will be filtered.

Result:

enter image description here

You can follow the same approach for the remaining data types, but you need to modify this code as per those data types.

Upvotes: 0

Related Questions