Is there a way to batch delete Enterprise Structure Locations and Categories?

[kevin_christens]

We would like to restructure how the locations and categories are organized and would like to delete all existing location and category paths to reload new ones.

Is there a simple batch process that can do this since doing individually via the web interface would be too time consuming?

Example would be a stored database routine similar to deleting users in bulk with ComplianceUserRemoveBatch.

[Alpesh_Gohil]

Hi, 

Please reach out to Support. They can help with providing the SQL. There are SQLs that have been used to perform cleanup for Cloud customers, which can be used for any OnPrem environments too.

Thanks!

[ChrisG]

Here's an example of a SQL script that can be executed against the compliance database to delete all groups identified by the first query in this script:

-- This script will delete all enterprise groups identified by the following query:

SELECT GroupID
INTO #GroupsToDelete
FROM dbo.GroupEX_T
WHERE
	GroupTypeID = 1			-- 1 = locations, 2 = corporate units, 3 = cost centers
	AND BusinessView = 0	-- don't delete the root group

DECLARE db_cursor CURSOR FOR
SELECT GroupID from #GroupsToDelete ORDER BY GroupID

OPEN db_cursor

DECLARE @ID int

FETCH NEXT FROM db_cursor INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @ID

	EXEC dbo.GroupDeleteAndChildren @groupid = @ID

	FETCH NEXT FROM db_cursor INTO @ID
END

CLOSE db_cursor
DEALLOCATE db_cursor

[Andrei_ABB]

Hi Chris, 

Has this script been updated for changes in new FNMS versions? I'm getting 

Invalid column name 'GroupExID'.
Procedure GroupDeleteAndChildren, Line 24 [Batch Start Line 0]
Invalid column name 'GroupExID'

I'm using FNMS 2022 R2

Maybe this procedure is now deprecated and we now have something new? Probably a column name changed by the message. 

Regards,

Andrei

[ChrisG]

I don't know of any changes, but it is possible the GroupDeleteAndChildren stored procedure does not work anymore. This stored procedure is not actually used by any functionality within FlexNet Manager Suite, so it could be deprecated.

Or is it possible that you have a customized stored procedure configured in your database? The "line 24" referenced in the error message seems a strange: I can't see how anything around line 24 in the stored procedure would obviously result in the error you are seeing.

If this procedure doesn't work anymore, new SQL script would be required to achieve this. I'm not sure if anybody here has a script to share.

[Andrei_ABB]

Hi, 

Update: I was able to get  a different script that doesn't use the "GroupDeleteAndChildren" stored procedure and works with FNMS 2022 R2. The only drawback is that it only works for costcenters since it removes all the references of costcenters in other tables. For anybody needing to delete something else like location or corporate unit, you can probably substitute costcenterid with locationid or corporate unitid in each of the update sections and will do just fine. Of course don't forget to test this on your test instance. I hope this will be useful for others looking for this in the future. 

Regards,

Andrei

[Andrei_ABB]

Hi Chris, 

I used the script that I provided above for cost centers and substituted the cost center with category in each table that was referencing GroupEx for categories. I just wanted to send an update and let everyone later reading this case know this is not ideal and there were traces left in our DB. I opened a case with Flexera and was recommended to run the procedure below which fixed our issue.

EXEC PopulateInitialEnterpriseGroups_MT 1

 However I now have another quiestion:  knowing that this method cannot be used to remove categories, would it work for corporate units? We have over 1k corporate units and I need a script to remove everything and import clean data in a new format. 

Regards,

Andrei

[vytasdem]

Is there a way to do this in Flexera One? 

deleting the entries in the WEB UI does not sound very smart...

[vytasdem]

by the way, Idea submitted: Ability to delete more than one entry at a time in "IT Asset | Ideas (aha.io)

if anyone feels this to be of use, please vote 🙂

[erwin_lindemann]

Hi Kevin,

Basically, when deleting any node object below any of the 'Enterprise > Structure' node (Corporate Units, Locations, Cost Centers and Categories) from the FNMS UI, FNMS will recursively delete all child node objects of the parent object automatically.

Please note that default categories under "Hardware" and "Software" cannot be deleted.

As a 'best practice', you should create locations and categories in a structured hierachical way. As an example for locations, you can use "EMEA > Germany > Munich > Site A". This makes it easy to delete all child notes for "EMEA > Germany" or "EMEA > Germany > Munich" in a single step.

[vytasdem]

Hi, 

This does work if you have everything in the correct hierarchy. In our situation we had an import which did not go as planned 😄 and we ended up with ~2k lines on the root level 🙂

[erwin_lindemann]

Ladies,

feel free to try the script below.

/*   
	Script for deleting ALL 'Group' child objects for a certain Group Type in FNMS safely.

	The script finds and deletes all direct child objects of the root object.

	The trigger 'GroupExDelete' on the [GroupEx_MT] table makes sure that 
	- all additional child objects are deleted recursively
	- All references to the deleted objects are removed.

	There is an issue with this trigger, as it will not remove references in the [RoleRight]-Table.
	
	If you are using an role that is scoped to a 'Group' (Location, Cost Center ...), this 
	scoping MUST be removed first.

	If not, you will get a 'REFERENCE constraint "FK_RoleRight_Scope_MT"' error message.
*/

/* @GroupTypeID: 1=Locations, 2=Departments, 3=Cost Center, 4=Category - see table [GroupType] */

DECLARE @GroupTypeID INT
SET		@GroupTypeID = 1     -- adapt this to the object type where you would like child objects to be removed

; WITH 
cteRootObjectGroupID(GroupID) AS
(
	/* Returns the 'GroupID' value for the root object for object type '@GroupTypeID' */
	SELECT  ge.GroupID 
	FROM   [GroupEx] ge
	WHERE   
	       ge.GroupTypeID = @GroupTypeID
	   AND ge.BusinessView = 1
),
cteFirstLevelChildren(GroupID) AS
(
	/* Returns the 'GroupID' values for all direct children of the root group object  */
	SELECT 
		TargetID        AS [GroupID]
	FROM   [MemberEx] m
	JOIN   cteRootObjectGroupID rog on rog.GroupID = m.GroupID
)
DELETE FROM [GroupEx_T] 
WHERE 
	GroupID IN	(SELECT GroupID FROM cteFirstLevelChildren)

As usual, doing a backup of your FNMSCompliance database before deleting data is a good idea 😁.

[Andrei_ABB]

Hi Erwin, 

Thank you for this script. It seems to be working however I still have my main problem. I always get a foreign key constraint error when trying to delete drom GroupEx_T and I need to go and manually set the BusinessUnitID to null in many other tables (ComplianceUser_MT, ComplianceComputer_MT, Asset_MT) probably others as well, however since it takes half a day to remove from one I'm still working on this. I thought the flag is going to take care of it and deleting from GroupEx_T will take care or all other tables related to GroupEx_MT however that is not happening. Am I getting something wrong? So far I'm doing this in test and I can afford to play with it,  however setting BusinessUnitID  to null in all those tables is super time consuming. 

Regards,

Andrei

[erwin_lindemann]

Hi Andrei,

There is a trigger named 'GroupExDelete' on the [GroupEx_MT] table. This trigger makes sure that all references to objects in tables like [ComplianceUser_MT], [ComplianceComputer_MT], [Asset_MT] ... are removed in case objects are deleted from the [GroupEx_T].

This trigger sets all references from the GroupEx_T] table in the 'LocationID', 'BusinessUnitID', 'CostCenterID' and 'CategoryID' target columns are set to NULL for the object(s) that are deleted.

Take a look at the source code of this trigger please in case you want to batch delete references manually.