Kusto sub query selection using toscalar - returns only last matching record

Question

I am referring sqlcheatsheet - Nested queries

Query 1:

traces
    | where customDimensions.Domain == "someDomain"
    | where message contains "some-text" 
    | project itemId=substring(itemId,indexof(itemId,"-"),strlen(itemId))

Result :

itemId

-c580-11e9-888a-8776d3f65945
-c580-11e9-888a-8776d3f65945
-c580-11e9-9b01-c3be0f4a2bf2

Query 2:

traces
    | where customDimensions.Domain == "someDomain"
    | where itemId has toscalar(
    traces
        | where customDimensions.Domain == "someDomain"
        | where message contains "some-text"  
        | project itemId=substring(itemId,indexof(itemId,"-"),strlen(itemId)))

Result for the second query returns records matching only last record of sub query

ie:) > -c580-11e9-9b01-c3be0f4a2bf2

Question :

How get entire result set that has matching with all the three items.

My requirement is to take entire sequence of logs for a particular request.

To get that I have below inputs, I could able to take one log, from that I can find ItemId

The itemId looks like "b5066283-c7ea-11e9-9e9b-2ff40863cba4". Rest of all logs related to this request must have "-c7ea-11e9-9e9b-2ff40863cba4" this value. Only first part will get incremented like b5066284 , b5066285, b5066286 like that.

Answer 1

toscalar(), as its name implies, returns a scalar value.

Given a tabular argument with N columns and M rows it'll return the value in the 1st column and the 1st row.

For example: the following will return a single value - 1

let T = datatable(a:int, b:int, c:int)
[
    1,2,3,
    4,5,6,
    7,8,9,
]
;
print toscalar(T)

If I understand the intention in your 2nd query correctly, you should be able to achieve your requirement by using has_any.

For example:

let T = datatable(item_id:string)
[
    "c580-11e9-888a-8776d3f65945",
    "c580-11e9-888a-8776d3f65945",
    "c580-11e9-9b01-c3be0f4a2bf2",
]
;
T
| where item_id has_any (
    (
        T
        | parse item_id  with * "-" item_id
    )
)