[SOLVED] KQL: How to reference columns within a let query in the next query

KQL: How to reference columns within a let query in the next query

I want to be able to reference min_TimeGenerated, max_TimeGenerated, and LocalIP without having to write 3 let queries and project each one. What's the best practice on how to do this?

let TimeRange = DeviceNetworkEvents
| search "10.10.10.10" 
| summarize min(TimeGenerated), max(TimeGenerated) by LocalIP
| take 1
| project min_TimeGenerated, max_TimeGenerated, LocalIP;
DeviceNetworkInfo
| where TimeGenerated between(TimeRange.min_TimeGenerated .. TimeRange.max_TimeGenerated) // how do i reference the min and max date time
| search TimeRange.LocalIP // how do I search for an item from let

Solution

Here's a solution. bag_upack will make it all one entity. toscalar the whole query will allow us to reference the entities within. Lastly to use the items we have to turn them back to the object they were in with todatetime and tostring. the search operator doesnt worth in this method so we have to result in using | where * contains "".

This is faster than writing many let queries. There maybe better solutions.

let TimeRange = toscalar(DeviceNetworkEvents
| search "10.10.10.10" 
| summarize min(TimeGenerated), max(TimeGenerated) by LocalIP
| take 1
| project tr = bag_pack("Min",min_TimeGenerated,"Max", max_TimeGenerated, "IP",LocalIP));
DeviceNetworkInfo
| where TimeGenerated between(todatetime(TimeRange.Min) .. todatetime(TimeRange.Max)) 
| where * contains tostring(TimeRange.IP)