adrian_ritz1 asked a question.
Update corporate unit based on domain
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.
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 NULLBEGIN-- 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)ENDIF OBJECT_ID('tempdb..Target') IS NOT NULLDROP TABLE TargetCREATE TABLE Target(TargetID INT, TargetTypeID INT, GroupExID VARCHAR(128) COLLATE DATABASE_DEFAULT, GroupTypeID INT)BEGIN TRANSACTION; WITH DesiredLocation AS (SELECTTOP 1 WITH TIEScc.ComplianceComputerID, LocationID = COALESCE(a.LocationID, cu.LocationID, ml.GroupExID, ul.GroupExID), ROW_NUMBER() OVER(PARTITION BY cc.ComplianceComputerID ORDER BY m.Priority) AS RankingFROMdbo.ComplianceComputerWithActiveInventory ccLEFT OUTER JOIN dbo.ComplianceDomain cd ON cd.ComplianceDomainID = cc.ComplianceDomainIDLEFT OUTER JOIN (CustomNameDomainToLocationMap mINNER 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.DomainPatternLEFT OUTER JOIN dbo.ComplianceUser cu ON cu.ComplianceUserID = cc.CalculatedUserIDINNER 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.GroupExIDWHERE(cc.LocationID IS NULL OR cc.LocationID = ul.GroupExID OR a.LocationID IS NOT NULL)ORDER BY Ranking)UPDATEccSETLocationID = dl.LocationIDOUTPUT INSERTED.ComplianceComputerID, tt.TargetTypeID, INSERTED.LocationID, 1 /* location */ INTO TargetFROMDesiredLocation dlJOIN dbo.ComplianceComputer cc ON cc.ComplianceComputerID = dl.ComplianceComputerID AND ISNULL(cc.LocationID, '') != dl.LocationID, dbo.TargetType ttWHEREtt.TargetTypeName = 'ComplianceComputer'PRINT N'Moved ' + CAST(@@ROWCOUNT AS nvarchar(255)) + N' unlocated ComplianceComputer records to a better location'EXEC dbo.GroupExUpdateGroupMembershipBulkDROP TABLE TargetUPDATEaSETLocationID = cc.LocationIDFROM dbo.Asset aJOIN dbo.ComplianceComputerWithActiveInventory ccON cc.AssetID = a.AssetIDAND 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 TRANSACTIONBe 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.