Is it possible to query the AVDB and retrieve the monitors for each review?
I tried the review definition table which has a monitor but that value is same as the review owner even though the review has multiple monitors set.
on Jan 6, 2017SELECT
user_id, first_name, last_name
FROM
PV_USERS
WHERE ID IN (SELECT monitor FROM T_AV_ENTITLEMENTREVIEWDEF)See if the information you are looking for can be found in one of the following:
Boris Lekumovich The table T_AV_REVIEW_MONITORCOUNTS has details for the monitors for a review run and not the review definition. There are no entries if review hasn't been run.
SELECT reviews.name, users.user_id, users.first_name, users.last_name FROM T_AV_REVIEW_MONITORCOUNTS monitors
INNER JOIN T_AV_REVIEWS reviews
ON reviews.id = monitors.review_id
INNER JOIN PV_USERS users
ON monitors.user_id = users.id
The same is true for PV_REVIEW_MONITOR_SUMMARY
I think you can use the MONITOR_ID from PV_REVIEW_MONITOR_SUMMARY to see the monitors.
Yeswanthraj Raja I did look at this earlier but it has 2 entries from the same review.
SELECT
user_id, first_name, last_name
FROM PV_USERS WHERE ID IN (SELECT MONITOR_ID FROM PV_REVIEW_MONITOR_SUMMARY )This query returns a monitor for a ServiceNow review but does not have the monitors for the below listed query.
Hi Pradeep, the actual monitors are only calculated at the time of review generation because the monitor list can change based on generated review. For example if you have supervisors of users as monitors, the monitor list will depend on what users are included in the review and who their supervisors are at that time. So, such monitor list can not be resolved before generating the review.
The monitor list would remain constant for the case you displayed where you explicitly pick the users as monitors for entire review. Till the review is generated, this list of users as well is only stored in an xml configuration. Check out the column alt_monitor_def on review definition table t_av_entitlementreviewdef
That makes sense. Thanks for the explanation.
Hi Pradeep, the actual monitors are only calculated at the time of review generation because the monitor list can change based on generated review. For example if you have supervisors of users as monitors, the monitor list will depend on what users are included in the review and who their supervisors are at that time. So, such monitor list can not be resolved before generating the review.
The monitor list would remain constant for the case you displayed where you explicitly pick the users as monitors for entire review. Till the review is generated, this list of users as well is only stored in an xml configuration. Check out the column alt_monitor_def on review definition table t_av_entitlementreviewdef