What is the conventional way of checking whether something Exists in another table in Application Insights Analytics Query Language?

Question

I want to limit requests by a specific associated dependency by name. I tried using a leftsemi join but that didn't seem to work as I expected because it game me the same results as my inner join.

requests
| where timestamp >= ago(24h)
| join kind=leftsemi (
    dependencies
    | where name contains "MYDATABASENAME" 
) on operation_Id 
| summarize count() by tostring(parseurl(url).Path)
| order by count_ desc

I'm looking at the the where-in statement next but I'm still unsure whether this is sort of the expected way to do what what typically be an exists statement in T-SQL.

See http://stackoverflow.com/questions/42258994/application-insights-analytics-doing-sub-selects — Peter Bons, Feb 17 '17 at 08:06

score 0 · Answer 1 · answered Feb 16 '17 at 19:43

0

You should be able to use let statement to achieve this.

answered Feb 16 '17 at 19:43

ManishKG

570
5
18

score 0 · Answer 2 · answered Feb 17 '17 at 18:12

Actually, in order to get the where-in semantics you should use inner join. From the documentation of join (at the kind=inner section):

There's a row in the output for every combination of matching rows from left and right.

In addition, since there's a limit on the size of the returned table, you might want to limit the right side of the join like this:

requests 
| where timestamp >= ago(24h)
| join kind=inner (
    dependencies
    | where name contains "MYDATABASENAME" 
    | project operation_Id
) on operation_Id 
| summarize count() by tostring(parseurl(url).Path)
| order by count_ desc

What is the conventional way of checking whether something Exists in another table in Application Insights Analytics Query Language?

2 Answers2