Home

How to filter a subquery

Highlighted
Peter Bons
Regular Visitor

How to filter a subquery

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

Re: How to filter a subquery

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.

 

Related Conversations