Update corporate unit based on domain

[adrian_ritz1]

Hi Community,

I have a tricky request from our customer.

FNMS 2018 R2 on prem installation, full access to the database

The problem is the following, to map corporate unit to an inventory base on domain, the customer provide me a mapping table, with domain and corporate unit.

The question how can I do this? 

I was thinking to build a custom view that will aggregate the compliancecomputer, domain, and the mapping table and use a business importer to update this?

Or may be some SQL command?

Do you have some idea how can I do this.

Thank you for your support and I wish you a nice weekend. 

[Nico_Erasmus]

Hi Adrian.

You are on the right track.  If the customer can give you the mapping of domain to Corporate Unit, then:

1. Create a view/query of devices per domain - include name, domain and sn in result

2. Use a business importer to update the inventory device.

Note: Be sure of your inventory device to asset synchronization settings so that you can be clear on updating the asset or the inventory device with your adapter.

Regards

[ChrisG · Accepted solution]

My answer here is somewhat complex and I hesitate to give it as it could be scary and uneducated use could mess up data. I am not suggesting this the "best" approach; better would probably be to use a business adapter.

With that caveat, the following SQL script I have stashed away from work done some years ago performs logic similar to what you are describing here. One key difference is that this script updates the "Location" of inventory device records rather than "Corporate Unit".

/*
 * Move computer records to a meaningful location, based on the following heuristics
 * (the first matching heuristic wins):
 *
 * 1. If the computer is linked to an asset, and that asset's location is not "Unknown"
 *    but is different from the computer's location, move it to the asset's location. In
 *    theory this should not be needed, but there is at least one defect in ECM8.1.2 and
 *    earlier releases where computer locations can be incorrectly cleared when the asset
 *    location is set (see IA 73491).
 *
 * 2. If the computer has a calculated user, move the computer to that user's location.
 *
 * 3. If the computer's name and domain matches a "known" pattern, move it to a location
 *    identified as being appropriate for that pattern. The "known"
 *    patterns are specified in the #CustomNameDomainToLocationMap
 *    table that is populated below.
 *
 * 4. Otherwise move the computer to the "Unknown" location.
 */

IF OBJECT_ID('tempdb..#CustomNameDomainToLocationMap') IS NULL
BEGIN
	-- The #CustomNameDomainToLocationMap table defines computer
	-- name and domain name patterns that are mapped to specific
	-- locations. In the case where a particular computer matches
	-- multiple records, the one with the smallest priority value
	-- (= highest priority) is chosen.

	CREATE TABLE #CustomNameDomainToLocationMap(
		DomainPattern NVARCHAR(100) COLLATE DATABASE_DEFAULT NOT NULL
		, NamePattern NVARCHAR(256) COLLATE DATABASE_DEFAULT NOT NULL
		, LocationPath NVARCHAR(500) COLLATE DATABASE_DEFAULT NOT NULL
		, Priority INT NOT NULL
	)

	INSERT #CustomNameDomainToLocationMap VALUES ('chinacorp.com', '%', 'Asia Pacific/China', 1)
	INSERT #CustomNameDomainToLocationMap VALUES ('indiacorp.com', '%', 'Asia Pacific/India', 1)
	INSERT #CustomNameDomainToLocationMap VALUES ('mexicocorp.com', '%', 'Latin America/Mexico', 1)
	INSERT #CustomNameDomainToLocationMap VALUES ('%', 'mel%', 'Asia Pacific/Australia/Melbourne', 2)
	INSERT #CustomNameDomainToLocationMap VALUES ('%', 'syd%', 'Asia Pacific/Australia/Sydney', 2)
END


IF OBJECT_ID('tempdb..#Target') IS NOT NULL
	DROP TABLE #Target

CREATE TABLE #Target(
	TargetID INT
	, TargetTypeID INT
	, GroupExID VARCHAR(128) COLLATE DATABASE_DEFAULT
	, GroupTypeID INT
)


BEGIN TRANSACTION

; WITH DesiredLocation AS (
	SELECT	TOP 1 WITH TIES
		cc.ComplianceComputerID
		, LocationID = COALESCE(a.LocationID, cu.LocationID, ml.GroupExID, ul.GroupExID)
		, ROW_NUMBER() OVER(PARTITION BY cc.ComplianceComputerID ORDER BY m.Priority) AS Ranking
	FROM	dbo.ComplianceComputerWithActiveInventory cc
		LEFT OUTER JOIN dbo.ComplianceDomain cd ON cd.ComplianceDomainID = cc.ComplianceDomainID
		LEFT OUTER JOIN (
			#CustomNameDomainToLocationMap m
			INNER JOIN dbo.GroupEx ml ON ml.Path = m.LocationPath AND ml.GroupTypeID = 1 /* location */
		) ON cc.ComputerName LIKE m.NamePattern AND ISNULL(cd.QualifiedName, '') LIKE m.DomainPattern
		LEFT OUTER JOIN dbo.ComplianceUser cu ON cu.ComplianceUserID = cc.CalculatedUserID
		INNER JOIN dbo.GroupEx_T ul ON ul.Path = 'Unknown' AND ul.GroupTypeID = 1 /* location */
		LEFT OUTER JOIN dbo.Asset a ON a.AssetID = cc.AssetID AND a.LocationID != ISNULL(cc.LocationID, '') AND a.LocationID != ul.GroupExID
	WHERE	(cc.LocationID IS NULL OR cc.LocationID = ul.GroupExID OR a.LocationID IS NOT NULL)
	ORDER BY Ranking
)
UPDATE	cc
SET	LocationID = dl.LocationID
OUTPUT INSERTED.ComplianceComputerID, tt.TargetTypeID, INSERTED.LocationID, 1 /* location */ INTO #Target
FROM	DesiredLocation dl
	JOIN dbo.ComplianceComputer cc ON cc.ComplianceComputerID = dl.ComplianceComputerID AND ISNULL(cc.LocationID, '') != dl.LocationID
	, dbo.TargetType tt
WHERE	tt.TargetTypeName = 'ComplianceComputer'


PRINT N'Moved ' + CAST(@@ROWCOUNT AS nvarchar(255)) + N' unlocated ComplianceComputer records to a better location'

EXEC dbo.GroupExUpdateGroupMembershipBulk

DROP TABLE #Target


UPDATE	a
SET	LocationID = cc.LocationID
FROM dbo.Asset a
	JOIN dbo.ComplianceComputerWithActiveInventory cc
		ON cc.AssetID = a.AssetID
		AND cc.LocationID != ISNULL(a.LocationID, '')

PRINT N'Synchronized location from ' + CAST(@@ROWCOUNT AS nvarchar(255)) + N' ComplianceComputer records to linked Asset records'

/*
   NB. We do *not* have to call GroupExUpdateGroupMembershipBulk here
   to update group membership details for updated assets, since there
   is a built-in "UpdateAssetGroupMembership" trigger that does that
   automatically for us. There is no similar trigger for
   ComplianceComputer, which is why an explicit call to
   GroupExUpdateGroupMembershipBulk is required above.
*/

COMMIT TRANSACTION

Be careful with this. If you are not confident that you can understand what this SQL is doing enough to make changes yourself, I would suggest avoiding using it. It may do things you don't expect if you don't think it through carefully.

[adrian_ritz1]

Hi @ChrisG ,

Thank you for your script but in the end I used a custom view and a business importer to do this. I implemented in the TEST platform and now I'm waiting for customer feedback.