System Center Me – Blog by Jonathan Andersson

Export TCP connections for a server to Excel

I work with many customers that need to know how a server connects to other systems before migrating the VM to Azure.

I have created a query in Azure Log Analytics together with Service Map that maps all the TCP connections so it can expored an Excel spreadsheet. The query is based on a query at the Service Map docs so please have a look there to get more inspiration.

Copy and paste the following query in your Log Analytics query window, where you have Service Map enabled. This query will show all servers in the Log Analytics workspace and their connections. If there is a need to only show connections to one server, add the server name after “where Computer like” at the first line.

// the machines of interest
let machines = ServiceMapComputer_CL | where Computer like "" | distinct ResourceName_s;
// map of ip to monitored machine in the environment
let ips=materialize(ServiceMapComputer_CL
| summarize ips=makeset(todynamic(Ipv4Addresses_s)) by MonitoredMachine=ResourceName_s, MonitoredComputer=Computer
| 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, Computer, 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, Computer, Process, ProcessName, SourceIp, DestinationIp, DestinationPort, Protocol, RemoteIp, RemoteMachine=MonitoredMachine, RemoteComputer=MonitoredComputer);
// the remote machines to/from which we have connections
// 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, Computer, Process, ProcessName, SourceIp, DestinationIp, DestinationPort, Protocol, RemoteIp, RemoteMachine, RemoteComputer
// 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 Computer, Direction, ProcessName, SourceIp, DestinationIp, DestinationPort, Protocol, RemoteComputer

// the machines of interest

let machines = ServiceMapComputer_CL | where Computer like "" | distinct ResourceName_s;

// map of ip to monitored machine in the environment

let ips=materialize(ServiceMapComputer_CL

| summarize ips=makeset(todynamic(Ipv4Addresses_s)) by MonitoredMachine=ResourceName_s, MonitoredComputer=Computer

| 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, Computer, 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, Computer, Process, ProcessName, SourceIp, DestinationIp, DestinationPort, Protocol, RemoteIp, RemoteMachine=MonitoredMachine, RemoteComputer=MonitoredComputer);

// 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, Computer, Process, ProcessName, SourceIp, DestinationIp, DestinationPort, Protocol, RemoteIp, RemoteMachine, RemoteComputer

// 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 Computer, Direction, ProcessName, SourceIp, DestinationIp, DestinationPort, Protocol, RemoteComputer

Azure Role to start and stop a VM

Here is an example how to give start and stop access to a user on a VM in Azure. The role also gives read access to the VM.

{
  "Name": "Virtual Machine Start and Stop",
  "IsCustom": true,
  "Description": "Can start and stop virtual machines.",
  "Actions": [
    "Microsoft.Compute/*/read",
    "Microsoft.Compute/virtualMachines/start/action",
    "Microsoft.Compute/virtualMachines/deallocate/action",
    "Microsoft.Compute/virtualMachines/restart/action"
  ],
  "NotActions": [],
  "DataActions": [],
  "NotDataActions": [],
  "AssignableScopes": [
    "/subscriptions/{subscriptionid}"
  ]
}

{

"Name": "Virtual Machine Start and Stop",

"IsCustom": true,

"Description": "Can start and stop virtual machines.",

"Actions": [

"Microsoft.Compute/*/read",

"Microsoft.Compute/virtualMachines/start/action",

"Microsoft.Compute/virtualMachines/deallocate/action",

"Microsoft.Compute/virtualMachines/restart/action"

"NotActions": [],

"DataActions": [],

"NotDataActions": [],

"AssignableScopes": [

"/subscriptions/{subscriptionid}"

]

}

Import the role in Azure

Open Azure cloud shell and use bash. Type the command “code start_stop_vm.json”, paste the role-json-code and save it.

In the shell, run the following command: az role definition create –role-definition “start_stop_vm_role.json”

Add users to the role that only should have permissions to start and stop the VM.

For more inspiration see this links:
https://docs.microsoft.com/en-us/azure/role-based-access-control/tutorial-custom-role-cli
https://docs.microsoft.com/en-us/azure/role-based-access-control/custom-roles
https://docs.microsoft.com/en-us/azure/role-based-access-control/resource-provider-operations#microsoftcompute

Using parse in Azure Log Analytics to create fields in queries

This is an example of how to grab information from a string in Azure Log Analytics and create a field to be used later in a query. In the example I will show how to collect the SubjectUserName, SubjectDomainName, ObjectName and AccessMask from EventData in a SecurityEvent.

This is the query I have created with the command parse to collect the SubjectUserName, SubjectDomainName, ObjectName and AccessMask.

SecurityEvent
| parse EventData with * '<Data Name="SubjectUserName">' SubjectUserName "</Data>" *
| parse EventData with * '<Data Name="SubjectDomainName">' SubjectDomainName "</Data>" *
| parse EventData with * '<Data Name="ObjectName">' ObjectName "</Data>" *
| parse EventData with * '<Data Name="AccessMask">' AccessMask "</Data>" *
| where EventID == "4656"
| where ObjectName like "."
| where ObjectName like @"F:\shares"
| where AccessMask == "0x10080"
| sort by TimeGenerated 
| project TimeGenerated, UserName = SubjectUserName, DomainName = SubjectDomainName, File = ObjectName

SecurityEvent

| parse EventData with * '<Data Name="SubjectUserName">' SubjectUserName "</Data>" *

| parse EventData with * '<Data Name="SubjectDomainName">' SubjectDomainName "</Data>" *

| parse EventData with * '<Data Name="ObjectName">' ObjectName "</Data>" *

| parse EventData with * '<Data Name="AccessMask">' AccessMask "</Data>" *

| where EventID == "4656"

| where ObjectName like "."

| where ObjectName like @"F:\shares"

| where AccessMask == "0x10080"

| sort by TimeGenerated

| project TimeGenerated, UserName = SubjectUserName, DomainName = SubjectDomainName, File = ObjectName

Below are some EventData sample from SecurityEvent and under the sample are the results from the query.

<EventData xmlns=”http://schemas.microsoft.com/win/2004/08/events/event”> <Data Name=”SubjectUserSid”>S-1-5-21-1225449125-278733945-481909518-72603</Data> <Data Name=”SubjectUserName”>duck</Data> <Data Name=”SubjectDomainName”>CORP</Data> <Data Name=”SubjectLogonId”>0x1dac58</Data> <Data Name=”ObjectServer”>Security</Data> <Data Name=”ObjectType”>File</Data> <Data Name=”ObjectName”>F:\shares\1234\Folder2\myfile.txt</Data> <Data Name=”HandleId”>0xeac</Data> <Data Name=”TransactionId”>{00000000-0000-0000-0000-000000000000}</Data> <Data Name=”AccessList”>%%1537 %%4423 </Data> <Data Name=”AccessReason”>%%1537: %%1801 D:(A;ID;FA;;;BA) %%4423: %%1801 D:(A;ID;FA;;;BA) </Data> <Data Name=”AccessMask”>0x10080</Data> <Data Name=”PrivilegeList”>-</Data> <Data Name=”RestrictedSidCount”>0</Data> <Data Name=”ProcessId”>0x4</Data> <Data Name=”ProcessName”></Data> <Data Name=”ResourceAttributes”>-</Data> </EventData>

Results from the query.

Send Email when Azure Site Recovery is done or manual step is needed

This post is about sending an email when a Azure Site Recovery (ASR) failover is done or before a manual step in the ASR failover plan. In the example I have used an Azure Automation runbook in the ASR plan to send an email through the service SendGrid. SendGrid can off course be changed to another solution but in my case, I find it easy to use.

If you want to try it, start by creating a SendGrid account in Azure.

Make a note of the username and your password, you will need it later.

Create or import an Azure Automation Runbook that will send the email. This is the Runbook I used: SendEmail Runbook. Read the information in the description of the Runbook to get it working.

In the example Runbook above, an Azure Automation credential is needed. This is how it should look like. Add the username and password from the SendGrid account.

Edit the variables in the Runbook script and publish it.

Go to the Recovery Services vault and add the Runbook to the ASR plan.

Clone VMs after ASR Test Failover

If you want to clone a production environment on-prem to Azure and then, for example, test an upgrade or do new development on those servers, here is one way to do it.

My solution is using Azure Site Recovery (ASR) and a PowerShell script. It does not have any impact on the on-prem environment because I am using Test Failover in ASR which is starting the servers on a separate VNet in Azure which is not having any connectivity back on-prem. The Test Failover feature in ASR will make a clone of the on-prem servers in Azure and will not shut them down.

In ASR, as of today, you can only do one Test Failover at the time. This means that if you have done one Test Failover you cannot do another one while the first one is running. Because of this I am using a script in ASR to clone the Test Failover VMs so you can do more than one environment for testing.

Here is how I did it!

1. First step is to install and configure ASR to replicate the servers that is going to be cloned https://docs.microsoft.com/en-us/azure/site-recovery. When that is done, control that the servers are using Managed Disks. It they are not, change so they do.

2. Next step is to create an Azure Automation Account and a Runbook for cloning the servers. Here is the script I use: My GitHub. If you are using my script, change the variables to fit your needs.

If you have issues with the script, update the Azure Automation Account modules and import the modules that are needed. Here is a screenshot of the modules I have tested the script with.

3. When the servers have been replicated with ASR, create an ASR Recovery Plan and add the servers to a Group. Add the earlier created Runbook from Azure Automation as a Recovery Plan post step on the Group with the servers.

When this is done, do a Test Failover to test you Recovery Plan and clone of the servers.