Inventory Computer and History deletion scripts

[#1]

• Owner: Nicolas Rousseau
• Solution Type: Backend SQL Script
• Flexera Product & Version: FlexNet Manager Suite On Premises
• Environment:
• Development Effort: 1 days
• Implementation Days: 0.1 Day
• Pre-Requisites: Database access with read write
• Disclaimer: 

• Disclaimer

  As this solution performs mass deletion (and even the ComplianceHistory_MT table truncation), this disclaimer has to be carefully considered. Please also refer to the “pre requisites” section that give hints on backup / roll back solution.

  SOLUTIONS ARE PROVIDED ON AN "AS IS" BASIS. NEITHER FLEXERA NOR ITS SUPPLIERS MAKE ANY WARRANTIES, EXPRESS OR IMPLIED, STATUTORY OR OTHERWISE, INCLUDING BUT NOT LIMITED TO WARRANTIES OF MERCHANTABILITY, TITLE, FITNESS FOR A PARTICULAR PURPOSE OR NONINFRINGEMENT. LICENSEE MAY HAVE OTHER STATUTORY RIGHTS. HOWEVER, TO THE FULL EXTENT PERMITTED BY LAW, THE DURATION OF STATUTORILY REQUIRED WARRANTIES, IF ANY, WILL BE LIMITED TO THE SHORTER OF (I) THE STATUTORILY REQUIRED PERIOD OR (II) THIRTY (30) DAYS FROM LICENSEE’S ACCEPTANCE OF THE AGREEMENT.

  Audience for this document

  This document is intended for FlexNet Manager Suite administrators, with a good knowledge in SQL.

  Business need and approach

  This document gives the details of scripts that will allow to clean up Inventory computers or compliance history data that causes possible issues (merged computers for instance with potential false positive applications) or performance and db size issues.

  More precisely, the scripts provided allow to:

  • Remove all computer records (and associated records) from FlexNet Inventory Manager that are more than 3 months old (threshold can be refined)
  • Remove all history records that are more than three months old, except history records related to contracts, licenses and purchases

  This document is intended for technical consultants.

  TO AVOID LOCKS, THE SCRIPTS MUST BE RUN ON OFFLINE DATABASES (no more inventory import or compliance processes).

  RUNNING THIS SCRIPT IS AT YOUR OWN RISK. TEST IT BEFORE RUNNING IT IN PRODUCTION.

  Pre-requisites

  As mentioned above, the two scripts provide below delete data or even truncate tables.

  The ComplianceHistory_MT truncation section has been commented to avoid a truncation “by mistake” of the table.

  The inventory devices deletion is a less critical operation, as live inventory will be restored during next uploads of NDI inventory files.

  Backup you FlexNet Inventory Manager and FlexNet Manager Compliance databases.

  A backup process is necessary for FlexNet Manager. Note that FlexNet Manager is not a critical application. This, there is no recommendation for mirroring the database. Worst case, if a database has to be restored, one day of business data will be lost and will lead to need to recreate licenses / purchases and contracts lost in the last day.

  Backup policies are defined by your IT organization, but a typical backup scheme would be the following: A daily full backup is sufficient to ensure the ability to restore the database. The full backups should be kept for one week, then one back up par week for one month… then one per month for three months…

  Make sure your recovery model is set to “simple”

  Full recovery model makes sense to allow a roll back at any time in case of database crash. As stated above, FlexNet Manager is not a critical application and loosing the business data of the day is not a big issue.

  However, the full recovery model turns into FNMSCompliance or FNMSInventory log files becoming huge. The mass delete operation can only make the situation worse.

  Test in test first

  The operations of these scripts are critical (records deletion, table truncation). This is imported you test them on your FNMS test instance, to evaluate the records that will be deleted, the time it takes to make the copies / deletion etc. Even on production, you can test the script part by part to understand better the scope of the changes.

  Script details

  Computer Records clean-up from Inventory Manager

  Need

  FNMS has no automated deletion mechanism (Some features are coming). Aging records in Inventory Manager can cause performance issues (quantity of “useless” file evidence information for instance), or, more complex to troubleshoot, merging issues (mix of old and new evidences for to ImportedComputer records that have been merged).

  Approach and code

  This script deletes from Inventory Manager all computers based on a 3 months age on last inventory date. It needs to be ran on the Inventory Manager database (named often FNMSInventory… or IM).

  As deletion can be very slow in inventory manager (it requires the use of the “DeleteComputerByID” stored procedure that deletes all related records), the script catches the list of ComperIDs to delete, deletes one by one… and commits every 1000 deletion.

  Note that a section of the caose that you can uncomment will allow to see on screen the computers that will be deleted.

  -- If you want to see all data on computers that will be deleted, uncomment the query below

  --SELECT c.ComputerCN as ComputerName, ir.*

  --FROM InventoryReport ir

  --LEFT JOIN Computer c on c.ComputerID = ir.ComputerID

  Compliance History records

  Need

  The ComplianceHistory table becomes use over the time (could be 2 billion records, 500 GB), a clean-up in necessary. Removing all history of this table is an issue as it removes import information related to licenses, applications installations or removals.

  Approach and code

  As the ComplianceHistory table structure has evolved over the time, the script builds a temps db based on the current list of columns of the ComplianceHistory table.

  The steps are the following:

  • Determine which ComplianceHistoryID corresponds to “3 month ago”. This ID will allo filters with way better performance.
  • Export all “to be conserved” ComplianceHistory records:
    • Less than 3 month old (old)
    • More than three month old
      • Related to contracts, purchases and Licenses.
    • Truncate the ComplianceHistory Database (which will reset the complianceHstoryIDs)
    • Re-import all conserved records.
    • DROP the “To Be Conserved” History records temp table.

  As the ComplianceHistory table is not altered or re-created, there is no need to re-index the table.

  ATTENTION

  As truncating the ComplianceHistory_MT is a critical operation, some parts of the code have been commented. For instance.

  --**********************CRITICAL SECTION, RUN AFTER TESTING IN TEST*************************

  --TRUNCATE TABLE ComplianceHistory_MT ****!!!!-- Uncomment this part when you are sure that the history is fine!!!!****

  --**********************CRITICAL SECTION, RUN AFTER TESTING IN TEST*************************

[mschwach]

Hello Nicolas,

is it necessary to execute both queries or only one of them?

regards,

Matthias

[nrousseau1]

Hi Matthias, There are two types of cleanups, you can decrease the size of your ComplianceHistory_MT table and keep older Inventory Manager computers or cleanup on the two side. How large is you ComplianceHistory_MT table? Please contact me: nrousseau@flexera.com.

Thanks, 

[dsalter]

My ComplianceHistory_MT  is 800GB and is a series problem from a storage and cost perspective.  I am happy to see this article as I opened a ticket with Flexera some time ago and was told there is no method to clean it up.  

You may want to mention you will want your database to be in Simple backup mode prior to starting this cleanup as you will likely overload your transaction logs if you are logging.

[nrousseau1]

Thanks Damon, you are totally right, using "Simple Recovery Model" is a must, even outside of this cleanup. I have seen customer with FNMS Compliance production database collapsing at first (large) inventory import. Best practice is always to use simple recovery model. FNMS is not a Business Critical application. One backup a day with not way to recover fully within the day is totally acceptable.

[Woo_Lam]

Hi Nicolas

Our FNMS Compliance database is using "FULL" Recovery model. What is the benefits of setting FNMS Compliance database using "SIMPLE" Recovery model? Getting better performance for inventory import and reconciliation?

Thanks

[dsalter]

Is it possible to share the scripts is a .sql file?

I am getting the following errors when attempting to run Clean Up Inventoried Computers and had similar errors on the Compliance History cleanup scripts.

Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'on'.
Msg 137, Level 15, State 1, Line 30
Must declare the scalar variable "@IncludedIDs".
Msg 137, Level 15, State 2, Line 31
Must declare the scalar variable "@IncludedIDs".
Msg 137, Level 15, State 2, Line 35
Must declare the scalar variable "@IncludedIDs".
Msg 137, Level 15, State 2, Line 40
Must declare the scalar variable "@IncludedIDs".
Msg 137, Level 15, State 2, Line 41
Must declare the scalar variable "@IncludedIDs".
Msg 137, Level 15, State 2, Line 44
Must declare the scalar variable "@IncludedIDs".

[nrousseau1]

Hello @Woo_Lam , sorry for the response time. Full recovery model leads to incredible bad impacts on log file growth because for intense SQL activities (inventory import) each transaction is saved to allow a potential revert/rollback.

FNMS is not a critical applications (like a Financial transaction or ERP would be for instance)... if you have a daily backup of the databases, you can restore the previous day FNMS Compliance database for instance. You will loose the purchases, contracts and licenses created on last day... but inventory will be restored in the next inventory import.

So, more advantages than risks moving to simple recovery mode!

Best regards,

Nicolas

[nrousseau1]

hello @dsalter , thanks for your feedback and sorry for taking time to come back to you. The error looks like what happens when you want to test a script that has variables without re-declaring the variables and their values... And I agree you may want to test the script before it actually truncates the ComplianceHistory_MT table!

I have uploaded v2 of the script that changes the approach

•  The "dangerous" parts are commented and you need to uncomment them to run the truncate / insert sections
• The script stores the variables values in a temp table, which allows to test the script by part.

I hope it solves your issue. Please contact me on nrousseau@flexera.com if you are struggling. To give you an idea, running the script on a 3 billion history records table takes 1 hour 10 minutes to extract the interesting 60 million history records and 20 minutes to re-import the 60 million records back into the truncated ComplianceHistory_MT table.

[craig_moore]

We just completed running the computer records clean script which took a long to complete.

We are reviewing the results and noticed that all inventory still shows records that have a 'stale'/old inventory date.  The Computer_MT table in the FNMSInventory DB still has the stale/old records so we are unsure on what the script actually did. My understanding is that the script should have deleted these records.

Do we need to now run a full compliance to complete the clean on computer/inventory records?  

 

[nrousseau1]

Hi Craig, yes, the computers have been removed from the FNMS Inventory database, and the next full inventory import and reconcile will delete these records from ImportedComputers, ImportedFileEvidences etc... this should release some space and decrease the risk for false positives. By curiosity, how long did the deletion take and for how many records? The deletions are performed one by one and I have not tried to do it by batches...

Thanks!

Nicolas

[craig_moore]

Nicolas,

dev environment which has reduced hardware specs and we had ~35k inventory records to be deleted so took ~30 hours fo script to complete.

So reconcile finished but nothing appears to have changed .. all the ~35K computers are still in inventory. 

Any ideas what we can check to determine why the script did not appear to work?

[didiercottereau]

Hello,

How do you make inventories?

Through Flexera agent or SCCM or Altiris or any other connection ?

[craig_moore]

Flexera agent.

[nrousseau1]

That's strange, the reader step removes from Importedcomputers_MT and all other "ImportedXX" tables the data... that then get's removed from the ComplianceComputerTable.

Let's have a look together.

[nrousseau1]

Just to update on the issue that computers where still on screen... and it resonates with multiple customer situations I have seen: there was a duplicated inventory source (the beacon reading inventory data from the FNMS Inventory database) and this was causing sticky data. Technically, if a beacon (or any inventory source) imports data into FNMS, it will create a "ComplianceConnectionID". If you have old connection or duplivcated ones, you may get incredibly painful "merging" issues with old evidences causing false positives... check on https://community.flexera.com/t5/FlexNet-Manager-Knowledge-Base/Inventory-Gap-Analysis-custom-reports-VMs-from-vCenter-Oracle/ta-p/211848 for the "merged devices" report.

To delete a connection, go to admin/Data Input (first screenshot), Inventory imports, open the detail, delete the connection (second screenshot).