I’ve recently come off a long-term role of getting thousands of machines set up and imaged, all day, everyday, all untouched until they reach the facility. In fact, one of this blog’s first posts was about adding in a Synology to the environment. Today I’d like to discuss my technological workflow I deployed in this environment.

As you may be able to gather, we have a lot of space for computers, as far as the eye can see, and that isn’t even including the imaging racks that can handle 32 laptops in a much denser configuration.

As you may imagine, set up of a network to accommodate network booting, imaging, and control of thousands of machines is quite a burden. There are many ways to solve the problems you’d face, and I’d like to go over one way that required a lot of out of the box thinking to get setup.

One thing that staff in this facility work on is getting these machines set up and reporting to anti-theft services in the cloud. Given the extreme value of even a handful of these devices, checking compliance is a serious concern. As such, automated scripts are ran on the devices to install software, force calls to the cloud, and backup that data our selves. All well and good, until you have a single machine you can’t find.

As these are fresh machines, the only thing that is known is what is on the boxes that are scanned. Thankfully, between the various data sources consolidated on one web page, staff can verify call activity and be able to know where said device is even located, down to the chain of switches it is uplinked to.

The backend of this data is from many places, but focusing on just the location of the device in the network, the idea is to avoid having to ask every switch if it knows of a MAC address, but instead to just look up the MAC in a table of all the switch data.

There is software out there to help with this. However, most of the software did not work for me in the end due to the following restrictions:

• Cost enough to make it a no starter (pretty much anything over 20 dollars)
• Required a Linux box or to install software not standard on our machines (such as Perl)
• Required someone to maintain the tool

As such, I was faced with building an automated tool using installed software (or portable software) on my machines. Instead, here is what I used:

• Windows Server 2012 R2 (standard install)
• The PuTTY offshoot KiTTY
• This standalone S3 command line tool
• The SolarWinds TFTP Server tool (already installed for switch firmware upgrades)
• A LAMP server in the cloud

Combined together, a batch script runs infinitely in a loop, connecting to switches, dumping their MAC Address data, and uploading it to the cloud.

This script contains four main logical points:

1. Connect to each switch on an increasing counter (or skip if offline)
2. Run the command to pull MAC Addresses
3. Save this data to the cloud and trigger the server to absorb it
4. Wait and do it again

Code-wise, it looked as such:

@echo off
TITLE MACGrabber
setlocal enabledelayedexpansion

set /a "counter=1"

:startmacro
IF "%counter%"=="1" (
ECHO.
ECHO Skipping %counter%
GOTO :nextone
)

....

IF "%counter%"=="70" (
ECHO.
set icsw=192.168.0.%counter%
ping -n 1 !icsw! | find "TTL=" >nul
if errorlevel 1 (
 GOTO :nextone
)else (
 ECHO Setting the switch source to !icsw!
)
)

IF "%counter%"=="71" (
ECHO.
ECHO Ending process...
GOTO :endmacro
)

kitty.exe -telnet !icsw! -cmd "\n no page\n copy command-output 'show mac-address' tftp 192.168.0.%TFTPSERVERIP% switch-%counter%-macs.txt\n logout\n y\n n" -log test.txt

s3 put %BUCKETNAME%/directdrop/macs/ C:\TFTP-Root\switch-%counter%-macs.txt

:nextone
set /a "counter+=1"
set icsw=
ECHO.
ECHO Moving to %counter%
GOTO :startmacro

:endmacro
"%cd%\data\curl.exe" --tlsv1 https://www.%DATASITE%.com/.../mac_address_sync.php
set /a "counter=1"
timeout /t 3600 /nobreak
GOTO :startmacro

and on the server side, here is the abbreviated code that absorbs that data into a database table.

$s3 = new S3(awsAccessKey, awsSecretKey);

$bucket_contents = $s3->getBucket($bucket, "directdrop/macs/");

$final_file_name = "macs_scraped.csv";
$final_file = fopen($final_file_name, "w");
$count = 0;
$local_time = date("Y-m-d h:iA");
foreach ($bucket_contents as $file) {

 $filename = substr($file['name'], 16);
 $switch = substr($filename, 7, strpos($filename, "-m") - 7);
 $s3->getObject($bucket, $file['name'], fopen("$filename", 'wb'));

 $reading = fopen("$filename", 'r');
 $linecount = 0;

 while (!feof($reading)) {
 $line = trim(fgets($reading));

 if ($linecount <= 5) {
 
 } else {// Skip header nonsense
 if ($line != "") {
 $count++;
 $mac_address = substr($line, 0, 13);
 $port = trim(substr($line, 14, 3));
 $vlan = trim(substr($line, 16));
 fputcsv($final_file, array("$switch", "$port", "$vlan", "$mac_address", "$local_time"));
 }
 }
 $linecount++;
 }
 fclose($reading);
}

fclose($final_file);

$truncate = "TRUNCATE TABLE switch_mapping";
if (!mysqli_query($mysqli_link, $truncate)) {
 die('Error: ' . mysqli_error($mysqli_link));
}

$query = "LOAD DATA LOCAL INFILE '$final_file_name' REPLACE INTO TABLE `switch_mapping` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '' LINES TERMINATED BY '\n';";
$result1 = mysqli_query($mysqli_link, $query);
if (!$result1) {
 $message_response = "Error running $query</br></br>: " . mysqli_error($mysqli_link);
 $error_occured = 1;
} else {
 $lines_imported = mysqli_affected_rows($mysqli_link);
 $message_response = "$lines_imported rows effected (deleted and added) from $final_file_name";
}

And now you have switch data in your cloud database table, ready to be joined in a query against the data that was available (for us, this was pulling the MAC Addresses from the anti-theft agent or the backup method and cross-referencing that data with the serial number).

What makes this work well is that the process is fully automated on the Engineer-side, and all a staffer has to do is enter the list of serials to attempt to locate a machine, as they know where the switches are located, and can chase down troublesome machines.

If you read into the script, you can see what’s happening is the data is dumped from the script to the TFTP server from a Telnet session to the switch, and then handed off to S3 to be absorbed by the site. Is it the most efficient way, the most glamorous, or most optimal? Probably not. However, it works well enough for the needs of the customer for the zero-dollar budget allotted to solving the problem, and brings a considerable benefit to the users. That’s a win in my book.

And that’s my HP ProCurve MAC Address scraper tool in a nutshell. I would like to stress to everyone that a lack of programming expertise shouldn’t scare you away from solving the IT related problems you have. I am not a professional developer, but when you piece together enough bits of technologies together, you can deliver some great solutions. Whether it be hacking StuffIt passwords, unique hardware/software solutions, or chopping up magazines to digitize, there’s always some crazy option on the table!