Announcing the SAVIYNT KNOWLEDGE EXCHANGE unifying the Saviynt forums, documentation, training, and more in a single search tool across platforms. Click HERE to read the Announcement.

Query showing null data on left joiner

gagan94
Regular Contributor
Regular Contributor

Hi Team,

I have to create one recon report for specific one endpoint but in that we are also getting null values from user data, Can anybody help me out in correcting the one of getting only active and inactive data of users.
I am using below query for getting data.

select ss.SYSTEMNAME as 'Security_System',ep.endpointname as 'Endpoint_Name',r.role_name as 'Rolename',ev.ENTITLEMENT_VALUE as 'Entitlemenet name',u.username as 'USERNAME',u.displayname as 'DISPLAYNAME',CASE U.STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS USERSTATUS,acc.NAME as 'AccountName' from accounts acc left join user_accounts ua on acc.accountkey=ua.accountkey left join endpoints ep on ep.endpointkey=acc.endpointkey left join users u on u.userkey=ua.userkey left join account_entitlements1 ae on ae.accountkey = acc.accountkey left join entitlement_values ev on ev.entitlement_valuekey=ae.entitlement_valuekey left join entitlement_types et on et.entitlementtypekey = ev.entitlementtypekey left join securitysystems ss on ep.SECURITYSYSTEMKEY=ss.SYSTEMKEY left join roles r on r.rolekey=ae.ASSIGNEDFROMROLE left join role_entitlements re on ev.entitlement_valuekey=re.entitlement_valuekey and re.rolekey=r.rolekey where ss.systemname='Workday' and acc.status in ('1','Active','Manually Provisioned') and r.role_name='rolename'.

I am also attaching the screenshot to get better clarity.

Regards
Gagan

9 REPLIES 9

sk
All-Star
All-Star

User details are not coming because those accounts are not correlated to any user in saviynt. In that case you will not find any user details. That is the expected behaviour as per your query.

What is your actual requirement? Which will help us to modify the query accordingly


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

gagan94
Regular Contributor
Regular Contributor

but We are getting account details for all these null data.
Requirement is to remove these null data from report. may be due to left join it is causing issue.

requirement is to get data for specific endpoint with below mentioned fields,

Security_SystemEndpoint_NameRolenameEntitlemenet nameUSERNAMEDISPLAYNAMEUSERSTATUSAccountName

Yes that's exactly I am trying to explain. You have some of the accounts from respective endpoint but those accounts are not correlated to any user in Saviynt. Hence you are missing user details. You can eliminate those null values by doing inner join instead of left join. But problem is if you do inner join then you will miss some accounts who are not correlated to any user.

If your requirement is to get all accounts from a respective endpoint then I would suggest to simply remove the join with user table and respective columns.

But if your requirement is to get all accounts on a particular endpoint and respective correlated user details then you will expect to see those null values where accounts are not correlated to any user.


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

data is coming proper share final query


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

gagan94
Regular Contributor
Regular Contributor

@rushikeshvartak :

Below find the latest query

select ss.SYSTEMNAME as 'Security_System',ep.endpointname as 'Endpoint_Name',r.role_name as 'Rolename',ev.ENTITLEMENT_VALUE as 'Entitlemenet name',u.username as 'USERNAME',u.displayname as 'DISPLAYNAME',CASE U.STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS USERSTATUS,acc.NAME as 'AccountName' from accounts acc left join user_accounts ua on acc.accountkey=ua.accountkey left join endpoints ep on ep.endpointkey=acc.endpointkey left join users u on u.userkey=ua.userkey left join account_entitlements1 ae on ae.accountkey = acc.accountkey left join entitlement_values ev on ev.entitlement_valuekey=ae.entitlement_valuekey left join entitlement_types et on et.entitlementtypekey = ev.entitlementtypekey left join securitysystems ss on ep.SECURITYSYSTEMKEY=ss.SYSTEMKEY left join roles r on r.rolekey=ae.ASSIGNEDFROMROLE left join role_entitlements re on ev.entitlement_valuekey=re.entitlement_valuekey and re.rolekey=r.rolekey where ss.systemname='Workday' and acc.status in ('1','Active','Manually Provisioned') and r.role_name='rolename'

Regards,

Gagan

gagan94
Regular Contributor
Regular Contributor

Hey @rushikeshvartak : did you get a chance to review my last query. Kindly suggest what we can do next to eliminate such null values from current report.

Regards,

Gagan

Did you get chance to looks at my last response?

You can eliminate those null values by doing inner join instead of left join. But problem is if you do inner join then you will miss some accounts who are not correlated to any user.

If your requirement is to get all accounts from a respective endpoint then I would suggest to simply remove the join with user table and respective columns.

But if your requirement is to get all accounts on a particular endpoint and respective correlated user details then you will expect to see those null values where accounts are not correlated to any user.


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

Share sample user data from vs expected in query


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

gagan94
Regular Contributor
Regular Contributor

Hey,
@rushikeshvartak : I have shared the data in attachment, it is available under my first post of this thread, please have look on this one and let me know is there any solution to avoid null values.

Regards,

Gagan