Azure-docs: In Kusto - how do you create a datatable for a group of machines

[kewalaka]

Regarding the complex example on this page: "Summarize the outbound connections from a group of machines"

This includes a datatable to restrict the query to a subset of machines, but I've been unable to find out how to create this - i.e. how do I create the "m-*" string below?

// the machines of interest
let machines = datatable(m: string) ["m-82412a7a-6a32-45a9-a8d6-538354224a25"];

Is it possible to modify this example to show either how to generate the datatable, or to remove this and show it being done straight from "VMComputer"?

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 09644a50-743d-c067-9bec-d96a05f69094
• Version Independent ID: 844b0aec-c99e-7335-bb79-001189c36f64
• Content: How to Query Logs from Azure Monitor for VMs - Azure Monitor
• Content Source: articles/azure-monitor/insights/vminsights-log-search.md
• Service: azure-monitor
• Sub-service: **
• GitHub Login: @bwren
• Microsoft Alias: bwren

[KrishnaG-MSFT]

@kewalaka Thanks for the comment.

This includes a datatable to restrict the query to a subset of machines, but I've been unable to find out how to create this - i.e. how do I create the "m-*" string below?

"m-*" string is "AgentId with m- prepended" that's available for each of your VMComputer

Is it possible to modify this example to show either how to generate the datatable, or to remove this and show it being done straight from "VMComputer"?

In general, datatable operator doesn't have a pipeline input and it returns a table whose schema and values are defined in the query itself.

Summarize the outbound connections from a group of machines example is specifically intended for static group of machines that are of interest so i believe the examples serves its purpose.

However, if you are looking for similar query which takes input from VMComputer table instead of static Computer agent id's with m- prepended then below is the query for it.

// the machines of interest
let machines = (){
    VMComputer
    | distinct Machine
};
// map of ip to monitored machine in the environment
let ips=materialize(VMComputer
| summarize ips=makeset(todynamic(Ipv4Addresses)) by MonitoredMachine=AzureResourceName
| mvexpand ips to typeof(string));
// all connections to/from the machines of interest
let out=materialize(VMConnection
| where Machine in (machines)
| summarize arg_max(TimeGenerated, *) by ConnectionId);
// connections to localhost augmented with RemoteMachine
let local=out
| where RemoteIp startswith "127."
| project ConnectionId, Direction, Machine, Process, ProcessName, SourceIp, DestinationIp, DestinationPort, Protocol, RemoteIp, RemoteMachine=Machine;
// connections not to localhost augmented with RemoteMachine
let remote=materialize(out
| where RemoteIp !startswith "127."
| join kind=leftouter (ips) on $left.RemoteIp == $right.ips
| summarize by ConnectionId, Direction, Machine, Process, ProcessName, SourceIp, DestinationIp, DestinationPort, Protocol, RemoteIp, RemoteMachine=MonitoredMachine);
// the remote machines to/from which we have connections
let remoteMachines = remote | summarize by RemoteMachine;
// all augmented connections
(local)
| union (remote)
//Take all outbound records but only inbound records that come from either //unmonitored machines or monitored machines not in the set for which we are computing dependencies.
| where Direction == 'outbound' or (Direction == 'inbound' and RemoteMachine !in (machines))
| summarize by ConnectionId, Direction, Machine, Process, ProcessName, SourceIp, DestinationIp, DestinationPort, Protocol, RemoteIp, RemoteMachine
// identify the remote port
| extend RemotePort=iff(Direction == 'outbound', DestinationPort, 0)
// construct the join key we'll use to find a matching port
| extend JoinKey=strcat_delim(':', RemoteMachine, RemoteIp, RemotePort, Protocol)
// find a matching port
| join kind=leftouter (VMBoundPort 
| where Machine in (remoteMachines) 
| summarize arg_max(TimeGenerated, *) by PortId 
| extend JoinKey=strcat_delim(':', Machine, Ip, Port, Protocol)) on JoinKey
// aggregate the remote information
| summarize Remote=makeset(iff(isempty(RemoteMachine), todynamic('{}'), pack('Machine', RemoteMachine, 'Process', Process1, 'ProcessName', ProcessName1))) by ConnectionId, Direction, Machine, Process, ProcessName, SourceIp, DestinationIp, DestinationPort, Protocol

Similarly, if you are looking for query which takes input as Computer names (from VMComputer table) instead of static Computer agent id's with m- prepended then below is the query for it.

// the machines of interest
let machines = (){
    VMComputer
    | where Computer == "xxxxxxxxxxxxxxxxxxx"
    | distinct Machine
};
// map of ip to monitored machine in the environment
let ips=materialize(VMComputer
| summarize ips=makeset(todynamic(Ipv4Addresses)) by MonitoredMachine=AzureResourceName
| mvexpand ips to typeof(string));
// all connections to/from the machines of interest
let out=materialize(VMConnection
| where Machine in (machines)
| summarize arg_max(TimeGenerated, *) by ConnectionId);
// connections to localhost augmented with RemoteMachine
let local=out
| where RemoteIp startswith "127."
| project ConnectionId, Direction, Machine, Process, ProcessName, SourceIp, DestinationIp, DestinationPort, Protocol, RemoteIp, RemoteMachine=Machine;
// connections not to localhost augmented with RemoteMachine
let remote=materialize(out
| where RemoteIp !startswith "127."
| join kind=leftouter (ips) on $left.RemoteIp == $right.ips
| summarize by ConnectionId, Direction, Machine, Process, ProcessName, SourceIp, DestinationIp, DestinationPort, Protocol, RemoteIp, RemoteMachine=MonitoredMachine);
// the remote machines to/from which we have connections
let remoteMachines = remote | summarize by RemoteMachine;
// all augmented connections
(local)
| union (remote)
//Take all outbound records but only inbound records that come from either //unmonitored machines or monitored machines not in the set for which we are computing dependencies.
| where Direction == 'outbound' or (Direction == 'inbound' and RemoteMachine !in (machines))
| summarize by ConnectionId, Direction, Machine, Process, ProcessName, SourceIp, DestinationIp, DestinationPort, Protocol, RemoteIp, RemoteMachine
// identify the remote port
| extend RemotePort=iff(Direction == 'outbound', DestinationPort, 0)
// construct the join key we'll use to find a matching port
| extend JoinKey=strcat_delim(':', RemoteMachine, RemoteIp, RemotePort, Protocol)
// find a matching port
| join kind=leftouter (VMBoundPort 
| where Machine in (remoteMachines) 
| summarize arg_max(TimeGenerated, *) by PortId 
| extend JoinKey=strcat_delim(':', Machine, Ip, Port, Protocol)) on JoinKey
// aggregate the remote information
| summarize Remote=makeset(iff(isempty(RemoteMachine), todynamic('{}'), pack('Machine', RemoteMachine, 'Process', Process1, 'ProcessName', ProcessName1))) by ConnectionId, Direction, Machine, Process, ProcessName, SourceIp, DestinationIp, DestinationPort, Protocol

Hope this clarifies!

[kewalaka]

I think your latter options are better because it follows a more logical path through the queries, as opposed to introducing the datatable and 'm-*' string right at the end when you're already introducing a complex query.

Admitting I am still at the "Kusto by example" stage in experience!

However thanks - it's a very useful reply, happy for this to be closed.

PS - i found that I had to use 'hostname' for my example to work, i.e.:

let machines = (){
    VMComputer
    | where HostName == "xxxxxxxxxxxxxxxxxxx"
    | distinct Machine
<etc>