1 Reply Latest reply on Jun 3, 2011 1:12 PM by Catalysttgj

    Unique NIC Address

    seattleman1969 SupportEmployee
      How can I get a  unique NIC address for every device in LDMS, when some of the NIC addresses in  the system are duplicate nics from VPN, AIR Card, and dialup  adapters?
      I need to be able to  produce a report with a single column containing a truly unique NIC  address.
        • 1. Re: Unique NIC Address
          Catalysttgj Expert

          There might be more clever ways to pull this off, but our method was to create a stored procedure that would select unique nic addresses for each device by eliminating the known fake mac addresses. We had to study our content a bit to determine what made the most sense, and in an attempt to future proof it a bit, we chose to filter by the "Description" for each Physical Address.

           

          The code that works for us, might not work for everyone, so whoever decides to go this route should carefully evaluate their own content, but hopefully this chunk of SQL code will get you on the right path.

           

          First thing, you'll want to add a new "column" to the NetworkSoftware table in your LDMS database. We chose this table because this is where the current NicAddress column also lives. we named our new column "BINAddress", which is just short for Built-in Nic Address. You'll want to make this column's properties the same as the Nicaddress column's - (nvarchar(45), null)

           

          From here this chunk of SQL code should work fine: (You'll need to make this into an SP, so that you can schedule it to run on some frequency.)

           

          update NetworkSoftware
          set BINaddress =
              (select Top (1) ba.PhysAddress from BoundAdapter as ba where
                  n.Computer_Idn = ba.Computer_Idn
                  AND ba.PhysAddress != 'Unknown'
                  AND ba.Description NOT LIKE '%Bluetooth%'
                  AND ba.Description NOT LIKE '%CreditCard%'
                  AND ba.Description NOT LIKE '%Mobile-based%'
                  AND ba.Description NOT LIKE '%PPP%'
                  AND ba.Description NOT LIKE '%VMware%'
                  AND ba.Description NOT LIKE '%VPN%'
                  AND ba.Description NOT LIKE '%Wireless%'
                  AND ba.Description NOT LIKE '%WWAN%'
                  AND (select count (*) from BoundAdapter as ba where
                          n.Computer_Idn = ba.Computer_Idn
                          AND ba.PhysAddress != 'Unknown'
                          AND ba.Description NOT LIKE '%Bluetooth%'
                          AND ba.Description NOT LIKE '%CreditCard%'
                          AND ba.Description NOT LIKE '%Mobile-based%'
                          AND ba.Description NOT LIKE '%PPP%'
                          AND ba.Description NOT LIKE '%VMware%'
                          AND ba.Description NOT LIKE '%VPN%'
                          AND ba.Description NOT LIKE '%Wireless%'
                          AND ba.Description NOT LIKE '%WWAN%') > 0)
          from
              NetworkSoftware as n

           

          update NetworkSoftware
          set BINaddress = '<MISSING_' + cast(Computer_idn as varchar) + '>'
          where
              BINaddress is null

           

           

          This will produce unique NICs for almost all devices, but there will always be a handful that won't have unique macs, so they may end up with Null values, so to avoid this, the second "update" near the bottom of the above code will force any nulls to be populated with the "MISSING_" + the device ID. This will make it easy to identify in a report devices that are having trouble providing a unique mac.

           

          Now, there's one more caveat, and that is devices that do not have only one unique mac, such as servers. The code above will select ONLY one of them, so it might not necessarily be the one you want it to select. The code might be able to be polished a bit to make a better solution for this. I'm sure there's some SQL gurus out there that might have a better approach. Please, offer suggestions for improvements, if you have any!

           

          The last thing that needs to be done is to add the new column to the LANDesk schema. This can be done by modifying the dataMart.xml. Keep in mind that you're modifying a very important file that landesk provides in their installation software. Meaning that if you change this, and this includes adding the extra column to the database, these changes may be lost during any sort of upgrade to your system. You would likely have to recreate these items again after an upgrade. You should definitely make a backup of your datamart.xml file before editing it. You may want to consult with landesk about an alternative method to editing datamart.xml. Having said all that, its pretty easy to do. Open datamart.xml with an editor and search for the column name "NicAddress". It will be under the table "NetworkSoftware". From there copy the entire line for "NicAddress", and paste it in right below it. Rename the second copy "BINAddress", then save the datamart.xml file. Don't forget to back it up before you do this! BTW, if you're uncertain about any of the above steps, DON'T DO THEM! You need to be comfortable with this entire process.

           

          Finally, you'll use the CoreDbUtil app to perform a "BuildComponents". This will add the new extra column to the schema, and make it possible to use the new column in LDMS query logic.

           

          Good luck!