User Update Rule VS Technical Rule

[krishna_sk]

HI Experts,

Currently we are working on the following usecase-

Report to show all the User update rules and the list of Technical rules it has

We want the following columns in the report 

Userupdate rule Name, User update rule description & Techincal Rule name 

We tried the following SQL QUERY's and we are getting the Technical Rule key in the ObjectValue column (that too having more than 1 entries seperated by comma).

select hr.Name as 'Rule Name',hr.description 'Rule Description',hra.objectvalue as 'Technical Rule Key' from hanarule hr join hanaruleattribute hra on hr.hanarulekey=hra.hanarule where hra.conditionoraction like 'ACTN' and hr.type=2 and hra.objectname=21 and hr.name like 'RBAC%'

We even tried using the STRING_SPLIT but it didn't worked.

select hr.Name as 'Rule Name',hr.description 'Rule Description',STRING_SPLIT(hra.objectvalue,',') as 'Technical Rule key' from hanarule hr join hanaruleattribute hra on hr.hanarulekey=hra.hanarule where hra.conditionoraction like 'ACTN' and hr.type=2 and hra.objectname=21 and hr.name like 'RBAC%'

Could you please help us with your insights on this.

Thank you

[dgandhi]

Hi,

Please find below document and query for reference:

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

Thanks

[krishna_sk]

Thank you for the Response Devang, We do referred the above freshdesk link and build the following query and we do getting the response but not in the expected manner.PFA for the response.

select hr.Name as 'Rule Name',hr.description 'Rule Description',hra.objectvalue as 'Technical Rule Key' from hanarule hr join hanaruleattribute hra on hr.hanarulekey=hra.hanarule where hra.conditionoraction like 'ACTN' and hr.type=2 and hra.objectname=21 and hr.name like 'RBAC%'

You can observe the Technical Rule column having multiple ID's of Technical rules(83,84,85,86,87). we want them to show as Technical rule name instead of the Rule ID.

We need help with the SQL query or the logic to build this.

Appreciate your help, Thank you.

 

[dgandhi]

Check this:

select hr.Name as 'Rule Name',hr.description 'Rule Description',ha.OBJECTNAME=21 THEN 'Re-Run Selected Technical Rule' WHEN ha.OBJECTNAME=20 THEN 'Generate User Email' ELSE ha.OBJECTNAME END as 'Technical Rule Key' from hanarule hr join hanaruleattribute ha on hr.hanarulekey=ha.hanarule where ha.conditionoraction like 'ACTN' and hr.type=2 and ha.objectname=21 and hr.name like 'RBAC%'

Thanks

[krishna_sk]

Hi Devang,

The above query didn't fulfill our requirement. We need the role name inside the technical Rule but not the object types.

 

Thank you.

[Darshanjain]

Hi @krishna_sk 

Here you go, You can use the below query to get the Technical rules selected in user update rules

SELECT hr.Name AS 'Rule Name',
hr.description AS 'Rule Description',
CASE
WHEN ha.OBJECTNAME = 21 THEN 'Re-Run Selected Technical Rule'
WHEN ha.OBJECTNAME = 20 THEN 'Generate User Email'
WHEN ha.OBJECTNAME = 3 THEN 'Re-run All prov rules'
END AS 'TechnicalRuleKey',
hr2.Name AS 'Hanarule Name'
FROM hanarule hr
JOIN hanaruleattribute ha
ON hr.hanarulekey = ha.hanarule
JOIN hanarule hr2
ON ha.OBJECTVALUE = hr2.hanarulekey
WHERE ha.conditionoraction LIKE 'ACTN'
AND hr.type = 2
AND ha.OBJECTNAME IN (21, 3);

Note: You can modify the query as per your requirement.

Thanks

Darshan  

[krishna_sk]

---

Thank you @Darshanjain for the Query.

The above query returns only one technical rule name (PFA) where we have multiple technical rules (Rulekey but not the Rulename) in a single User update rule, which are separated by comma (,).

Can you please help me with this which will list all the Rule names.

The below attachment will give you more idea on the requirment.

 

Thank you.

Sai Krishna

---

[Darshanjain]

Okay then you may need to figure out how you can split those values accordingly, if you see the first query you will understand how to split those values and get the names. you should be able to get that sorted out.

Thanks

Darshan

[krishna_sk · Accepted solution]

Thank you all for your insights😊. We achieved the use case using the below query.

SELECT hr.Name AS 'User Update Rule Name',hr.RULEDESCRIPTION AS 'User Update Rule Description',(select hr1.name from hanarule hr1 where hanarulekey=
REPLACE(TRIM(SUBSTRING(SUBSTRING_INDEX(hra.objectvalue, ',', numbers.n),
LENGTH(SUBSTRING_INDEX(hra.objectvalue, ',', numbers.n - 1)) + 1)),',','')) AS 'Technical Rule Name'
FROM hanarule hr
JOIN hanaruleattribute hra ON hr.hanarulekey = hra.hanarule
JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10) numbers ON CHAR_LENGTH(hra.objectvalue) - CHAR_LENGTH(REPLACE(hra.objectvalue, ',', '')) + 1 >= numbers.n
WHERE hra.conditionoraction LIKE 'ACTN'
AND hr.type = 2
AND hra.objectname = 21
AND hr.name LIKE '%RBAC%'
AND hr.status=0
ORDER BY hr.Name ASC, CAST(`ObjectValue` AS UNSIGNED) ASC;