Get List of Users who Qualify for BR Technical Rules (via Query/Report)

[Srikanth]

Team,

Is there a way to get list of users who qualify for BR technical rules based on the basic or advanced conditions that are configured in rules.

For example -

User attributes - state = test, country = abc, location = test1

BR Tech Rule - a.state=test and a.country=abc and a.location = test1

As the user qualifies the tech rule, the query/report should show user and tech rules which he qualifies for BR process.

Issue -

As the advanced conditions are stored as string in the hanarule table, you cannot equate this in any where clause. Tried using dynamic variables / session variables, but the db vizualiser or analytics does not support it.

Is there a way to get such list using existing (supported) SQL capabilities of Saviynt ?

[rushikeshvartak]

Query should work in data analyzer share query you tried

[Srikanth]

SELECT
@cond:=advancedquery
FROM
hanarule
WHERE
type = 1 AND status = 0
AND hanarulekey = 820;
SELECT
@sqlstring:=CONCAT('SELECT userkey,username,firstname,lastname,email,secondaryemail,statuskey,systemusername FROM users WHERE ',
@cond);
PREPARE sqlquery FROM @sqlstring;
EXECUTE sqlquery;
DEALLOCATE PREPARE sqlquery;

This is not fully formed, but was trying something similar to this.

[rushikeshvartak]

This is stored procedure replace sqlstring to actual value and test 

[Srikanth]

I do not want to take this approach only as storedprocs maintenance etc is overhead. Thats why checking if there is any other simpler solution anyone built

[rushikeshvartak]

The Query you pasted in stored procedure. Prepare SQL query for report

[Srikanth]

advancequery in hanrule is stored as a string value, you cannot equate it or execute it directly to fetch matching users.