cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
  • 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************************

     

Was this article helpful? Yes No
100% helpful (1/1)
Comments
mschwach
By
Level 7

Hello Nicolas,

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

 

regards,

Matthias

nrousseau1
By Level 10 Champion
Level 10 Champion

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
By
Level 6

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
By Level 10 Champion
Level 10 Champion

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
By
Level 5

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
By
Level 6

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
By Level 10 Champion
Level 10 Champion

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
By Level 10 Champion
Level 10 Champion

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
By
Level 6

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
By Level 10 Champion
Level 10 Champion

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
By
Level 6

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
By
Level 4

Hello,

How do you make inventories?

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

craig_moore
By
Level 6

Flexera agent.

nrousseau1
By Level 10 Champion
Level 10 Champion

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
By Level 10 Champion
Level 10 Champion

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

Pick Up Data Input.png

 

 

 

 

 

Delete Connection.png

steve_young
By
Level 3

Is anyone getting errors running the script.

It starts throwing the following errors in our environment, even though it sets IDENTITY_INSERT to on in the script for this table

(1 row affected)
Msg 544, Level 16, State 1, Line 14
Cannot insert explicit value for identity column in table '#ComplianceHistory2' when IDENTITY_INSERT is set to OFF.

(1 row affected)
Msg 544, Level 16, State 1, Line 81
Cannot insert explicit value for identity column in table '#ComplianceHistory2' when IDENTITY_INSERT is set to OFF.

(1 row affected)
Msg 544, Level 16, State 1, Line 99
Cannot insert explicit value for identity column in table '#ComplianceHistory2' when IDENTITY_INSERT is set to OFF.
Warning: Foreign key 'FK_ComplianceHistory_ComplianceHistoryType' on table 'ComplianceHistory_MT' referencing table 'ComplianceHistoryType' was disabled as a result of disabling the index 'PK_ComplianceHistory'.
Warning: Index 'IX_ComplianceHistoryByAsset' on table 'ComplianceHistory_MT' was disabled as a result of disabling the clustered index on the table.
Warning: Index 'IX_ComplianceHistoryByComputer' on table 'ComplianceHistory_MT' was disabled as a result of disabling the clustered index on the table.
Warning: Index 'IX_ComplianceHistoryByContract' on table 'ComplianceHistory_MT' was disabled as a result of disabling the clustered index on the table.
Warning: Index 'IX_ComplianceHistoryByPurchaseOrderDetail' on table 'ComplianceHistory_MT' was disabled as a result of disabling the clustered index on the table.
Warning: Index 'IX_ComplianceHistoryByVendor' on table 'ComplianceHistory_MT' was disabled as a result of disabling the clustered index on the table.
Warning: Index 'IX_ComplianceHistoryBySoftwareLicense' on table 'ComplianceHistory_MT' was disabled as a result of disabling the clustered index on the table.
Warning: Index 'IX_ComplianceHistoryBySoftwareTitle' on table 'ComplianceHistory_MT' was disabled as a result of disabling the clustered index on the table.
Warning: Index 'IX_ComplianceHistoryByPaymentSchedule' on table 'ComplianceHistory_MT' was disabled as a result of disabling the clustered index on the table.
Warning: Index 'IX_ComplianceHistoryByInstance' on table 'ComplianceHistory_MT' was disabled as a result of disabling the clustered index on the table.
Warning: Index 'IX_ComplianceHistoryByUser' on table 'ComplianceHistory_MT' was disabled as a result of disabling the clustered index on the table.
Warning: Index 'IX_ComplianceHistoryByDocument' on table 'ComplianceHistory_MT' was disabled as a result of disabling the clustered index on the table.
Warning: Index 'IX_ComplianceHistoryByProject' on table 'ComplianceHistory_MT' was disabled as a result of disabling the clustered index on the table.

nrousseau1
By Level 10 Champion
Level 10 Champion

Hello @steve_young ,

Indeed, there was an issue with index drops and re-creation. I just uploaded v4 that also includes a version that does not build the queries from the tables schema (easier to read) and that copies and re-inserts the "Interesting history records" using 10 million records loops (with commits) to avoid issues with SQL Server log file size.

Best regards and sorry for the inconvenience.

Nicolas 

steve_young
By
Level 3

Thanks for that, will download and test the new version

dbeckner
By Level 10 Champion
Level 10 Champion

We are preparing to implement this in our environment. Does removing any of the history affect the Compliance Trend report? FlexNet Manager Suite Online Help - Compliance Trend Report (flexera.com)

Version history
Last update:
‎May 31, 2023 11:28 AM
Updated by: