Finding requests and the dependency call

Question

Assuming things are setup correctly to log sql calls on requests from an asp.net core app, Im trying to see the request from an asp.net controller that was associated with a given sql call. However, Im not sure on the join condition. Should I join on operation_parentId of the dependency to the operationId of the request? If I do, I get nothing. What is the "tie" between the dependency and the request that called it?

eg. Something like this

// join on operationid
    requests 
    |where timestamp > start_time and timestamp < end_time
    | join (dependencies
            | where ['type'] == "SQL" and data has "some-field-name-here" 
            ) on operation_Id
    
//join on parentid ????     
    requests 
    |where timestamp > start_time and timestamp < end_time
    | join (dependencies
            | where ['type'] == "SQL" and data has "some-field-name-here" 
            ) on $left.operation_Id = $right.operationParentId

The 2nd query gives me nothing, but the requests in the 1st query, if I look at the code that supposedly made the associated sql call, there is no statements that in the associated method of that request that would have made that sql call. What am I missing?

Answer 1

The easiest way of getting all the related telemetry items is by joining on the operation_Id like you did. But be aware there might be some nesting. Lets assume that we have the following situation:

• API Controller
  • Some Dependency
    • Sql Dependency
  • Some Other Dependency

With your query you won't see the "Some dependency" that contains the code that generates the "Sql Dependency" because you filtered out all telemetry not of type "SQL".

You can also walk throught the tree using the Id and operation_ParentId. For example, to get the first level dependencies ("Some Dependency" & "Some Other Dependency") you do:

requests 
    |where timestamp > start_time and timestamp < end_time
    | join (dependencies
            | where ['type'] == "SQL" and data has "some-field-name-here" 
            ) on $left.id == $right.operationParentId

Here are the docs regarding the data model of the correlation.