If username field is having email value then you may need to modify the query to pull employeeid and map that with accountname field
Modify the query as below and see if that fixes the issue
SELECT DISTINCT u.employeeid AS accountname,
'SaviyntforSaviynt' AS name,
'SaviyntforSaviynt' AS endpointname,
u.employeeid AS accountcn,
'Role' AS attribute,
r.ROLE_NAME AS entitlementvalue,
u.username AS username,
u.statuskey AS status,
u.updatedate,
u.employeeid AS objectguid
FROM users u,
ROLES r,
role_owners rw
WHERE u.userkey=rw.USERKEY
AND r.ROLEKEY=rw.ROLEKEY
AND r.STATUS=1
UNION
SELECT DISTINCT u.employeeid AS accountname,
'SaviyntforSaviynt' AS name,
'SaviyntforSaviynt' AS endpointname,
u.employeeid AS accountcn,
'Rule' AS attribute,
CASE
WHEN TYPE=0 THEN concat(h.NAME, ' TYPE:BUSINESS')
WHEN TYPE=1 THEN concat(h.NAME, ' TYPE:TECHNICAL')
WHEN TYPE=2 THEN concat(h.NAME, ' TYPE:USER UPDATE')
WHEN TYPE=3 THEN concat(h.NAME, ' TYPE:NEW USER')
WHEN TYPE=4 THEN concat(h.NAME, ' TYPE:REMOVE USER')
WHEN TYPE=5 THEN concat(h.NAME, ' TYPE:UPDATE ENTITLEMENT')
WHEN TYPE=6 THEN concat(h.NAME, ' TYPE:NEW ENTITLEMENT')
WHEN TYPE=7 THEN concat(h.NAME, ' TYPE:REMOVE ENTITLEMENT')
WHEN TYPE=8 THEN concat(h.NAME, ' TYPE:REQUEST')
END AS entitlementvalue,
u.username AS username,
u.statuskey AS status,
u.updatedate,
u.employeeid AS objectguid
FROM hanarule h,
users u,
rule_owners ro
WHERE u.USERKEY = ro.USERKEY
AND h.HANARULEKEY=ro.RULEKEY
AND h.status=0
UNION
SELECT DISTINCT u.employeeid AS accountname,
'SaviyntforSaviynt' AS name,
'SaviyntforSaviynt' AS endpointname,
u.employeeid AS accountcn,
'UserGroup' AS attribute,
ug.user_groupname AS entitlementvalue,
u.username AS username,
u.statuskey AS status,
u.updatedate,
u.employeeid AS objectguid
FROM users u,
user_groups ug,
usergroup_owners ugo
WHERE ug.usergroupkey=ugo.USERGROUPKEY
AND ugo.USERKEY =u.userkey
UNION
SELECT DISTINCT u.employeeid AS accountname,
'SaviyntforSaviynt' AS name,
'SaviyntforSaviynt' AS endpointname,
u.employeeid AS accountcn,
'SAVRole' AS attribute,
s.rolename AS entitlementvalue,
u.username AS username,
u.statuskey AS status,
u.updatedate,
u.employeeid AS objectguid
FROM users u,
user_savroles us,
savroles s
WHERE u.userkey=us.userkey
AND us.rolekey=s.rolekey
AND s.statuskey=1
UNION
SELECT DISTINCT u.employeeid AS accountname,
'SaviyntforSaviynt' AS name,
'SaviyntforSaviynt' AS endpointname,
u.employeeid AS accountcn,
'Risk' AS attribute,
concat(r.RISKNAME, ' RULESET:', rs.RULESET) AS entitlementvalue,
u.username AS username,
u.statuskey AS status,
u.updatedate,
u.employeeid AS objectguid
FROM risks r,
users u,
riskowners ro,
rulesets rs
WHERE r.RULESETKEY=rs.RULESETKEY
AND u.USERKEY = ro.OWNERUSERKEY
AND r.RISKID = ro.RISKID
AND r.status=0
UNION
SELECT DISTINCT u.employeeid AS accountname,
'SaviyntforSaviynt' AS name,
'SaviyntforSaviynt' AS endpointname,
u.employeeid AS accountcn,
'Function' AS attribute,
concat(f.FUNCTION_NAME, ' RULESET:', rs.RULESET) AS entitlementvalue,
u.username AS username,
u.statuskey AS status,
u.updatedate,
u.employeeid AS objectguid
FROM functions f,
users u,
rulesets rs
WHERE f.RULESETKEY=rs.RULESETKEY
AND f.OWNER = u.USERKEY
AND f.ownertype=1
AND f.status=1
UNION
SELECT DISTINCT u.employeeid AS accountname,
'SaviyntforSaviynt' AS name,
'SaviyntforSaviynt' AS endpointname,
u.employeeid AS accountcn,
'BusinessProcess' AS attribute,
b.BUSPROCNAME AS entitlementvalue,
u.username AS username,
u.statuskey AS status,
u.updatedate,
u.employeeid AS objectguid
FROM busprocs b,
users u,
bp_owners bo
WHERE u.USERKEY = bo.USERKEY
AND b.BUSPROCKEY = bo.BUSPROCKEY
AND b.status=1
UNION
SELECT DISTINCT u.employeeid AS accountname,
'SaviyntforSaviynt' AS name,
'SaviyntforSaviynt' AS endpointname,
u.employeeid AS accountcn,
'JRM' AS attribute,
j.JRMRULENAME AS entitlementvalue,
u.username AS username,
u.statuskey AS status,
u.updatedate,
u.employeeid AS objectguid
FROM jrmrules j,
users u
WHERE u.USERKEY = j.owner ;
Also mapping as below
Rushikesh Vartak
