Page Comparison

...

Code Block

lang	sql
title	Detect Imported Users that are a duplicate

selectSELECT * fromFROM Inv_Global_Windows_Users GWU
WHERE EXISTS (
	(selectSELECT 1 fromFROM ResourceKey RK whereJOIN Inv_Global_Windows_Users GWU2 ON RK.ResourceGuid = GWU2._ResourceGuid
	WHERE RK.KeyName = 'name.domain' 
and		AND RK.KeyValue= (upperUPPER(GWU.UserId) + '.' + upperUPPER(GWU.Domain))
	and	AND RK.ResourceGuid != GWU._ResourceGuid
	)

Code Block

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

selectSELECT * fromFROM Inv_Global_Windows_Users GWU
WHERE NOT EXISTS (
	(selectSELECT 1 fromFROM ResourceKey RK whereJOIN Inv_Global_Windows_Users GWU2 ON RK.ResourceGuid = GWU2._ResourceGuid
	WHERE RK.KeyName = 'name.domain' 
and		AND RK.KeyValue= (upperUPPER(GWU.UserId) + '.' + upperUPPER(GWU.Domain))
	) 	AND UserId IS NOT NULL

Code Block

lang	sql
title	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))

...

Code Block

lang	sql
title	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)
	)

Versions Compared

Old Version 8

New Version 9

Key