How to filter a subquery

%3CLINGO-SUB%20id%3D%22lingo-sub-139354%22%20slang%3D%22en-US%22%3EHow%20to%20filter%20a%20subquery%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-139354%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20perform%20a%20subselect%20on%20a%20related%20set%20of%20data.%20That%20subdata%20needs%20to%20be%20filtered%20using%20data%20from%20the%20main%20query%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20customEvents%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20extend%20envId%20%3D%20tostring(customDimensions.EnvironmentId)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20extend%20organisation%20%3D%20tostring(customDimensions.OrganisationName)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20extend%20version%20%3D%20tostring(customDimensions.Version)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20extend%20app%20%3D%20tostring(customDimensions.Appname)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20where%20customDimensions.EventName%20contains%20%22ApiSessionStartStart%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20extend%20dbInfo%20%3D%20toscalar(%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20customEvents%20%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20extend%20dbInfo%20%3D%20tostring(customDimensions.dbInfo)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20extend%20serverEnvId%20%3D%20tostring(customDimensions.EnvironmentId)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20where%20customDimensions.EventName%20%3D%3D%20%22ServiceSessionStart%22%20or%20customDimensions.EventName%20%3D%3D%20%22ServiceSessionContinuation%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20where%20serverEnvId%20%3D%20envId%20%3CSTRONG%3E%2F%2F%20This%20gives%20and%20error%3C%2FSTRONG%3E%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20project%20dbInfo%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20take%201)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20order%20by%20timestamp%20desc%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20project%20timestamp%2C%20customDimensions.OrganisationName%2C%20customDimensions.Version%2C%20customDimensions.onBehalfOf%2C%20customDimensions.userId%2C%20customDimensions.Appname%2C%20customDimensions.apiKey%2C%20customDimensions.remoteIp%2C%20session_Id%20%2C%20dbInfo%2C%26nbsp%3B%20envId%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20above%20query%20results%20in%20an%20error%3A%3C%2FP%3E%0A%3CP%3E%26gt%3B%20%3CSTRONG%3EFailed%20to%20resolve%20entity%20'envId'%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EHow%20can%20I%20filter%20the%20data%20in%20the%20subselect%20based%20on%20the%20field%20%60envId%60%20in%20the%20main%20query%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-139354%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EApplication%20Insights%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-139973%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20filter%20a%20subquery%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-139973%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20seems%20to%20me%20like%20you're%20actually%20doing%20a%20join%2C%20so%20this%20might%20be%20easier%3A%3C%2FP%3E%0A%3CPRE%3EcustomEvents%0A%20%20%20%20%7C%20extend%20envId%20%3D%20tostring(customDimensions.EnvironmentId)%0A%20%20%20%20%7C%20extend%20organisation%20%3D%20tostring(customDimensions.OrganisationName)%0A%20%20%20%20%7C%20extend%20version%20%3D%20tostring(customDimensions.Version)%0A%20%20%20%20%7C%20extend%20app%20%3D%20tostring(customDimensions.Appname)%0A%20%20%20%20%7C%20where%20customDimensions.EventName%20contains%20%22ApiSessionStartStart%22%0A%20%20%20%20%7C%20join%20(%0A%20%20%20%20%20%20%20%20customEvents%20%0A%20%20%20%20%20%20%20%20%7C%20extend%20dbInfo%20%3D%20tostring(customDimensions.dbInfo)%0A%20%20%20%20%20%20%20%20%7C%20extend%20envId%20%3D%20tostring(customDimensions.EnvironmentId)%0A%20%20%20%20%20%20%20%20%7C%20where%20customDimensions.EventName%20%3D%3D%20%22ServiceSessionStart%22%20or%20customDimensions.EventName%20%3D%3D%20%22ServiceSessionContinuation%22%0A%20%20%20%20%20%20%20%20%7C%20take%201)%0A%20%20%20%20%20%20%20%20on%20envId%20%2F%2F%20This%20gives%20and%20error%20%0A%20%20%20%20%7C%20order%20by%20timestamp%20desc%0A%20%20%20%20%7C%20project%20timestamp%2C%20customDimensions.OrganisationName%2C%20customDimensions.Version%2C%20customDimensions.onBehalfOf%2C%20customDimensions.userId%2C%20customDimensions.Appname%2C%20customDimensions.apiKey%2C%20customDimensions.remoteIp%2C%20session_Id%20%2C%20dbInfo%2C%20%20envId%3C%2FPRE%3E%0A%3CP%3EI%20admit%20I%20might%20have%20misunderstood%20the%20meaning%20of%20your%20query%2C%20but%26nbsp%3Bjoined%20just%20seemed%20to%20pop.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Visitor

I want to perform a subselect on a related set of data. That subdata needs to be filtered using data from the main query:

 

    customEvents
    | extend envId = tostring(customDimensions.EnvironmentId)
    | extend organisation = tostring(customDimensions.OrganisationName)
    | extend version = tostring(customDimensions.Version)
    | extend app = tostring(customDimensions.Appname)
    | where customDimensions.EventName contains "ApiSessionStartStart"
    | extend dbInfo = toscalar(
        customEvents
        | extend dbInfo = tostring(customDimensions.dbInfo)
        | extend serverEnvId = tostring(customDimensions.EnvironmentId)
        | where customDimensions.EventName == "ServiceSessionStart" or customDimensions.EventName == "ServiceSessionContinuation"
        | where serverEnvId = envId // This gives and error
        | project dbInfo
        | take 1)
    | order by timestamp desc
    | project timestamp, customDimensions.OrganisationName, customDimensions.Version, customDimensions.onBehalfOf, customDimensions.userId, customDimensions.Appname, customDimensions.apiKey, customDimensions.remoteIp, session_Id , dbInfo,  envId

 

The above query results in an error:

> Failed to resolve entity 'envId'

How can I filter the data in the subselect based on the field `envId` in the main query?

1 Reply

It seems to me like you're actually doing a join, so this might be easier:

customEvents
    | extend envId = tostring(customDimensions.EnvironmentId)
    | extend organisation = tostring(customDimensions.OrganisationName)
    | extend version = tostring(customDimensions.Version)
    | extend app = tostring(customDimensions.Appname)
    | where customDimensions.EventName contains "ApiSessionStartStart"
    | join (
        customEvents 
        | extend dbInfo = tostring(customDimensions.dbInfo)
        | extend envId = tostring(customDimensions.EnvironmentId)
        | where customDimensions.EventName == "ServiceSessionStart" or customDimensions.EventName == "ServiceSessionContinuation"
        | take 1)
        on envId // This gives and error 
    | order by timestamp desc
    | project timestamp, customDimensions.OrganisationName, customDimensions.Version, customDimensions.onBehalfOf, customDimensions.userId, customDimensions.Appname, customDimensions.apiKey, customDimensions.remoteIp, session_Id , dbInfo,  envId

I admit I might have misunderstood the meaning of your query, but joined just seemed to pop.