Creation of User Resources via CMDB Import

Is creating User Resources without ResourceKeys bad?

The definite answer is Yes. This is the official word from the SMP Development team. Whilst Symantec Support may deny it, creation of User resources without the 'name.domain' Resource Key will most probably result in duplicate User Resources within the CMDB.

Whilst CMDB Import tasks do create 'name.domain' Resource Keys for Computer resources, it does not for User resources. The 'name.domain' Resource Key is a requirement for both Computer and User resources.

My newly created User Resources are being deleted!

Arellia's "User Server Resource Discoverer" discovers User resources which have no required ResourceKey and deletes them to ensure that no duplicates will be created in the CMDB.

To disable the automatic delete of User Resources without resource keys, in the configuration tree:
/Settings/Arellia/Infrastructure/Resource Discovery/Server Discoverers

Disable the "User Server Resource Discoverer"

I cannot rely on my Active Directory for importing Users and need to import using the CMDB Import functionality

Whilst this problem has been acknowledged by Symantec development there is still no current fix to the issue.

There are ways to get around the issue however if the CMDB import populates the "Global Windows User" dataclass with the domain name and user id of the user being imported.

Arellia suggests that the following three sql scripts will assist with the User Resource import without Resource Keys being populated, and should check with your Symantec Support Representative before testing or applying any changes.

Detect Imported Users that are a duplicate

SELECT * FROM Inv_Global_Windows_Users GWU
WHERE EXISTS (
	SELECT 1 FROM ResourceKey RK JOIN Inv_Global_Windows_Users GWU2 ON RK.ResourceGuid = GWU2._ResourceGuid
	WHERE RK.KeyName = 'name.domain' 
		AND RK.KeyValue= (UPPER(GWU.UserId) + '.' + UPPER(GWU.Domain))
		AND RK.ResourceGuid != GWU._ResourceGuid
	)

Detects Users populated with domain details that should have a resource key populated

SELECT * FROM Inv_Global_Windows_Users GWU
WHERE NOT EXISTS (
	SELECT 1 FROM ResourceKey RK JOIN Inv_Global_Windows_Users GWU2 ON RK.ResourceGuid = GWU2._ResourceGuid
	WHERE RK.KeyName = 'name.domain' 
		AND RK.KeyValue= (UPPER(GWU.UserId) + '.' + UPPER(GWU.Domain))
	) AND UserId IS NOT NULL

Detects Users populated with domain details that have a Resource Key that does not match the correct calculated KeyValue

select GWU._ResourceGuid, 'name.domain', upper(GWU.UserId) + '.' + upper(GWU.Domain) AS KeyValue, RK.KeyValue as CurrentKeyValue 
from Inv_Global_Windows_Users GWU 
INNER JOIN ResourceKey RK ON RK.ResourceGuid = GWU._ResourceGuid AND RK.KeyName = 'name.domain'
WHERE
	RK.KeyValue <> (upper(GWU.UserId) + '.' + upper(GWU.Domain))

Inserts ResourceKeys for Users that have Domain details populated, but no corresponding resource Key exists

INSERT INTO ResourceKey(ResourceGuid, KeyName, KeyValue)
SELECT GWU._ResourceGuid, 'name.domain', UPPER(GWU.UserId) + '.' + UPPER(GWU.Domain)
FROM Inv_Global_Windows_Users GWU
WHERE NOT EXISTS (
	SELECT 1 FROM ResourceKey RK 
	WHERE RK.KeyName = 'name.domain' AND RK.KeyValue = (UPPER(GWU.UserId) + '.' + UPPER(GWU.Domain))
) AND UserId IS NOT NULL

Updates ResourceKeys for Users that have Resource Key that do not match the upper case domain details

UPDATE ResourceKey SET KeyValue = UPPER(GWU.UserId) + '.' + UPPER(GWU.Domain)
FROM Inv_Global_Windows_Users GWU 
WHERE
	GWU._ResourceGuid = ResourceKey.ResourceGuid  AND 
	KeyName = 'name.domain'	AND 
	KeyValue <> (UPPER(GWU.UserId) + '.' + UPPER(GWU.Domain)) 
	AND NOT EXISTS (
		SELECT 1 FROM ResourceKey RKC 
		WHERE RKC.ResourceGuid = ResourceKey.ResourceGuid 
			AND KeyValue = UPPER(GWU.UserId) + '.' + UPPER(GWU.Domain)
	)