https://docs.saviyntcloud.com/bundle/KBAs/page/Content/Fetching-a-consolidated-report-of-Technical-a...
select h.HANARULEKEY as RuleKey,h.name as RuleName,h.RULEDESCRIPTION as RuleDescription,
CASE WHEN h.TYPE=1 THEN 'Technical' WHEN h.TYPE=2 THEN 'User Update' END as RuleType,
CASE WHEN h.BIRTHRIGHT=1 THEN 'Checked' ELSE '' END as Birthright,
CASE WHEN h.REMOVEBIRTHRIGHTONFAIL=1 THEN 'Checked' ELSE '' END as RemoveBirthRightOnFail,
CASE WHEN h.INVOKEBYDETECTIVEJOB=1 THEN 'Checked' ELSE '' END as InvokeByDetectiveJob,
CASE WHEN h.EVENTSOURCE=5 THEN 'User Updated via Import' WHEN h.EVENTSOURCE=6 THEN 'User Updated via API' WHEN h.EVENTSOURCE=4 THEN 'User Updated via UI' WHEN h.EVENTSOURCE=1 THEN 'User Created via UI' ELSE h.EVENTSOURCE END as InvokeByDetectiveJob,
CASE WHEN h.status=1 THEN 'In-Active' else 'Active' END as Statuss,
h.ADVANCEDQUERY,
CASE WHEN ha.OBJECTNAME=3 THEN 'Re-Run Provisioning Rules' WHEN ha.OBJECTNAME=7 THEN 'Enable Account' WHEN ha.OBJECTNAME=-999 THEN 'Create Account' WHEN ha.OBJECTNAME=19 THEN 'Update Account' WHEN ha.OBJECTNAME=12 THEN 'Disable Account' WHEN ha.OBJECTNAME=9 THEN 'Update User' WHEN ha.OBJECTNAME=21 THEN 'Re-Run Selected Technical Rule' WHEN ha.OBJECTNAME=20 THEN 'Generate User Email' ELSE ha.OBJECTNAME END as EntitlementNameOrAction,
CASE WHEN ha.OBJECTTYPE like '%EP%' THEN (select endpointname from endpoints where concat('EP_',endpointkey) = ha.OBJECTTYPE)
WHEN ha.OBJECTTYPE REGEXP '[0-9]' THEN (select entitlementname from entitlement_types where entitlementtypekey = ha.OBJECTTYPE) ELSE ha.OBJECTTYPE END as EntitlementTypeOrEndpoint,
CASE WHEN ha.OBJECTVALUE not like '%,%' and ha.OBJECTNAME != 9 THEN (select endpointname from endpoints where endpointkey = ha.OBJECTVALUE)
WHEN ha.OBJECTVALUE like '333,323,175' THEN (select group_concat(endpointname separator ',') from endpoints where endpointkey in (333,323,175))
WHEN ha.OBJECTVALUE like '323,175' THEN (select group_concat(endpointname separator ',') from endpoints where endpointkey in (323,175))
WHEN ha.OBJECTVALUE like '264,325,290,263' THEN (select group_concat(endpointname separator ',') from endpoints where endpointkey in (264,325,290,263))
WHEN ha.OBJECTVALUE not like '%,%' and ha.OBJECTNAME = 9 THEN (select CONNECTIONNAME from externalconnection where EXTERNALCONNECTIONKEY = ha.OBJECTVALUE)
WHEN ha.OBJECTVALUE is null THEN ''
ELSE ha.OBJECTVALUE END as Endpoint
from hanarule h, hanaruleattribute ha where h.HANARULEKEY=ha.HANARULE
and ha.CONDITIONORACTION = 'ACTN'
order by HANARULEKEY
