(v1) Scheduled Reports by User

This Query will allow an Admin to see all of the Perspectives that have been created when those Perspectives have an attached Scheduled Report. Using the following Query an Admin can identify Perspectives that may still be running Scheduled Reports, even if that User is no longer Active in the system. From there the Admin can begin to narrow down where the Scheduled Report is coming from to end it.

MySQL Query

SELECT 
  U.RECID AS USERS_RECID,
  U.USERID,
  U.DISPLAY_NAME,
  E.RECID AS EVENTS_RECID,
  E.NEXT_RUN_DATE,
  UP.NAME AS PERSPECTIVE_NAME,
  E.DATA->>"$.gridParams.grid" AS GRID_CLASS,
  E.DATA->>"$.gridParams.scenario" AS GRID_SCENARIO,
  E.DATA->>"$.requestParams.email" AS EMAIL
FROM USERS_PERSPECTIVES UP
LEFT JOIN EVENTS E ON E.RECID = UP.EVENTS_RECID
INNER JOIN USERS U ON U.RECID = UP.USERS_RECID
WHERE EVENT = 'sys-grid-report-email';

Oracle Query

SELECT
  U.RECID AS USERS_RECID,
  U.USERID,
  U.DISPLAY_NAME,
  E.RECID AS EVENTS_RECID,
  E.NEXT_RUN_DATE,
  UP.NAME AS PERSPECTIVE_NAME,
  JSON_VALUE(E.DATA, '$.gridParams.grid') AS GRID_CLASS,
  JSON_VALUE(E.DATA, '$.gridParams.scenario') AS GRID_SCENARIO,
  JSON_VALUE(E.DATA, '$.requestParams.email') AS EMAIL
FROM USERS_PERSPECTIVES UP
LEFT JOIN EVENTS E ON E.RECID = UP.EVENTS_RECID
INNER JOIN USERS U ON U.RECID = UP.USERS_RECID
WHERE EVENT = 'sys-grid-report-email';