How to UNION data from approval and task tables?

[chuckh_]

Hi all,

      Auditors are looking for a one-stop view into the details of our changes, which includes change details coming from both the change task and the approval tables.   In short, we need to union rows from each of the two tables to chronologically show the work and the approvals that occurred for each change.   I have a sample query below which runs against an ODBC connection, but I need to know if something more efficient can be created 'within' the tool so that a scheduled report can be run and delivered instead of running/exporting SQL query results.

Query:

SELECT ct.dv_change_request AS ChangeNbr,
      'Task' as Type,
      ct.short_description AS Acty,
      ct.dv_state AS Status,
      convert(ct.closed_at,timestamp) AS Timestp,
      ct.dv_closed_by AS Auth_By,
      su.email as EMail
FROM change_task ct,
      sys_user su
  WHERE su.sys_id = ct.closed_by AND ct.dv_change_request='CHGXXXXXXX'

UNION ALL

SELECT sa.dv_sysapproval AS ChangeNbr,
      'Appr' as Type,
      sa.u_activity AS Acty,
      sa.dv_state AS Status,
      convert(sa.sys_updated_on,timestamp) AS Timestp,
      sa.dv_approver AS Auth_By,
      su.email as EMail
FROM sysapproval_approver sa,
                      sys_user su
WHERE su.sys_id = sa.approver AND sa.dv_sysapproval='CHGXXXXXXX' AND sa.dv_state='Approved'

ORDER BY 5;

Has anybody had to do something similar?   If so, how did you accomplish it?

Thanks in advance,

      Chuck H.

[drjohnchun]

What's not clear to me is why Approval History is capturing only the last approval - being a journal field, either it should capture all or nothing, as far as I understand.

I've done some more digging and we'll need to check 2 places to see if Approval History has been property enabled:

1. In the Navigator filter, type "sys_properties.list". Once the System Properties list comes up, look for the property named "glide.workflow.user_approval_history" - it should be set to "true"
2. Locate the Business Rule "Approval Events (Task)" and make sure it's Active. If you'd like, you can review the script and see that it's adding journal entries for approval/rejection events, along with comments.

I have a suspicion that these two are already enabled in your case, but let's see.

There's a detailed discussion on this feature at Approver comments on Task, which may be of help. The field is described in the wiki at Task Table - ServiceNow Wiki

Sorry, I haven't been able to nail this - hope this is all worth the effort in the long run.

[chuckh_]

I'll need somebody to check on the sys_properties.list in production.   However, I have privileges in test and can tell you that our test instance has "glide.workflow.user_approval_history" set to "false" AND that's a clone of production from just a few weeks ago.   So I think we're getting somewhere....

If the outcome is that it's also false in production, is it just a matter of enabling it?   Are there valid reasons for it being false?   What are the tradeoffs to enabling it?

Thank you again.

[drjohnchun]

Can you set "glide.workflow.user_approval_history" to true in the test instance and see if it works? The only downside is the added data entry in the task.approval_history field not just for Change but other records as well, such as Request, where approvals/rejections take place in workflows (but this may be desirable, in your case).

[chuckh_]

Looks like that worked!

In TEST,

* BEFORE changing glide.workflow.user_approval_history to true

* AFTER:

[drjohnchun]

Well, there you go. Happier New Year now with the Approval History on your Change records (and keep the auditors happy too)!