Home

How to filter a subquery

Highlighted
Peter Bons
Frequent 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
No Assignments Tab in Microsoft Teams
Nick Davies  in  Microsoft Teams  on
25 Replies
Accessing Team Calendar on iPhone
Abbas Jaffar Ali  in  Microsoft Teams  on
8 Replies
Inserting links
Jessica Carver  in  Microsoft Teams  on
13 Replies
How do I delete a conversation generated by connectors?
Fuji Ogawa  in  Microsoft Teams  on
4 Replies
Excel crashing
Eric Maillebiau  in  Microsoft Teams  on
7 Replies
Holy contacts Batman! (search contact list)
John Pan  in  Skype for Business Users  on
2 Replies