Run Controls Catalog Report
In Assure 9.1 , we have the ability to click thru the Assure GUI and execute this Controls Catalog report, showing monthly/quarterly historical counts of executions by Control. We would like to be able to schedule this report to run at a specific time (monthly). Is there a script available that would allow us to do this ?
I looked thru the existing libraries of provided scripts and was not able to locate one.
Below is a example of a provided shell script that allows us to schedule Exports of existing controls with provided parameter settings.
/ifgx/infogix/infogixassure/Assure/Infogix/server-util/default/IA/exportce.sh
Is there something similar that we could use to execute the Controls Catalog Report ?
-
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.
-
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.
-
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,
Please sign in to leave a comment.
Comments
4 comments