Kusto Query Language

• Count will return the number of rows in the query
• Where allows filtering the rows that are returned based on a condition
• Take will return a specified number of rows, but no guarantee which rows are returned
• Sort will allow sorting the output into an order
• Top will return the first N records sorted by the specified column
• Extend command allows creating new columns from existing columns or other data such as hard-coded values
• Summarize will return the total values for a specific grouping of rows

Create List

let set1 = toscalar (AzureNetworkAnalytics_CL | take 1 | project FlowCount_d);
print set1

Compare two lists

let history = dynamic (['20.150.9.36','20.50.65.82']);
let current = dynamic (['20.150.9.36','20.50.65.82', '10.0.0.10']);
print set_difference(current, history)

let startdate = ago(1h);
let current = toscalar(AzureNetworkAnalytics_CL
 | where FlowStartTime_t > startdate
 | where SubType_s == "FlowLog" and FlowDirection_s == "O"
 | extend DestinationIP = extract("(([0-9]{1,3})\\.([0-9]{1,3})\\.([0-9]{1,3})\\.(([0-9]{1,3})))", 1, DestPublicIPs_s)
 | distinct DestinationIP
 | summarize make_list(DestinationIP));
let history = toscalar (AzureNetworkAnalytics_CL
 | where FlowStartTime_t <= startdate and FlowStartTime_t > ago(2d)
 | where SubType_s == "FlowLog" and FlowDirection_s == "O"
 | extend DestinationIP = extract("(([0-9]{1,3})\\.([0-9]{1,3})\\.([0-9]{1,3})\\.(([0-9]{1,3})))", 1, DestPublicIPs_s)
 | distinct DestinationIP
 | summarize  make_list(DestinationIP));
print set_difference(current, history)