We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

Run Controls Catalog Report

Comments

4 comments

  • Avatar
    Brenda Stoetzer

    Hi John,

    There isn’t a script to trigger the Controls Catalog Report; however, you can use the SQL below to achieve the same output as the report. Note that we don’t generally recommend tapping into the database directly since the structure may change release by release, but this would provide a solution to your needs.

     

    SELECT PROCESSGROUP.NAME AS "Control Entity", PROCESSDEFINITION.DISPLAYNAME AS "Control Point", PERIODYYYYMM, EXECUTIONCOUNT

    FROM PROCESSCOUNTS

    INNER JOIN PROCESSCOUNTPD ON PROCESSCOUNTPDPK=PROCESSCOUNTPDFK

    INNER JOIN PROCESSDEFINITION ON PROCESSDEFINITIONFK=PROCESSDEFINITIONPK

    INNER JOIN PROCESSGROUP ON PROCESSGROUPFK=PROCESSGROUPPK

    WHERE PROCESSGROUPFK>1

    ORDER BY PERIODYYYYMM DESC

     

    It’s also possible to achieve the same goal via Data360 DQ+. Assure sends execution results to DQ+ for the purpose of custom reports and dashboards. More details on this is in the “Control Execution Results to Data360 DQ+” section of the Assure 9.3 Release Notes.

    0
    Comment actions Permalink
  • Avatar
    John Love

    thanks Brenda,   we'll see what we can do with this.

    0
    Comment actions Permalink
  • Avatar
    Brian Butters

    John,

    If you take the data approach, the PROCESSCOUNTS table has two additional columns that don't get on the controls catalog report - RECORDSPROCESSED and EXECUTIONSECONDS. RECORDSPROCESSED is the sum of "Total Instances" you see on a result catalog for the control point, and EXECUTIONSECONDS is finish time - start time in seconds for the catalogs.

    While it is true that Assure can send execution results to DQ+, you don't have in DQ+ a catalog of all the control points of the Assure system available as data that is pushed by Assure to DQ+. The reason I bring this up is the query provided here will only list those control points that have counts in a period. If you do want to take a data oriented approach, I can give you an adjusted query that would include all control points in the system, not just those with count data.

    0
    Comment actions Permalink
  • Avatar
    John Love

    thanks Brian -  my intention with being able to schedule/automate this Catalog Controls report was to get monthly executions counts. I thought if I could schedule that report I could then feed it into an another process all just to grab those counts.  We will just manually execute the report , I appreciate it both you and Brenda for providing some feedback with other options to get these numbers,

    0
    Comment actions Permalink

Please sign in to leave a comment.