Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

How to get report of all users on which a particular update rule was triggered

sampath18
Regular Contributor II
Regular Contributor II

Hi Guys,

We need to get a report of users on which a particular rule was triggered . Is there a table in saviynt that stores this information directly or indirectly. Can you please provided an advise on this.

 

Thanks

Sampath

7 REPLIES 7

Saathvik
All-Star
All-Star

Try below. You won't be able to run this query from Data Analyzer if you don't see usershistory table, generally this table is not exposed . So please use analytics to run this query.

 

select u.USERKEY, u.username from users u inner join usershistory uh on u.userkey=uh.userkey where uh.RULERUNS like '%<Rule_NAME>%';


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

sampath18
Regular Contributor II
Regular Contributor II

Hi,

The query is working well. But how to get to know which columns are available in this table. i am asking because this table is not available in data analyzer and we can not execute select * from table in analytics too.

Also, i have tried another query using table called "USERRULERUNDATA_ARCHIVE". This works but it was taking time to excute.

 

select U.USERNAME,U.USERKEY,UA.RULEACTIONSUCCESSFUL,UA.EVENTSOURCE,UA.RULETYPE,UA.USERRULERUNDATA_ARCHIVEKEY,UA.UPDATEDATE as Updateddate,UA.CHANGEMAP
from USERRULERUNDATA_ARCHIVE UA
INNER JOIN USERS U
ON UA.USERKEY = U.USERKEY
where UA.UPDATEDATE like '<date>' AND UA.RULETYPE ='<Rule type number>' AND UA.RULEACTIONSUCCESSFUL LIKE '%<rulename>%'

Thanks

Sampath

Use below query and check columns in any table ( update table name )

SELECT COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS  WHERE  TABLE_NAME = 'externalconnection';


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

sampath18
Regular Contributor II
Regular Contributor II

Thank you Rushi, This is very informative.

Regards,
Sampath

rushikeshvartak
All-Star
All-Star

You can check & reprocess from 

select * from userrulerundata - Store all information

Admin - Policies - Execution Trail

rushikeshvartak_0-1672939368911.png

 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

sampath18
Regular Contributor II
Regular Contributor II

Hi Rushi,

Thanks for the response. I had already looked into excution trail before. but that does not give  information about which rule had triggered for the user. So here we are looking to fetch a report that contains users impacted by a particular rule. The table 'userrulerundata' also does not have that information. Moreover this table has userkey but that is not matching with users table which looks incorrect for me. Hope userkey in this table is a FK of userkey in users table. 

 

Thanks
Sampath

Did you try the query I shared? Does it match your requirement?


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.