Hello Bighnajit,
I had previously built this script to gather these details from the database, since some of the totals are shared by job type it may take some review of the different types and their to confirm the results. This query would not be able to tell you the number of records that were not promoted, since the number of records that can be promoted seems to be determined before the total number of items to be processed is defined.
Hopefully this may give you a starting point to work from.
SELECT bj.JOB_ID as 'JobId'
,CASE WHEN bj.JOB_TYPE_CODE = 16 THEN 'Clean Up'
WHEN bj.JOB_TYPE_CODE = 48 THEN 'CodeSet Import'
WHEN bj.JOB_TYPE_CODE = 35 THEN 'Copy Data'
WHEN bj.JOB_TYPE_CODE = 46 THEN 'Copy Hierarchy'
WHEN bj.JOB_TYPE_CODE = 40 THEN 'Create Codeset from Repository Data'
WHEN bj.JOB_TYPE_CODE = 51 THEN 'Deduplication Process'
WHEN bj.JOB_TYPE_CODE = 52 THEN 'Deduplication Result Import'
WHEN bj.JOB_TYPE_CODE = 7 THEN 'Delete All'
WHEN bj.JOB_TYPE_CODE = 44 THEN 'Delete Orphan Images'
WHEN bj.JOB_TYPE_CODE = 12 THEN 'Export'
WHEN bj.JOB_TYPE_CODE = 43 THEN 'Generate Variants'
WHEN bj.JOB_TYPE_CODE = 45 THEN 'Image Report'
WHEN bj.JOB_TYPE_CODE = 1 THEN 'Import'
WHEN bj.JOB_TYPE_CODE = 17 THEN 'Import Category/Dynamic Association'
WHEN bj.JOB_TYPE_CODE = 19 THEN 'Migrate EPX Users In'
WHEN bj.JOB_TYPE_CODE = 4 THEN 'Migration In'
WHEN bj.JOB_TYPE_CODE = 11 THEN 'Migration Out'
WHEN bj.JOB_TYPE_CODE = 27 THEN 'Pending Request'
WHEN bj.JOB_TYPE_CODE = 25 THEN 'Pending Request Export'
WHEN bj.JOB_TYPE_CODE = 26 THEN 'Pending Request Import'
WHEN bj.JOB_TYPE_CODE = 38 THEN 'Promote'
WHEN bj.JOB_TYPE_CODE = 37 THEN 'Publication'
WHEN bj.JOB_TYPE_CODE = 29 THEN 'Replace All'
WHEN bj.JOB_TYPE_CODE = 30 THEN 'Replace All by API'
WHEN bj.JOB_TYPE_CODE = 49 THEN 'Retail Product Split/Clone'
WHEN bj.JOB_TYPE_CODE = 39 THEN 'Saved Set Import'
WHEN bj.JOB_TYPE_CODE = 18 THEN 'Snapshot'
WHEN bj.JOB_TYPE_CODE = 9 THEN 'State Manage All'
WHEN bj.JOB_TYPE_CODE = 42 THEN 'Taxonomy Node Update'
WHEN bj.JOB_TYPE_CODE = 5 THEN 'Update All'
WHEN bj.JOB_TYPE_CODE = 10 THEN 'Update All By API'
WHEN bj.JOB_TYPE_CODE = 3 THEN 'Validation'
END AS 'JobTypeCode'
,COALESCE(TOTAL_ITEMS,0) AS TOTAL_ITEMS
,COALESCE(NUM_UPDATES,0) AS NUM_UPDATES
,COALESCE(NUM_ATTR_ERRORS,0) AS NUM_ATTR_ERRORS
,COALESCE(NUM_INSERTS,0) AS NUM_INSERTS
,COALESCE(NUM_ITEM_ERRORS,0) AS NUM_ITEM_ERRORS
,COALESCE(NUM_UPDATES,0) AS NUM_UPDATES
, CASE WHEN bj.STATUS = -1 THEN 'Error'
WHEN bj.STATUS = 0 THEN 'Queued'
WHEN bj.STATUS = 1 THEN 'Completed'
WHEN bj.STATUS = 2 THEN 'Aborted'
WHEN bj.STATUS = 99 THEN 'Processing'
END AS 'JobStatus'
, CASE WHEN bmr.NAME IS NULL THEN 'N/A' ELSE bmr.NAME END AS 'RepositoryName'
, CASE WHEN bu.LOGIN IS NULL THEN 'N/A' ELSE bu.LOGIN END AS 'UserLogin'
, COALESCE(bjh.FILENAME,'') AS 'ExportFile'
, bjh.LOGFILE AS 'LogFile'
, bj.APPSERVER_JNDI AS 'JobQueue'
, bj.START_TIME
FROM B_JOB bj
LEFT JOIN B_MASTER_REPOSITORY bmr on bmr.MASTER_REPOSITORY_ID = bj.MASTER_REPOSITORY_ID
LEFT JOIN B_USER bu ON bu.USER_ID = bj.USER_ID
LEFT JOIN B_JOB_HISTORY bjh on bjh.JOB_ID = bj.JOB_ID
------------------------------
Technical Practice Lead | Enterworks Practice
Amplifi Inc. |
www.goamplifi.comBased in Scottsdale,AZ, USA
------------------------------
Original Message:
Sent: 09-15-2023 05:57
From: Bighnajit Mishra
Subject: SQL Queries to view operation metrics
Hello Everyone,
I am looking for a query which can give me details of operation that happens in the Enable environment.
For example,
If a import job ran for repository X. I want to know how many record did the Import process Inserted, Updated or Rejected. I know it can be found from sync log, but I am looking for a sql query option for better reporting.
Similarly, for promotion. How many got promoted or how many failed to promote.
Thanks
Bighnajit
------------------------------
Bighnajit Mishra
lululemon athletica
------------------------------