RMS200520: Devices in RayManageSoft Are Not Created / Updated from their Inventory Data

Situation

For some devices the "Service the ManageSoft database" scheduled task does not create or update the device data even though there is related inventory data in the database. The reason for this behavior is, that there are conflicting inventory data entries with identical MAC addresses. This article helps to create devices from this conflicting data and afterwards, clean up the duplicate devices based on the age of the inventory data.

Security Hint

• Always create a backup of your database before you apply any changes to the database!

Prerequisites

• Download the attached (link at the end of this article) "DeviceCleanup.zip" file and extract the included files.

Updating the Device Entries

1. Open the "sp_PopulateNetworeDevices.sql" from the attached ZIP file using SQL Management Studio.
2. Ensure that ManageSoft is selected as current database in the drop-down list at the top left (as seen in the screenshot). 
3. Execute the query. This will update the "PopulateNetworkDevices" stored procedure in a way that will update devices even if there are conflicting MAC addresses in the inventory.
4. Now either execute the "Service the ManageSoft database" scheduled task or create a new query in the SQL Server Management Studio, paste "EXEC PopulateNetworkDevices" into the query, and click "Execute".

Deleting the Duplicate Devices Based on Inventory Age

1. Open the "Device_Cleanup.sql" from the attached ZIP file in the SQL Server Management Studio.
2. Ensure that ManageSoft is selected as current database in the drop-down list at the top left (see screenshot above).
3. There are two different possibilities to execute the next step:
   1. Execute the query to delete duplicate devices. For each MAC address only the device with the newest inventory data will be kept. The outdated / missing devices should now be updated / available in RayManageSoft.
   2. Alternatively, if you want to check beforehand which devices will be removed, you can mark the text from line 1 up to line 56 (where the comment says "--Show duplicate devices with outdated inventories") and click "Execute". Then you can view the devices which will be deleted (by marking the text from line 57 to 59 and clicking "Execute"). If you are content with the data, you can proceed and mark the text from line 67 to the end of the document and click "Execute". The outdated / missing devices should now be updated / available in RayManageSoft