and more in a single search tool across platforms. Read the announcement here. |
01/05/2023 05:28 AM
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
Solved! Go to Solution.
01/05/2023 07:09 AM
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>%';
.
01/09/2023 02:37 AM
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
01/09/2023 02:44 AM
Use below query and check columns in any table ( update table name )
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'externalconnection';
01/09/2023 02:49 AM
Thank you Rushi, This is very informative.
Regards,
Sampath
01/05/2023 09:24 AM
You can check & reprocess from
select * from userrulerundata - Store all information
Admin - Policies - Execution Trail
01/05/2023 10:02 AM
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
01/05/2023 10:25 AM
Did you try the query I shared? Does it match your requirement?