Fetching Child Entitlement Value from Data Analyzer

[vmudagal1]

Hi Team, 

Could anyone please let know how I can fetch the Child Entitlement Value, Custom Properties using data analyzer? 

Currently I am able to fetch parent entitlement value, but not able to select child entitlement value column as well.

I would need the result to look like below:

Endpointname | ParentEntitlement | ChildEntitlement | ChildEntitlement_Custom Property12 | Accountname

Select
E.endpointname,
EV.entitlement_value as ParentEntitlement,
A.name
FROM
ENDPOINTS E,
entitlement_types ET,
entitlement_values EV,
entitlements2 EV2,
account_entitlements1 AE1,
accounts A
Where
E.endpointkey = ET.endpointkey
and EV.entitlementtypekey=ET.entitlementtypekey
and EV2.entitlement_value1key=EV.entitlement_valuekey
and EV.entitlement_valuekey=AE1.entitlement_valuekey
and AE1.accountkey=A.accountkey
and E.endpointkey='37'

Thanks, 

[Dhruv_S]

Please refer to the following post for tables related to the child entitlement values. Please let us know if you have further questions.

Solved: Query to find child entitlement. - Saviynt Forums - 28197

Regards,

Dhruv

[vmudagal1]

Hi @Dhruv_S & All,

We are trying to create an analytics report where the requirement according to client is as below:

Entitlement Type: Privileged Account 

Table 1: Data Imported to Saviynt looks as below for entitlment Type Privileged Account:

Entitlement_value	customproperty12	customproperty5	customproperty6	customproperty3	customproperty1	Accountname
Database-ABCD-ADSHS-DB-RDA-OID-SH-advc.test.net-PAM-SRT-ADSHS-T-A-M3	TRGS465473.advc.test.net;TDSH5633.advc.test.net;TRSTS35354.advc.test.net;STRF48987.advc.test.net	Local Windows Server	DEV	ADSHS-RIM-SET-COD-T	RIM-SAV-ADSHS-COD-A	TestUser1

Table 2: Analytics Report is expected to fetch data as below representation from above imported data:

Entitlement_value	customproperty12	customproperty5	customproperty6	customproperty3	customproperty1	AccountName
Database-ABCD-ADSHS-DB-RDA-OID-SH-advc.test.net-PAM-SRT-ADSHS-T-A-M3	TRGS465473.advc.test.net	Local Windows Server	DEV	ADSHS-RIM-SET-COD-T	RIM-SAV-ADSHS-COD-A	TestUser1
Database-ABCD-ADSHS-DB-RDA-OID-SH-advc.test.net-PAM-SRT-ADSHS-T-A-M4	TDSH5633.advc.test.net	Local Windows Server	DEV	ADSHS-RIM-SET-COD-T	RIM-SAV-ADSHS-COD-A	TestUser1
Database-ABCD-ADSHS-DB-RDA-OID-SH-advc.test.net-PAM-SRT-ADSHS-T-A-M5	TRSTS35354.advc.test.net	Local Windows Server	DEV	ADSHS-RIM-SET-COD-T	RIM-SAV-ADSHS-COD-A	TestUser1
Database-ABCD-ADSHS-DB-RDA-OID-SH-advc.test.net-PAM-SRT-ADSHS-T-A-M6	STRF48987.advc.test.net	Local Windows Server	DEV	ADSHS-RIM-SET-COD-T	RIM-SAV-ADSHS-COD-A	TestUser1

QUERY USED TO FECTH ABOVE DATA IN TABLE 2

select distinct SUBSTRING_INDEX(SUBSTRING_INDEX(ev.customproperty12, ';', numbers.n), ';', -1) as 'ServerName',
EV.customproperty5 as 'Application Service',
EV.customproperty6 as 'Environment',
EV.customproperty3 as 'Safe',
EV.customproperty1 as 'Account Name',
A.name as 'User Name'
from
(select 1 n union all
select 2 union all select 3 union all
select 4 union all select 5) numbers INNER JOIN entitlement_values EV
on CHAR_LENGTH(EV.customproperty12)
-CHAR_LENGTH(REPLACE(EV.customproperty12, ';', ''))>=numbers.n-1
INNER JOIN entitlement_types ET on EV.entitlementtypekey=ET.entitlementtypekey
INNER JOIN endpoints E on E.endpointkey=ET.endpointkey
INNER JOIN entitlements2 ET2 on ET2.entitlement_value2key = EV.entitlement_valuekey
INNER JOIN entitlement_values EV2 on ET2.entitlement_value1key = EV2.entitlement_valuekey
INNER JOIN account_entitlements1 AE1 on EV2.entitlement_valuekey=AE1.entitlement_valuekey
INNER JOIN accounts A on AE1.accountkey=A.accountkey
where
E.endpointkey=37

NOTE: We are trying to fetch all values separated by delimiter(;) in rows  (Column reference Table 1 customproperty12)

We are able to fetch desired report using below query in Data Analyzer but see below error message when using same query in Creating an Analytics Report for the same query. 

When clicking on "Preview" in Analytics I able to see the data being fetched only while clicking on "Create" to save the analytics seeing below error message

Please let know how I can overcome this error as the query is working fine in Data Analyzer. Cross apply String Split() functions is also not acceptable. Provide guidance on what is supported in Analytics Report for query.

Thank you, 

Vidya D Mudagal

[Dhruv_S]

Could you please provide screenshot of sample output from the same query from data analyzer.

[vmudagal1 · Accepted solution]

Hi @Dhruv_S

This was resolved by giving a space before and after operators (>=)

-CHAR_LENGTH(REPLACE(EV.customproperty12, ';', ''))>=numbers.n-1 

-CHAR_LENGTH(REPLACE(EV.customproperty12, ';', '')) >= numbers.n-1

Thank you, 

Vidya D Mudagal

[Dhruv_S]

Thanks for the update. Could you please accept it as Solution to help others who may have a similar problem.