2

I am using this reference documentation for Application Insights.

How can I do a sub-select using the output of a different query?

//Query 1
Events 
| where  Timestamp >= ago(30min) and Data contains('SomeString')
| project TraceToken

//I would like to use the first query's output in the subselect here.
Events 
| where TraceToken in ({I would like to use the First query's output here.})

Is a join better in this scenario. Which would have better performance.

John Gardner
  • 24,225
  • 5
  • 58
  • 76
Praveen Reddy
  • 7,295
  • 2
  • 21
  • 43

1 Answers1

14

You can use let statement to achieve this.

Here is an example from the Analytics documentation, I hope this helps:

let topCities =  toscalar ( // convert single column to value
   requests
   | summarize count() by client_City 
   | top 4 by count_ 
   | summarize makeset(client_City));
requests
| where client_City in (topCities) 
| summarize count() by client_City;

Edit: By default the maximum number of elements returned by the makeset() function is 128. The MaxSetSize should be specified for larger dataSets.

smarty
  • 378
  • 3
  • 18
Dmitry Matveev
  • 2,563
  • 11
  • 16
  • Removed my post on using a join since this answer fills the OPs question – James Davis - MSFT Feb 15 '17 at 21:17
  • James Davis, Can you please also post the answer with the join. I would like to do a comparison of performance and see if a specific query type suits a situation. – Praveen Reddy Feb 15 '17 at 21:36
  • Dmitry, Thanks for your answer. It works great on a small Dataset. The query is timing out once I increase the date range. How can I make it more efficient for larger datasets. We have approximately half a billion Events per day. A query over a range of half a day is timing out. – Praveen Reddy Feb 15 '17 at 22:10
  • 1
    If you have a huge data set making it into TraceToken set and then each of the TraceToken in the second query goes through the whole set again and again - then you might want to use the join. `A | where | project TraceToken | join (B | where ) | project ) on TraceToken | `. The link in my answer above also contains examples of joins. – Dmitry Matveev Feb 15 '17 at 22:35