RVY200403: How to clean up RayVentory Database from duplicates and outdated inventories

Security Hint:

• Always do a database backup before you do any changes to the regarding database.

Prerequisites:

• Download the attached "DuplicateCleanup.zip" file and extract the included files.
• Microsoft SQL Management Studio with access to the RayManageSoft/RayVentory database
• Microsoft Excel 2010 or newer version
• SQL Server 2012 or newer
• If you are using a RayManageSoft version older than 11.0 you also need to execute the attached "create_new_RMS_func.sql" file in the ManageSoft database (execute multiple times if errors are shown)

Creating a database backup

1. Start Microsoft SQL Management Studio with elevated rights and connect to the RayManageSoft/RayVentory database.
2. Open the "Backup.txt" file and copy the SQL-Code into a new Sql-Query in Microsoft SQL Management Studio.
3. Validate that the path in line 5 exists on the machine, where the database is located.
4. Validate that the database name in line 6 matches.
5. Execute the adjusted SQL-Query in Microsoft SQL Management Studio. This will create a backup of your RayManageSoft/RayVentory database. The file "managesoft_backup.bak" will be created.

Show Duplicates

1. Open the "Duplicate_Show.txt" file and copy the SQL-Code into a new query in Microsoft SQL Management Studio.
2. By default inventories that are older than 89 days are regarded as outdated. This value can be changed in line 24: when DATEDIFF(day,GetDate(),InventoryReport.SWDate) * -1 > 89 then 'TRUE' Just change the value of 89 to the desired value.
3. Make sure that ManageSoft is selected as the current database in the drop-down list at the top left
4. Execute the query, to get a list of potential duplicates.
5. Click on the top left cell of the results table to mark the whole table
6. Do a right click in your results view and click on "Copy with headers".
   
7. Open the file called "DuplicateAnalysis_Template.xlsx".
8. Select cell A7 in the table and paste the previously copied content into the document.
9. Check each entry carefully and decide whether the device inventory is a duplicate and should get removed or it is not a duplicate and should be kept. To do so, in the column "Recommendation" you have the possibility to select "Delete Inventory" for each device.
10. For every entry with "Confirm Keep/Delete inventory" you have to decide if you want to keep or delete it.
11. Save and export as CSV (CSV UTF-8 (seperated by separators) (*.csv))
12. Open the exported CSV file in Notepad++ or a similar text editor.
13. Remove all unnecessary characters before the header begins and at the bottom of the file, like in the example picture below.
14. Save the file as "DuplicateList.csv"

Remove Duplicates

For SSMS 17.3 or newer:

1. Start Microsoft SQL Management Studio with administrative rights and connect to the RayManageSoft/RayVentory database.
2. Right click on your ManageSoft database and click "Tasks" -> "Import Flatfile..."
3. Press "Next >"
4. Select the adjusted .csv file of "Show Duplicates" step 14.
5. Enter the name of the new table "DuplicateList" and click on "Next >".
6. Click on the "Next >" button 2 more times.
7. Click on "Finish" and validate that the result was successful.
8. Open the attached file called "Duplicate_Delete.txt" and copy the content into a new SQL-Query in Microsoft SQL Server Management Studio.
9. Execute the SQL-Query to delete the computers out of the ManageSoft database which are explicitly marked as "Delete Inventory" by you.
10. To delete the temporary table we created in "Remove Duplicates" step 7, you have to open a new SQL-Query, paste "DROP TABLE [dbo].[DuplicateList]" and execute.
11. If you want to check whether you still have duplicates, repeat all steps.

For SSMS versions below 17.3:

1. Start Microsoft SQL Management Studio with administrative rights and connect to the RayManageSoft/RayVentory database.
2. Right click on your ManageSoft database and click "Tasks" -> "Import Data..."
3. Press "Next >"
4. Select "Flat File Source" as Data source
5. Select the adjusted .csv file of "Show Duplicates" step 14.
6. Click "Next >" until the "Choose a Destination" page is opened
7. Select the "SQL Server Native Client 11.0" from the dropdown list and hit next
8. Click "Next >" 3 more times and then click "Finish" and validate that the result was successful
9. Open the attached file called "Duplicate_Delete.txt" and copy the content into a new SQL-Query in Microsoft SQL Server Management Studio.
10. Execute the SQL-Query to delete the computers out of the ManageSoft database which are explicitly marked as "Delete Inventory" by you.
11. To delete the temporary table we created in "Remove Duplicates" step 8, you have to open a new SQL-Query, paste "DROP TABLE [dbo].[DuplicateList]" and execute.
12. If you want to check whether you still have duplicates, repeat all steps.