Complete Duplicate MAC-address Detector

Version 2

     

    I - Introduction

    The LANDesk Management Suite / Ivanti Endpoint Manager has had means with which to detect duplicate MAC-addresses - albeit mainly for a purpose of deleting older records / said duplicates. The relevant technology has a few other limitations around it, some of which are intentional (we prefer to err on the side of caution / "not deleting"), but one of its biggest limitations is that it's a "big magic button" that just has an on or an off switch.

     

    There's no "what would happen if I turned this on,,.?" scenario / data provider, whcih is of particular use to larger enterprises especially. It would be really useful to either have SELECTIVE methods of deleting ("some but not others") duplicates, and/or of being able to actually see "how bad" the situation around multiple MAC-addresses is, without automatically having those deleted.

     

    This is the scenario that this script (and a potential follow-on script) are to address.

     

    I.A - Minor note about screenshots

    Please be aware that screenshots may not marry up 100% with what you're seeing. I've been editing the SQL-script for data points off and on (after the screenshots were made). The main gist should still be pretty clear.

     

    II - The script & its usage.

    The script is a pretty simple PowerShell script that doesn't require anything special. Just run it on wherever you've got your LDMS / IEM Database installed (the script talks directly to SQL Server).

     

    • The script is 100% "non destructive" and will not do anything other than "read" data. Any handling / sorting of data/results is taken care of either directly with SQL-statements or within Powershell itself. So a "read-only" type account is the only thing that's needed.
      • A separate script is intended to handle deletion of devices. This is PURELY for the detection & reporting of duplicate MAC-addresses.

     

    • The script uses passthrough NT-authentication to connect to SQL. So no usage of SQL-account. Just create an NT-based account for it, and that way you can schedule the script to run (semi-)regularly as you desire via either Windows Scheduler or LANDesk local Scheduler task to provide you up-to-date information.
    • The script itself is very heavily documented within - the intention is to make sure that everyone with even a modicum of general scripting language will hopefully understand what happens where - how - and why.

     

    In order to run the script you just need to do the following:

    1. Copy / extract the script on to a system that has SQL Server (needed as Powershell will import the relevant commandlets to talk to SQL).
    2. Edit the "SETTINGS.TXT" file and fill out your DB-server name/instance/database name. If you're uncertain about anything, look at the "SETTINGS_Example.TXT" for a "living example" of what goes where and/or poke your DBA to help you out.
    3. Make sure you have (and are logged in with) a Windows account which has at the very least *READ* access to the database. The Powershell script uses pass-through authentication (easier than handling securely storing credentials).
      1. Alternatively - you can run the script as "A Windows User" via either Windows Scheduler or the LANDesk Local Scheduler to deal with user-context.
    4. Make sure you're executing the powershell script from the same location it's stored in (as well as its SETTINGS.TXT).

     

    The only common "gotcha" is really step 4 - folks may try to execute the script in a remote directory. Powershell will look in its local directory for a SETTINGS.TXT & not find it. So - keep relative directories in mind .

     

    III - Points of note & observations

    There's a few important points that need to be kept in mind when looking at the results. I tried to include those in the log-file / script itself:

     

    • LINUX/UNIX systems tend to bring back "all 0-s" MAC-addresses for inactive NIC-s (i.e. - NIC-s that don't have a cable connected).
    • Some systems may not return all data (for instance, older scanners and/or Linux systems commonly don't return BIOS data). So it may be desirable/necessary for certain organisation to expand on the data set being returned back.

     

    • The author tried to provide sensible data-points to help with identifying relative age of records / help with identifying uniqueness of devices. This can be expanded upon either by requesting data points to be added here (in a comment), or just by editing the SQL Statement used. While length, the SQL Statement should be pretty simple to understand & expand upon (if you're familiar with where the data you're trying to add is located). There's a strong change that more uniqueness data points will be added as time passes / situations arise.

     

    IV - Brief side-line walkthrough - making the data readable in Excel

    If anyone is unsure in how to make the CSV data "Excel readable" - here's a quick walkthrough to help out with that.

     

    Step 1 - open up the CSV results with Excel. Your initial view should appear roughly like so (i.e. - not very readable):

     

    Step 2.A - select / highlight the data beginning with "COMPUTER_IDN" like so (You just need to do so for the first column starting from A2):

     

    Step 2.B - Click on the DATA-section and then on the "TEXT TO COLUMNS"-button (highlighted in purple):

     

    Step 3.A - You'll now be presented with a simple wizard to tell EXCEL how it should convert things. Here's an initial screen (Make sure you select "Delimited" and then hit "NEXT"):

    So - from this initial screen...:

     

    Over to this:

     

    Step 3.B - Change the default Delimited option from "Tab" over to "Comma".

    So you should go from this:

    Over to this:

     

    Step 3.C - Editing the data types:

    • You COULD do this here, but it may be easier to do so in "regular Excel" for the most part.
    • One of two important things here is to set the destination ($A$2 is "fine", as the first line is populated by a comment field injected by Powershell).
    • The second really "noteworthy" thing to do at this stage is to find the MAC-address field and to change it over to "TEXT". Otherwise, any "all 0-s" MAC-addresses risk being presented as "just a single 0", which will falsify things potentially for you.

     

    So from this...

     

    over to something like this:

     

    Click "Finish" when you're happy

     

     

    This will then result in a changed setup like this one. Much better, but still needs improvement:

     

    Step 4 - Now having some data, you can edit the data-types to be more useful. Sensible data types to change are:

    • Ensure that you've changed the MAC-addresses over to being a TEXT field (You should've done this above already)).
    • Change the "Last Updated By Inventory Server" to be a proper Date / Time format (for some reason Excel doesn't have SQL DATETIME values as standard...).
      • European / most of the world time format should be ==> dd/mm/yyyy hh:mm:ss
      • US Time format should be ==> mm/dd/yyyy hh:mm:ss

     

    By performing those changes (and expanding our fields), defining the top line as filters as well as changing the text alignment to be on the left, we'll get something like the following:

     

    ... which can now be saved as an Excel sheet / worked on / review much more easily (including filtering based on age, etc.)

     

    V - Deleting devices.

    While the author intends to write up a separate follow-on script which (re-)processes the output file for "devices to be deleted", this can be currently batched/scripted as well.

     

    Most of the information required can be found in this article here -- Getting Started with the MBSDK (Example Scripts Included) -- as by and large, such a script could be broken down into the following steps:

    1. Read the (modified) CSV / XLSX file
    2. Call a www-service to delete the device(-s) 1 at a time - such as "DeleteComputerByGUID" (The script (v1.0.0.0) does return GUID-s). This would seem safer than deleting devices by IP-Name (as there's always the risk of duplicates).
    3. Repeat step 2 until the list has been processed.

     

    ... the "most complicated" operation then would be reading out a CSV / XLSX file at most - which shouldn't be too hard.

     

     

    VI - Contents of the attached zip-file

    The attached 7z-file contains the following files:

    • "DetectedDuplicateMACs.txt" ==> Example output of "just the MAC addresses".
    • "Duplicate MAC Checker.ps1" ==> The main powershell script. Feel free to fiddle with it. Should hopefully have all the relevant bits documented sufficiently.
    • "Duplicate-MAC-Checker.Log.txt" ==> Example log-file of a run against a database of mine (if you want to see what sort of thing to expect).
    • "FullResults_ExampleOutput.csv" ==> Exaxmple CSV output against a database of mine. Note the lack of BIOS information is normal (given the Linux agents used).
    • "SETTINGS.TXT" ==> Text-file that you need to fill out / edit for the Powershell script to be able to connect to your database (seemed easier/cleaner than asking people to edit the powershells script).
    • "SETTINGS_Example.TXT" ==> Filled out example of the "SETTINGS.TXT" if you're not sure what I'm talking about. If you're not sure what your DB-instance is, try "default" and/or talk to your DBA.

     

    VII - Feedback / Questions and what not ...

    ... can be added in the comments section