...
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.
| Code Block |
|---|
| lang | sql |
|---|
| title | Detect Imported Users that are a duplicate |
|---|
| lang | sql |
|---|
|
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
) |
| Code Block |
|---|
lang | sql |
|---|
| title | Detects Users populated with domain details that should have a resource key populated |
|---|
| lang | sql |
|---|
|
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 |
| Code Block |
|---|
lang | sql |
|---|
| title | Detects Users populated with domain details that have a Resource Key that does not match the correct calculated KeyValue |
|---|
| lang | sql |
|---|
|
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 | Inserts ResourceKeys for Users that have Domain details populated, but no corresponding resource Key exists |
|---|
| lang | sql |
|---|
|
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 |
| Code Block |
|---|
lang | sql |
|---|
| title | Updates ResourceKeys for Users that have Resource Key that do not match the upper case domain details |
|---|
| lang | sql |
|---|
|
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)
)
|