How to use a filter in subselect

Question

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?

score 1 · Accepted Answer · answered Jan 05 '18 at 20:13

i believe you'd need to use join instead, where you'd join to get that value from the second query

docs for join: https://docs.loganalytics.io/docs/Language-Reference/Tabular-operators/join-operator

the left hand side of the join is your "outer" query, and the right hand side of the join would be that "inner" query, though instead of doing take 1, you'd probably do a simpler query that just gets distinct values of serverEnvId, dbInfo

How to use a filter in subselect

1 Answers1