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.
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.
This document is intended for FlexNet Manager administrators, with a good knowledge in SQL.
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:
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.
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.
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…
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.
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.
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).
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
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.
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)
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*************************
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.
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:
As the ComplianceHistory table is not altered or re-created, there is no need to re-index the table.
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************************
Aug 26, 2021 02:21 AM - edited May 31, 2023 11:28 AM
Hello Nicolas,
is it necessary to execute both queries or only one of them?
regards,
Matthias
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,
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.
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.
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
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".
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
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
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.
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?
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
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?
Hello,
How do you make inventories?
Through Flexera agent or SCCM or Altiris or any other connection ?
Flexera agent.
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.
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).
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.
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
Thanks for that, will download and test the new version
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)