Is the primary key of the Computer table in the inventory database in order?

[bleepie]

Hello,

I need to fetch ComputerID's from the Computer_MT table from the inventory database. The gathered ID's are then fed to the delete_devices stored procedure. Sometimes there are duplicates in the Computer_MT table. In this case I need to remove the oldest one(s).

There is no date field in the table so I cannot determine which of the duplicates is the newest and which is the oldest. However, I might be able to use the primary key (ComputerID). The field ComputerID seems to increment but I need to make sure that is the case.

My question:

Will every new row in the database automatically have a higher ComputerID number?

[nrousseau1]

Hello @bleepie ,

Please have a look to this article that will be very helpful. https://community.flexera.com/t5/FlexNet-Manager-Knowledge-Base/Inventory-Computer-and-History-deletion-scripts/ta-p/204962

It provides the script that will determine the computers (in FNMSInventory) to delete according to their last inventory date when there are duplicates.

To answer to your question:

• The inventory dates are stored in InventoryManager in the InventoryReport view (that you can join to Computer through the ComputerID)
• You are right ComputerID are always ascending, the latest ID is the most recently created one... unfortunately, a new NDI will randomly update duplicated computer records. Keeping the most recently created computer will not keep the most recently updated (the script provided in the solution I shared in 2021 uses the inventory dates).
• This topic relates to a broader "merge" issue. If you are on a pre 2023 version, you can create this report provided in this post: https://community.flexera.com/t5/FlexNet-Manager-Knowledge-Base/Inventory-Gap-Analysis-custom-reports-VMs-from-vCenter-Oracle/ta-p/211848. If you are post 2023, in the SAM Operations Folder (or from the SAM Operations Hub), you can run the Merged Devices Issues Analysis report that provides the full analysis.

You can also contact me for support on this topic and many others: nrousseau@nrsamconsulting.com.

Best regards,

Nicolas

[bleepie]

Hello Nicolas, thank you for your reply. I have few questions. The first question is regarding the InventoryReport_MT table. I have picked a random row from the Computer_MT table which has ComputerID number 5. This ComputerID does not occur in the InventoryReport_MT table. I am not sure why that is. Should every computer occur in both tables?

Also, when looking at fields of the InventoryReport_MT table, there is not one single field that does not at least contain one NULL value. By this I mean that if I were to join on, for example, the HWDate field, there are still a few rows that I cannot use as they have a NULL value in it. The ones that have a NULL value do have other fields that could be used instead if that were the case such as SWDate, FilesDate, ServicesDate or VMwareServicesDate. Which field is the recommened field to use?

I am currently wondering the following: The Computer_MT table and the delete_computers stored procedure are both part of the inventory database. The data the user sees in the GUI is the data in the compliance database. If I understand correctly, the data is being pushed from the agent to the inventory database and  from the inventory database moved to the compliance database. We run the agent data upload once a week on Saturday. If I just completely feed the computer id's of the duplicate machines (even the ones we wanted to keep) to the delete_computers stored procedure on a Friday, would that interfere with the history of the records of the same machine in the compliance database? If it doesn't I think this is the easiest way to handle this issue.

[bleepie]

I just tested the latest point. Fed a computer id to the stored procedure. The row was removed from the Computer_MT table and some other affected rows. In the GUI (ComplianceComputer_MT table) I can still see the record (obviously) and its history. Lets say the agent of the hosts generates a new ndi file and it is is uploaded to the inventory database on saturday, I expect the host to show up again in the Computer_MT table. Is my assumption correct?