Excel – correctly sort IP addresses

This post is probably for pedants only, who care passionately about correctly sorting IP addresses in an Excel spreadsheet. This approach uses pure functions – no VBA. I prefer it to some other approaches because, frankly, they sail right over my head.

Let’s start with a column of IP addresses – like this one:

Excel tables are lovely, for working with data like this. If you convert your data to a table, you get to use named column references, which we’ll see in a moment. Go to Insert > Table and you get something like this:

You can’t sort this column meaningfully, as-is. We need an additional column, which we’ll use to transform the contents of the IP column.

And then in any of the rows in that column, we enter this formula:

=
IF(0,"##### FIRST OCTET #####","") &
TEXT(
  LEFT(
    [@IP],
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        1
      )
    ) - 1
  ),
  "000"
)
& "." &
IF(0,"##### SECOND OCTET #####","") &
TEXT(
  MID(
    [@IP],
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        1
      )
    ) + 1,
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        2
      )
    )
    -
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        1
      )
    )
  ),
  "000"
)
& "." &
IF(0,"##### THIRD OCTET #####","") &
TEXT(
  MID(
    [@IP],
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        2
      )
    ) + 1,
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        3
      )
    )
    -
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        2
      )
    )
  ),
  "000"
)
& "." &
IF(0,"##### FOURTH OCTET #####","") &
TEXT(
  MID(
    [@IP],
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        3
      )
    ) + 1,
    
    IF(
      ISERROR(
        FIND("/",[@IP])
      ),
      LEN([@IP]),
      FIND("/",[@IP]) - 1
    )    
    -
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        3
      )
    )
  ),
  "000"
)
&
IF(0,"##### CIDR #####","") &
IF(
  ISERROR(FIND("/",[@IP])),
  "",
  RIGHT(
    [@IP],
    LEN([@IP]) - FIND("/",[@IP]) + 1
  )
)

You end up with this, on which you can now perform an alphabetical (A-Z) sort:

If you like, you can hide that column, so you don’t need to look at its hideousness. Then whenever you need to resort, go to Data > Sort.

Some things to mention about this formula:

  • [@IP] is the named column reference I referred to previously.
  • I edited this formula in a code editor (Notepad++), so I could nicely indent and keep track of opened and closed parenthesis. This makes life much easier, when writing long formulae! There’s one gotcha – Notepad++ by default uses tabs rather than spaces, which breaks Excel. Make sure there are no tab characters in your indentation.
  • The IF(0,"##### THIRD OCTET #####","") stuff is a hack, which allows you to insert a comment into a text-based formula. The 0 evaluates to FALSE, so it returns the function’s third parameter – an empty string. The second parameter is where I place my comment. Handy!
  • Excel doesn’t have a function to find the position of the nth occurrence of a string. So there’s a nifty two-step hack for this, which is not my original idea. First, we use the SUBSTITUTE() function, which can substitute a character for the nth occurrence of some text. We search for the nth occurrence of the full stop (“.”) and replace it with CHAR(134) – the dagger symbol (†). Then we find the position of that CHAR(134), to feed into the LEFT()/MID()/RIGHT() functions.
  • The formula handles CIDR notation.

Script to clone a VM with free VMware ESXi

clones

Many people run free versions of ESXi, particularly in lab environments. Unfortunately with the free version of ESXi, the VMware API is read-only. This limits (or complicates) automation.

I was looking for a way to clone guest VMs with the minimum of effort. This script, which took inspiration from many sources on the internet, is the result. It takes advantage of the fact that although the API is limited, there are plenty of actions you can take via SSH, including calls to vim-cmd.

<#

.SYNOPSIS
    Clones a VM,

.DESCRIPTION
    This script:

    - Retrieves a list of VMs attached to the host
    - Enables the user to choose which VM to clone
    - Clones the VM

    It must be run on a Windows machine that can connect to the virtual host.

    This depends on the Posh-SSH and PowerCLI modules, so from an elevated
    PowerShell prompt, run:

        Install-Module PoSH-SSH
        Install-Module VMware.PowerCLI

    For free ESXi, the VMware API is read-only. That limits what we can do with
    PowerCLI. Instead, we run certain commands through SSH. You will therefore
	need to enable SSH on the ESXi host before running this script.
	
	The script only handles simple hosts with datastores under /vmfs. And it
	clones to the same datastore as the donor VM. Your setup and requirements
	may be more complex. Adjust the script to suit.

.EXAMPLE
    From a PowerShell prompt:

      .\New-GuestClone.ps1 -ESXiHost 192.168.101.100

.COMPONENT
    VMware scripts

.NOTES
    This release:

        Version: 1.0
        Date:    8 July 2021
        Author:  Rob Pomeroy

    Version history:

        1.0 - 8 July 2021 - first release

#>
param
(
    [Parameter(Mandatory = $true, Position = 0)][String]$ESXiHost
)

####################
## INITIALISATION ##
####################

# Load necessary modules
Write-Host Loading PowerShell modules...
Import-Module PoSH-SSH
Import-Module VMware.PowerCLI

# Change to the directory where this script is running
Push-Location -Path ([System.IO.Path]::GetDirectoryName($PSCommandPath))


#################
## CREDENTIALS ##
#################

# Check for the creds directory; create it if it doesn't exist
If(-not (Test-Path -Path '.\creds' -PathType Container)) {
    New-Item -Path '.\creds' -ItemType Directory | Out-Null
}

# Looks for credentials file for the VMware host. Passwords are stored encrypted
# and will only work for the user and machine on which they're stored.
$credsFile = ('.\creds\' + $ESXiHost + '.creds')
If(-not (Test-Path -Path $credsFile)) {
    # Request credentials
    $creds = Get-Credential -Message "Enter root password for VMware host $ESXiHost" -User root
    $creds.Password | ConvertFrom-SecureString | Set-Content $credsFile
}
$ESXICredential = New-Object System.Management.Automation.PSCredential( `
    "root", `
    (Get-Content $credsFile | ConvertTo-SecureString)
)


#########################
## List VMs (PowerCLI) ##
#########################
#
# Disable HTTPS certificate check (not strictly needed if you use -Force) in
# later calls.
Set-PowerCLIConfiguration -InvalidCertificateAction Ignore -Confirm:$false | Out-Null

# Connect to the ESXi server
Connect-VIServer -Server $ESXiHost -Protocol https -Credential $ESXICredential -Force | Out-Null
If(-not $?) {
    Throw "Connection to ESXi failed. If password issue, delete $credsFile and try again."
}

# Get all VMs, sorted by name
$guests = (Get-VM -Server $ESXiHost | Sort-Object)

# Work out how much we need to left-pad the array index, when outputting
$padWidth = ([string]($guests.Count - 1)).Length

# Output the list of VMs, with array index padded so it lines up nicely
Write-Host ("Existing VMs (" + $guests.Count + "), sorted by name:")
for ( $i = 0; $i -lt $guests.count; $i++)
{
    If($guests[$i].PowerState -eq "PoweredOn") {
        Write-Host -ForegroundColor Red ("[" + "$i".PadLeft($padWidth, ' ') + "](ON) : " + $guests[$i].Name) 
    } Else {
        Write-Host ("[" + "$i".PadLeft($padWidth, ' ') + "](off): " + $guests[$i].Name) 
    }
}
Write-Host


##########################
## Choose a VM to clone ##
##########################

$chosenVM = 0
do {
    $inputValid = [int]::TryParse((Read-Host 'Enter the [number] of the VM to clone (the donor)'), [ref]$chosenVM)
    if($chosenVM -lt 0 -or $chosenVM -ge $guests.Count) {
        $inputValid = $false
    }
    if (-not $inputValid) {
        Write-Host ("Must be a number in the range 0 to " + ($guests.Count - 1).ToString() + ". Try again.")
    }
} while (-not $inputValid)

# Check the VM is powered off
if($guests[$chosenVM].PowerState -ne "PoweredOff") {
    Throw "ERROR: VM must be powered off before cloning"
}

# Get VM's datastore, directory and VMX; we assume this is at /vmfs/volumes
If(-not ($guests[$chosenVM].ExtensionData.Config.Files.VmPathName -match '\[(.*)\] ([^\/]*)\/(.*)')) {
    Throw "ERROR: Could not calculate the datastore"
}
$VMdatastore = $Matches[1]
$VMdirectory = $Matches[2]
$VMXlocation = ("/vmfs/volumes/" + $VMdatastore + "/" + $VMdirectory + "/" + $Matches[3])
$VMdisks     = $guests[$chosenVM] | Get-HardDisk


###############################
## File test (PoSH-SSH SFTP) ##
###############################

# Clear any open SFTP sessions
Get-SFTPSession | Remove-SFTPSession | Out-Null

# Start a new SFTP session
(New-SFTPSession -Computername $ESXiHost -Credential $ESXICredential -Acceptkey -Force -WarningAction SilentlyContinue) | Out-Null

# Test that we can locate the VMX file
If(-not (Test-SFTPPath -SessionId 0 -Path $VMXlocation)) {
    Throw "ERROR: Cannot find donor VM's VMX file"
}


#################
## New VM name ##
#################

$validInput = $false
While(-not $validInput) {
    $newVMname = Read-Host "Enter the name of the new VM"
    $newVMdirectory = ("/vmfs/volumes/" + $VMdatastore + "/" + $newVMname)

    # Check if the directory already exists
    If(Test-SFTPPath -SessionId 0 -Path $newVMdirectory) {
        $ynTest = $false
        While(-not $ynTest) {
            $yn = (Read-Host "A directory already exists with that name. Continue? [Y/N]").ToUpper()
            if (($yn -ne 'Y') -and ($yn -ne 'N')) {
                Write-Host "ERROR: enter Y or N"
            } else {
                $ynTest = $true
            }
        }
        if($yn -eq 'Y') {
            $validInput = $true
        } else {
            Write-Host "You will need to choose a different VM name."
        }
    } else {
        If($newVMdirectory.Length -lt 1) {
            Write-Host "ERROR: enter a name"
        } else {
            $validInput = $true

            # Create the directory
            New-SFTPItem -SessionId 0 -Path $newVMdirectory -ItemType Directory | Out-Null
        }
    }
}


###################################
## Copy & transform the VMX file ##
###################################

# Clear all previous SSH sessions
Get-SSHSession | Remove-SSHSession | Out-Null

# Connect via SSH to the VMware host
(New-SSHSession -Computername $ESXiHost -Credential $ESXICredential -Acceptkey -Force -WarningAction SilentlyContinue) | Out-Null

# Replace VM name in new VMX file
Write-Host "Cloning the VMX file..."
$newVMXlocation = $newVMdirectory + '/' + $newVMname + '.vmx'
$command = ('sed -e "s/' + $VMdirectory + '/' + $newVMname + '/g" "' + $VMXlocation + '" > "' + $newVMXlocation + '"')
($commandResult = Invoke-SSHCommand -Index 0 -Command $command) | Out-Null

# Set the display name correctly (might be wrong if donor VM name didn't match directory name)
$find    = 'displayName \= ".*"'
$replace = 'displayName = "' + $newVMname + '"'
$command = ("sed -i 's/$find/$replace/' '$newVMXlocation'")
($commandResult = Invoke-SSHCommand -Index 0 -Command $command) | Out-Null

# Blank the MAC address for adapter 1
$find    = 'ethernet0.generatedAddress \= ".*"'
$replace = 'ethernet0.generatedAddress = ""'
$command = ("sed -i 's/$find/$replace/' '$newVMXlocation'")
($commandResult = Invoke-SSHCommand -Index 0 -Command $command) | Out-Null


#####################
## Clone the VMDKs ##
#####################

Write-Host "Please be patient while cloning disks. This can take some time!"
foreach($VMdisk in $VMdisks) {
    # Extract the filename
    $VMdisk.Filename -match "([^/]*\.vmdk)" | Out-Null
    $oldDisk = ("/vmfs/volumes/" + $VMdatastore + "/" + $VMdirectory + "/" + $Matches[1])
    $newDisk = ($newVMdirectory + "/" + ($Matches[1] -replace $VMdirectory, $newVMname))

    # Clone the disk
    $command = ('/bin/vmkfstools -i "' + $oldDisk + '" -d thin "' + $newDisk + '"')
    Write-Host "Cloning disk $oldDisk to $newDisk with command:"
    Write-Host $command
    # Set a timeout of 10 minutes/600 seconds for the disk to clone
    ($commandResult = Invoke-SSHCommand -Index 0 -Command $command -TimeOut 600) | Out-Null
    #Write-Host $commandResult.Output
   
}


########################
## Register the clone ##
########################

Write-Host "Registering the clone..."
$command = ('vim-cmd solo/register "' + $newVMXlocation + '"')
($commandResult = Invoke-SSHCommand -Index 0 -Command $command) | Out-Null
#Write-Host $commandResult.Output


##########
## TIDY ##
##########

# Close all connections to the ESXi host
Disconnect-VIServer -Server $ESXiHost -Force -Confirm:$false
Get-SSHSession | Remove-SSHSession | Out-Null
Get-SFTPSession | Remove-SFTPSession | Out-Null

# Return to previous directory
Pop-Location

You can download the latest version of the script from my GitHub repository.

Cover photo by Dynamic Wang on Unsplash

Using a Canon EOS 60D as a webcam

Canon EOS 60D
At the time of writing, the excellent EOS 90D is the modern equivalent of my 60D. I really want a Sony Alpha though! In the interests of transparency: these are affiliate links. See my affiliate disclosure page for an explanation.

Necessity is the mother of invention. In the midst of the trauma and struggles of coronavirus, one positive theme has consistently emerged: innovation. In particular, the explosive rise of home working, podcasting and vlogging has resulted in significant improvements in associated technology.

So when I recently started researching ways to raise my webcam game (for conference calls and church meetings), I was spoilt for choice. Since I’m a fan of both cost-efficiency and quality, I was particularly interested in seeing what could be achieved with my existing equipment, including my faithful DSLR camera – a Canon EOS 60D.

The last time I looked into this, the main way to take a feed from this camera model, was through its HDMI port and a separate video capture device (which I don’t own). But Canon has pulled an innovation blinder, releasing and improving its EOS Webcam Utility and ensuring that it not only works with the latest hardware, but also with such aging models as my ten-year-old 60D. Oh Canon, I love you.

“There must be a catch,” I thought, as I read reports of camera sensors overheating, or timing out after 30 minutes. So with no great expectations, I downloaded and tested the software.

Oh. My. Word. Did I mention Canon how I love you?

Screenshot of a Zoom session using the EOS webcam utility, my Canon EOS 60D and a 17-55 f/2.8 lens.

With zero effort and no tweaking of camera settings, the improvement was immediately visible. From the screenshot you can see I need to work on contrast and lighting. But for a first test, this made me very happy.

Did the camera overheat or timeout? No. I ran the session for about two hours. The camera was slightly warm at the end. Granted it’s not a hot summer’s day here in the UK (is it ever!) but it looks to me like this setup would work all day, every day. The only snag is battery life. So instead of being on the market for a superior webcam, I’m instead on the market for an external power supply (like Canon’s ACK-E6). Clones of the OEM adapter are available for about £23. Bargain.

Photo of EOS 60D courtesy of John Torcasio, CC BY-SA 3.0, via Wikimedia Commons.

SOLVED: first-time login problems when enforcing MFA with AWS

If you’re reading this page, you probably already have an MFA strategy sorted. But for those still making decisions: I love my YubiKey 5 NFC. I use it constantly to log into AWS, to secure my GitHub account, to protect my “I would be completely ruined if it were hacked” email account, etc. My wife was extremely puzzled when I asked for one for my birthday. She didn’t really know what it was she was buying me. What a great present though!

In the interests of transparency: this is an affiliate link. See my affiliate disclosure page for an explanation.

AWS has a tutorial about enforcing MFA for all users. The general thrust of the article is to create a policy that allows users without MFA to do nothing other than log in and set up MFA. Having enabled and logged in using MFA, other permissions become available to the user (according to whatever other permissions are assigned).

This works well apart from one snag: having created a user, and set the flag forcing the user to change password on first login, the user cannot log in. Instead the user is greeted with the following error:

Either user is not authorized to perform iam:ChangePassword or entered password does not comply with account password policy set by administrator

The problem lies in a policy statement called “DenyAllExceptListedIfNoMFA”. As its name suggests, for a user without MFA, this blocks all bar the specified actions. In AWS’s recommended policy, the section effectively allows the following actions:

"iam:CreateVirtualMFADevice",
"iam:EnableMFADevice",
"iam:GetUser",
"iam:ListMFADevices",
"iam:ListVirtualMFADevices",
"iam:ResyncMFADevice",
"sts:GetSessionToken"

You’ll notice that those actions don’t include anything about changing a password! So without MFA already enabled on your account, there’s no way to change your password when first logging on (if “force password change” is enabled). The trick is to add two more permissions:

"iam:ChangePassword",
"iam:CreateLoginProfile",

For a user that has not yet logged into the AWS console, this will allow creation of the user’s login profile and setting a new password.

SOLVED: Windows 10 forbidden port bind

Angry budgie - Photo by Егор Камелев on Unsplash

Ever have this problem, launching a Docker container (in this case, Nginx on port 8000)?

Error: Unable to start container: Error response from daemon: Ports are not available: listen tcp 0.0.0.0:8000: bind: An attempt was made to access a socket in a way forbidden by its access permissions.

or maybe this problem, trying to run PHP’s built-in webserver?

php -S localhost:8080
[Fri Sep 11 09:00:09 2020] Failed to listen on localhost:8080 (reason: An attempt was made to access a socket in a way forbidden by its access permissions.)

Who to trust?

Like me, you may already have read many “solutions”, on a whole bunch of spammy websites. The “fixes” are often no more than workarounds – and in some cases, pretty bad workarounds, at that. Such as:

  • Disable VPN
  • Disable Internet Connection Sharing
  • Disable third party firewall
  • Disable antivirus (for goodness’ sake!)

More sensibly, use (e.g.) netstat to find out if something has already bound to the port.

None of these helped in my case. (Well I didn’t try disabling my antivirus or firewall, because c’mon!) Nothing was bound to the ports in question. I couldn’t disable ICS because I’m using its capabilities to provide NAT routing for Hyper-V networks.

The cause

It turns out the problem is down to Docker and Hyper-V reserving a shed load of ports. You can verify if this is the case for you by running the following command (which despite advice elsewhere on the internet does not need to be in an elevated PowerShell prompt; plain old no-privileges cmd will do):

netsh interface ipv4 show excludedportrange protocol=tcp

In my case, I could see that a lot of ports were reserved, between 1128 and 55437:

Start Port End Port
---------- --------
1128 1227
1228 1327
1328 1427
1428 1527
1528 1627
1628 1727
1728 1827
1828 1927
1928 2027
[snip]
50000 50059 *
53610 53709
53710 53809
54210 54309
54610 54709
54710 54809
54910 55009
55113 55212
55214 55313
55338 55437

* - Administered port exclusions.

I confirmed that this is the issue by picking a port that hadn’t been reserved:

php -S localhost:50080
[Fri Sep 11 09:12:21 2020] PHP 7.4.8 Development Server (http://localhost:50080) started

(For me, the PHP web server would also start quite happily on port 80, incidentally. But you probably shouldn’t do that!)

People who have identified this issue tend to recommend disabling Hyper-V, excluding whatever ports you need and re-enabling Hyper-V. I’m nervous of that approach however, having spent a lot of time configuring Hyper-V networking and having seen this approach nuke networking in the past.

If you’re happy taking that approach, I suggest reading and understanding this Microsoft article. Personally, I prefer to approach this as follows.

Find gaps in port reservations

Hyper-V and Docker between them seem to reserve different sets of ports on each reboot. Helpful. You can look for gaps in the port reservations using the following method, but note that these gaps will not persist, without other measures. Here’s how to find the gaps:

  1. Run the netsh command above.
  2. Copy and paste the output into Notepad++ and use search and replace (in regular expression mode) to turn all the spaces into tabs – replace ( +) with \t.
  3. Copy and paste the result into Excel (which will now put all the ports nicely into cells.
  4. Use an Excel formula to identify gaps in the reserved ranges: =IF(A4=(B3+1), "continuous", "## " & TEXT(A4-B3-1, "0") & " PORT GAP ##")
    List of reserved port ranges, showing any gaps
  5. Where “PORT GAP” appears, there is a gap between the end port on that line and the start port on the next (this would be 2115-2379 in the example above, which is 265 ports, inclusive).

As you can see, this approach does find you an available port (unless something else has bound to it):

php -S localhost:2115
[Fri Sep 11 09:36:58 2020] PHP 7.4.8 Development Server (http://localhost:2115) started

The fix: reserve your own ports

Well, two can play that game. Once you’ve found a gap, you can permanently reserve it for your own use. I found the largest gap between 12970 and 49670, so decided to reserve a memorable slice of ports: 20000- 21000. The appropriate incantation follows, which does need to be elevated this time. Swap port numbers and range to suit your environment and requirements:

netsh int ipv4 add excludedportrange protocol=tcp startport=20000 numberofports=1000 store=persistent

You will see that the range is now showing as administratively reserved (indicated by the asterisk):

netsh interface ipv4 show excludedportrange protocol=tcp

Protocol tcp Port Exclusion Ranges
Start Port End Port
---------- --------
1215 1314
…
20000 20999 *
…
51490 51589
- Administered port exclusions.

And once again, I can use a port within my preferred range:

php -S localhost:20080
[Fri Sep 11 10:37:23 2020] PHP 7.4.8 Development Server (http://localhost:20080) started

This exclusion persists between reboots and protects your range from being stolen by Hyper-V or anything else.

Angry budgie featured photo by Егор Камелев on Unsplash

Hyper-V virtual switch creation woes

man rubbing his temples in frustration
UPDATE: You may wish to leap straight to the comment below from Craig S., in which he recommends a workaround for this problem. Many subsequent commenters have tried his suggestion, with success. Thanks, Craig!

For my own part, I moved to using an internal switch configured for NAT and run a pfSense VM in that network, for DHCP. Without doubt, Craig’s solution is easier to implement!

If you found this post helpful, you might also be interested in John Savill’s book, Mastering Windows Server 2016 Hyper-V. In the interests of transparency: this is an affiliate link. See my affiliate disclosure page for an explanation.

High-end laptop hardware does not approach enterprise-grade server quality. That’s my takeaway.

I’ve been wrangling with Hyper-V on a very nice ultrabook that has 32GB of RAM and a Core i7 processor (quad-core). Highly portable and useful for running multiple VMs, which indeed was the idea.

This ultrabook is also now sporting the 2004 release of Windows 10. Security-obsessed folks like me have taken a keen interest in the application sandbox features.

Since sandboxing is all about virtualisation, it made sense, I reasoned, to use Hyper-V rather than any other virtualisation platform. It’s native to Windows and this way there should be fewer potential conflicts between hypervisors.

And it was all going well until I attempted to create an “external” (bridged) switch. The creation process failed with the following unhelpful Virtual Switch Manager errors:

  • “Adding ports to the switch ‘[switch name]‘ failed. The operation failed because the object was not found.”
  • “Failed while adding virtual Ethernet switch connections. Ethernet port ‘{[insert long GUID here]}’ bind failed: Cannot create a file when that file already exists. (0x800700B7).”

Worse than that, whatever process had partially completed could not easily be reversed – clicking ‘cancel’ did not back out any changes. Rather, it left my laptop with networking utterly ruined.

I confirmed this by repeated attempts to create a vSwitch, following resetting the network stack and removing/reinstalling Hyper-V. Same results every time. If you find yourself in this mess, utter the incantation “netcfg -d” at an elevated command prompt and reboot. (You may also need to remove and re-add WAN miniport devices as described here, since this process can break existing L2TP VPN connections.)

Is there a way to fix this problem? I believe not, at present. It’s almost certainly connected to the attempted use of a WiFi adapter which Hyper-V can’t always support. My adapter is an Intel Wi-Fi 6 AX200, FWIW. No matter how expensive the laptop, you can’t assume that the hardware will provide across-the-board support for virtualisation. For that, you really need a decent-quality server-grade NIC.

All is not lost. Although it’s not possible to bridge the Wi-Fi adapter, the default network (which offers NAT) works fine. And internal virtual networks (which aren’t hardware bound) are also unaffected. Granted, this means you can’t route externally into your VMs, but that’s not the end of the world, particularly if you’re au fait with port forwarding (read also the comments at that link).

(Yes, you could also use an external network interface for this, though that reduces your laptop’s portability of course. Quality matters for that too – YMMV.)

If you have cracked this problem or can provide any further thoughts or guidance, please do let me know in the comments!

This article’s featured photo by Siavash Ghanbari on Unsplash

OCS Inventory NG authentication with Active Directory

OCS Inventory NG forms an essential part of my DevOps/Security strategy. OCS gives me visibility of the hardware and software inventory of all compute nodes in my network.

My primary user directory is AD, so I try and ensure that wherever possible, systems authenticate against AD. This is perfect when disabling accounts, when someone leaves. Less worrying about all the other places that person may have credentials.

I’ve found that the instructions for authenticating OCS against LDAP simply don’t work for Active Directory. That’s up to and including the most recent version at the time of writing – version 2.4.

It’s necessary to make a code change, for AD authentication to work. In an ideal world, I’d raise a pull request with the OCS team to fix this, but I’m not really a developer. I’ve chosen instead the line of least resistance: hack the code to make it work. Sometimes you just have to be pragmatic.

The fix isn’t documented as far as I can tell. I’m indebted to Bruno Lessa for discovering and/or writing up the code changes that are necessary to enable authentication with Active Directory.

So, here are the code changes you need to make, after installing OCS Inventory. Some of the files may be in a slightly different location, in your installation, depending on your operating system. My installation was on Ubuntu 16.04 – I imagine the location is pretty similar on other OSes too.

File Find line containing Change to
/usr/share/ocsinventory-reports/ocsreports/backend/AUTH/auth.php $list_methode = array(0 => “local.php”); $list_methode = array(0 => “ldap.php”, 1 => “local.php”);
/usr/share/ocsinventory-reports/ocsreports/backend/identity/identity.php $list_methode = array(0 => “local.php”); $list_methode = array(0 => “ldap.php”, 1 => “local.php”);?

Having made those code changes, you configure LDAP in the web interface (Config > Config > LDAP configuration). Values similar to those below:

Setting Example value
CONEX_LDAP_SERVEUR PROD-DOMC-01.mydomain.com
CONEX_ROOT CN=ldapreadonlyuser,OU=Accounts,DC=yourdomain,DC=com
CONEX_ROOT_PW ldapreadonlypassword
CONEX_LDAP_PORT 389
CONEX_DN_BASE_LDAP OU=Accounts,DC=yourdomain,DC=com
CONEX_LOGIN_FIELD samaccountname
CONEX_LDAP_PROTOCOL_VERSION 3
CONEX_LDAP_CHECK_FIELD1_NAME memberof
CONEX_LDAP_CHECK_FIELD1_VALUE CN=SysOps Admins,OU=SysOps,OU=Groups,DC=yourdomain,DC=com
CONEX_LDAP_CHECK_FIELD1_ROLE Super administrators
CONEX_LDAP_CHECK_FIELD2_NAME memberof
CONEX_LDAP_CHECK_FIELD2_VALUE CN=SysOps Operators,OU=SysOps,OU=Groups,DC=yourdomain,DC=com
CONEX_LDAP_CHECK_FIELD2_ROLE RO
CONEX_LDAP_CHECK_DEFAULT_ROLE [blank]

Use CCleaner? Read this.

CCleaner is a popular program for cleaning up computers. Amongst the host of similar programs out there, CCleaner is the only one I’ve used and trusted, for many years. This week, that trust has been undermined fundamentally.

A version of CCleaner was released during August that contained malicious code, presumably without the developers’ knowledge – though it could well have been an inside job. Anyone installing CCleaner during August/early September may have installed the compromised version of CCleaner – version 5.33.

This is serious. CCleaner is powerful software. The injected code would run with at least the same power of CCleaner, which means it could potentially:

  • Watch your browsing activity
  • Capture passwords
  • Steal your files
  • Compromise your online banking credentials
  • Delete arbitrary data
  • Encrypt files

And so on.

You can see if you’re at risk by running CCleaner and checking the version number:

If you have version 5.33 version installed, I strongly recommend taking the following steps:

  • Uninstall CCleaner immediately
  • Change all passwords you use with the affected computer – including online passwords, banking passwords, etc.
  • Review bank account and credit card statements for unusual activity

In many cases, you can add an extra layer of protection to your passwords by using “two factor authentication” (Google calls it 2-step verification). When logging into certain services, you will be prompted to enter a code from a text message or app. Even if your password has been compromised, two-factor authentication makes it that bit harder for others to gain access to your accounts.

For more information on two factor authentication (“2FA”):

CNET: Two factor authentication what you need to know
PCMag: Two factor authentication – who has it and how to set it up

For a list of services known to support 2FA:

TwoFactorAuth.org

Cisco’s security research team Talos advises that the ultimate target seems to be prominent tech companies. There’s evidence to suggest that a Chinese group has used this injected malware to launch further targeted attacks on companies like Sony, Intel, Samsung, Microsoft and several others. The most likely objective here is to steal intellectual property.

Should that make us any less concerned? Probably not. Such a serious compromise in a widespread, popular program undermines trust in software supply chains generally. There isn’t an awful lot we can do to defend against this sort of approach, other than to proceed with caution when installing any software. Best to stay away from the latest, bleeding-edge releases, perhaps.

Avast, the popular antivirus manufacturer owns CCleaner. If this can happen to a leading software security company, it can happen to anyone.

Run for the hills!!! 😀

Integrating OCS Inventory with Rundeck

I’ve been on a DevOps journey for a while now. If you’re in a similar place – am I just a dullard, or is it slow going?!

I work mainly at the Ops side of the equation, in an environment that strongly favours open source solutions. Most recently I’ve been focusing on automating asset management/inventory. For this, OCS Inventory NG fits the bill well. The interface isn’t that slick, and I couldn’t for the life of me get the Active Directory integration working [UPDATE: now working; read this post], but for collecting software and hardware inventory, it’s the bomb.

In a mixed estate (Windows/Linux/Mac), I can use Group Policy, Rudder and Meraki respectively to force the OCS agent onto endpoints. Which means I can just sit back and let my CMDB populate itself. Awesome. (Because who’s got time to keep these things updated themselves, right?)

This inventory automation was a prerequisite for Rundeck. Since you’re here, you probably already know, but just in case you don’t: Rundeck is a fantastic tool for wrapping policies around any task you can dream of. You can use it for centralised job scheduling, you can use it to allow your developers to reboot servers without giving them SSH access, and you have ACLs and a full audit trail for everything.

For Rundeck to be any use, it needs a list of servers to control, which brings me back to OCS Inventory. OCS knows about my servers, so let’s just get Rundeck talking to OCS. Then Rundeck will have an always-up-to-date list of server endpoints, with no human input required. Marvellous.

My weapon of choice here is PHP, because I know it and because all the required components for this script are already installed on the OCS Inventory server. The simple prerequisites:

  1. Ensure all servers are tagged on their way into OCS Inventory. I use the installation switch /TAG="SERVER" with the OCS agent.
  2. On the OCS Inventory server, create a read-only MySQL user for the script. I created the user “[email protected]” (so its purpose was clear) and gave it the minimum permissions – SELECT on the accountinfo and hardware OCS tables.

I created a PHP script in the OCS Inventory web root. For me that’s at /usr/share/ocsinventory-reports/ocsreports. I called the script rundeck-xml.php. And here’s the code:

<?php
// OCS inventory integration into Rundeck.
$host = "127.0.0.1";
$db = "ocsweb";
$user = "rundeck";
$pwd = "PASSWORD GOES HERE";

$link = mysqli_connect($host, $user, $pwd, $db);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}


// Select all devices tagged as "SERVER" in the OCS database
$query = "
    SELECT `NAME`, `WORKGROUP`, `OSNAME`, `OSVERSION`, `OSCOMMENTS`, `IPADDR`, `DESCRIPTION`, `ARCH` FROM hardware
    LEFT JOIN accountinfo ON hardware.`ID` = accountinfo.`HARDWARE_ID`
    WHERE accountinfo.`TAG` LIKE '%SERVER%'
    ORDER BY `NAME`
";


if($result = mysqli_query($link, $query)) {
    // Start XML
    header('Content-type: text/xml');
    echo "<project>\n";

    while($row = mysqli_fetch_object($result))
    {
        echo "    <node name=\"{$row->NAME}\" type=\"node\"\n";
        echo "        hostname=\"{$row->NAME}.{$row->WORKGROUP}\"\n";
        echo "        osName=\"{$row->OSNAME}\"\n";
        echo "        osVersion=\"{$row->OSVERSION}\"\n";
        // Architecture is either in the DESCRIPTION field (for Ubuntu) or ARCH field (for Windows)
        $arch = (isset($row->ARCH) ? $row->ARCH : $row->DESCRIPTION);
        echo "        osArch=\"$arch\"\n";
        echo "    />\n";
    }
    mysqli_free_result($result);

    echo "</project>\n";
}
?>

Possibly not the most elegant code, but it gets the job done. Further security is left as an exercise for the reader. 😉

Referring to the database and the RESOURCE-XML Rundeck schema, you can extend this script to suit your needs. Add this to your Rundeck project configuration as an external resource model, with the URL of the above script. E.g. http://ocsserver.domain.com/ocsreports/rundeck-xml.php. All being well, every server from OCS Inventory will now appear as a node in Rundeck.

GDPR: what is a small UK business to do?

Although it’s nearly upon us, it seems like many businesses remain unaware of the impending data protection doom of the General Data Protection Regulations. Small businesses in particular. It’s easy to think that (a) there’s no way you’d have time to prepare your business and (b) it won’t apply to you in any event.

The trouble is, that’s a risky position to take. When it comes into force on 25 May 2018, GDPR will usher in fines of up to €20m (and beyond). On top of that, consumers will be increasingly ready and willing to sue companies over data protection issues. Every business needs to take GDPR seriously, then.

Under the current regime, governed by the Data Protection Act, the maximum fine for a data breach is £500k. Under GDPR, at present Euro exchange rates, it’s 34 times that amount. Our data protection enforcement body, the Information Commissioner’s Office (ICO), is about to have a major weapons upgrade.

In June 2017, the ICO fined Morrisons £10,500 for a marketing faux pas. In July, the company under the cosh was MoneySuperMarket and the fine, £80,000. Scaling those fines up 34 times and you’re looking at £357k and £2.7m respectively.

Now it might not work that way in practice, but we’re still looking at huge potential exposure – the kind of exposure that could put a company out of business. Realistically a smaller company is likely to face a smaller fine (smaller customer databases, smaller likely impact from any breach). But also, a smaller company, with less resources to apply to security and cyber risk insurance, is more likely to fall foul of the regulations and be fined. Again and again and again.

Does this sound alarmist? Possibly. It all comes down to risk really. If you’re happy to play fast and loose with your customers’ data in full knowledge of the consequences, read no further. But if all this is giving you pause for thought, stick with me.

But Brexit?

Sorry; we’ll be following GDPR regardless of Brexit.

250 is the magic number

The regulations impose differing obligations on companies, depending on number of employees. The legislation will be less onerous for companies with fewer than 250 members of staff. But still onerous.

If you’re under the 250 mark, but you process or store much personal data (customers, suppliers, employees), GDPR will apply to you in full. So if you’re running a greengrocer’s you’re probably okay. If you’re running a small accountancy firm, well you’ve got a lot of work to do. And we can’t afford to ignore this, right?

New stuff

We’re already covered by the Data Protection Act in the UK. GDPR significantly enhances personal data protection and privacy by imposing:

  • Significant changes when it comes to consent. You may not market to anyone who has not consented. And consent has to consist of an act on the part of the person. Pre-ticking consent boxes on website won’t fly any more.
  • Clarity and ease. It must be easy for consumers to understand what it is they’re consenting to, and easy for them to withdraw consent. Consent must be defined by channel (e.g. email/telephone/SMS) and duration (how long the consent will last).
  • Data portability. If someone asks for a copy of the data you hold on them, you must supply it within 30 days, in a common electronic format (Word document, Excel spreadsheet, PDF file, etc.).
  • Accuracy. You are obliged to correct any incorrect data – including, if you’ve shared that data with a third party, making them correct it too.
  • A right to be forgotten. If someone asks you to remove their data, and you have no other legitimate reason to keep it, you have to remove it.
  • Mandatory data breach processes. If you become aware of a breach that affects personal privacy, you will need to tell the ICO within 72 hours of discovering the breach. Essentially means you need a bullet-proof data breach policy in place.
  • Privacy by design. If you’re designing a new system or business process, you must consider privacy at the outset (and you must document the fact).
  • Data Protection Impact Assessments. If a piece of work is likely to represent a high risk when it comes to personal data, you must conduct a DPIA. The GDPR does not specify the detailed process, but it’s essentially based on risk analysis. If after your analysis, you conclude there is a high risk to privacy, you must consult the ICO before commencing work.
  • Data Protection Officer. If your business is over the 250 mark, or under it and you process personal data, you must appoint a Data Protection Officer. And that DPO needs to have some idea of the responsibilities of the role. Reading this blog post should help!
  • A broad definition of “personal data”. This now includes IP addresses, for example. It’s essentially any data that identifies a person or that could be used with other data to identify a person.
  • Security. The legislation requires you to take reasonable steps to protect personal data. Think encryption, robust passwords for access, principle of least privilege, need to know, etc.

What do I need to do?

If you’re reading all this for the first time, you’ve probably already started to identify areas of your business that you’ll need to review. Here’s a general plan of attack that I would recommend:

  1. Appoint a Data Protection Officer.
  2. Review all your data, thoroughly. If you have more than one employee, you’ll probably need to involve others in this process. If you don’t know where your data is or what data you’re holding, you will be oblivious to your compliance obligations. And obliviousness is no defence I’m afraid, when it comes to penalties.
  3. If you undertake any marketing activity at all, use the remaining time you have between now and May to seek consent from your existing customer base. If you don’t have their consent post-May 2018, and you market to them, you’re liable to be fined and/or sued.
    For companies with large marketing operations, this will be quite a sizeable undertaking. Make sure when you’re collecting consent, you note when consent was granted, which channels it covers and how long it will last. In future, you’ll need a process to renew consent before expiry, or to expunge expired data.
  4. Ensure that in any automated process you use to collect consent, you don’t use pre-ticked boxes or similar. Also, don’t do this anymore: “If you don’t reply to this email, we’ll assume you want to hear from us…”
  5. Update any privacy notices, particularly taking account of the obligation to be clear. Pretend you’re writing it to be read by a 12 year old.
  6. Put in place processes to amend or delete data when required to do so.
  7. Develop a process to provide a copy of all data to a consumer, when asked.
  8. If there’s a chance you will process the data of anyone under the age of 13, you’ll need a process for obtaining parental consent.
  9. Write a data breach response plan. This doesn’t need to be a 100 page document. Just simple steps to follow in case of a breach – which include notifying the ICO and the affected consumers as appropriate.
  10. If in doubt, seek professional help.

Disclaimer

I’m writing this as a Certified Information Systems Security Practitioner and a non-practising solicitor. These guidelines do not constitute legal advice, but I hope they will point you in the right direction. The truth is that these regulations aren’t in force yet, so nobody really knows quite what impact they will have on the data protection landscape. It will be a big shake-up though, that’s for sure.

Featured photo used with permission.