How do you do a distinct query with a criteria to find a specific substring in Kusto?

user_1678772 0
Viewed 137

I am a beginner at Kusto and am trying to create a query that returns a distinct (fruit) column based on another (data) column containing a specific substring.

In the example below, if an "awesome" substring is found in the data column then return "found" that and if not, return "not found" but always maintain the distinctness of the first column.

let Fruit = datatable(fruit:string, data:string) 
[
    "apple", "awesome",
    "apple", "beast",
    "banana", "a beast",
    "orange", "awesome cat",
    "orange", "blah" 
];

So the desired output would be:

"apple", "found",
"banana", "not found",
"orange", "found",
kusto-explorer kql azure-data-explorer
1 Answers

one option would be using the countif() aggregation function, with fruit as the aggregation key, as follows:

datatable(fruit:string, data:string) 
[
    "apple", "awesome",
    "apple", "beast",
    "banana", "a beast",
    "orange", "awesome cat",
    "orange", "blah" 
]
| summarize countif(data has 'awesome') by fruit
| project fruit, output = iff(countif_ == 0, "not found", "found")
fruit output
apple found
banana not found
orange found
user_78618070
Related