Export Technical Rules with Last update date

[AS5278]

Hi All,

Is it possible to export all the technical rules in Saviynt along with the 'Last update date' and 'Last updated by' ?. Currently I am able to export it bu these two columns are not present in the exported report.

Please advice.

Thanks,

Atul Singh

[Manu269]

From UI export I dont see an option to determine the attributes.

[rushikeshvartak]

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

[AS5278]

@rushikeshvartak 

I have framed below analytics query for extracting technical rules:

select distinct 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.status=1 THEN 'In-Active' else 'Active' END as State, h.UPDATEDATE as 'Last Updated', u.username as 'Last Updated By', h.ADVANCEDQUERY, h.ADVANCEDCONFIG, ha.OBJECTVALUE from hanarule h join hanaruleattribute ha on h.HANARULEKEY=ha.HANARULE join users u on u.userkey=h.updateuser where ha.CONDITIONORACTION = 'ACTN' and h.TYPE=1 and h.status!=1;

The report is generating fine however the actual technical rule is not getting included in the report.

I am selectting the 'ADVANCEDQUERY' column from hanarule table. But this column is coming as empty for all the rules.

How do I include the actual technical rule in the report?.

For example:

If Users.statuskey EQUALS "1"
AND Users.secondaryEmail CONTAINS "abc.com"
AND Users.User Type EQUALS "E"
AND Users.username DOES NOT CONTAIN "@"
AND Users.username DOES NOT CONTAIN "."
Then
Create Account on Oracle Analytics Cloud(OAC)

This rule also needs to be included in the analytics report.

Thanks,

Atul Singh

[rushikeshvartak]

Its working for me in v24.4

select distinct 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.status=1 THEN 'In-Active' else 'Active' END as State, h.UPDATEDATE as 'Last Updated', u.username as 'Last Updated By', h.ADVANCEDQUERY, h.ADVANCEDCONFIG, ha.OBJECTVALUE from hanarule h join hanaruleattribute ha on h.HANARULEKEY=ha.HANARULE join users u on u.userkey=h.updateuser where ha.CONDITIONORACTION = 'ACTN' and h.TYPE=1 and h.status!=1

and name='test1'

[Manu269]

Validated in 23.9,23.10 its working for me

[AS5278]

What if the rule was not configured using 'advanced query' but was configured using the normal drop down selection?. In that case how to include the rule in the report?.

[rushikeshvartak]

Its already included