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

Note, this document has been updated on May 31st 2022 to fix and issue related to index drop and re-build with History Records deletion and provide a new versions of the "Interesting history records" extract / insert that now uses 10000 records loops with commits to avoid SQL Server log size massive growth.

• 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 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 (Version 1, no intermediate loops and commits): FNMS 2018+

  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

  The script assumes you are on FNMS 2018 or later and will not try to “discover” the table list of columns (see version 2 below). This makes the script easier to read.

  The steps are the following:

  Determine which ComplianceHistoryID corresponds to “3 month ago”. Using the ID allows better performance in the queries. You need to make sure there is one record returned. A comment: “Sorry, no HistoryID was found for exactly 91 days ago” will warn you that another approach than the ID is required (find a relevant ID)

  • Uses physical tables to store to allow easier troubleshooting
  • Exports all “to be conserved” ComplianceHistory_MT records:
    • Less than 3 months old (old)
      • All records.
      • Except a set of licenses (that may have a lot of allocations for instance). You need to set the SoftwareLicenseIDs that are relevant to the exclusion. The script has negative IDs and will not exclude licenses by default.
    • Does this in batches of 10 000 with intermediate commits to avoid the log file excessive growth.
    • More than three months old
      • Related to contracts, purchases and Licenses.
      • Exclude some licenses
    • Truncate the ComplianceHistory_MT Database (which will reset the complianceHstoryIDs). You need to uncomment here.
    • Removes all ComplianceHistory_MT indexes. Re-builds the primary key.
    • Re-import all conserved records. You need to uncomment too.
    • DROP the “To Be Conserved” History records temp table. This step is commented to let you troubleshoot the data.

  ATTENTION

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

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

   

  Compliance History records (Version 2, no intermediate loops and commits)

  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 also 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.

  --**********************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*********************

  ADDITIONAL INFORMATION

  Also see the following article for an alternate discussion about this topic: History table deletion scripts - Solution to Forum Post.