How to create report to get activity on Notes Tab from Change Request

[lkm]

I created a report using Change Task [change_task] table and can view the Work Notes (number 3 below screenshot) and Additional Comments(number 2 below screenshot)on the Notes Tab. I can't figure out field in number 1 below. Number 3 has yellow bar on left side of box. Number two has black bar on left side of box. Number one is grey. If I hover on right side where date and time stamp for box is it will tell me Additional Comment (2) or work notes (3). I don't get anything if I hover over date and time on number 1 below. I believe system creates this note when the change is made. I am trying to create a report for when the planned start and end date gets modified (Number 1 below shows example of this). I would like to create report to capture that information. I am currently on "Helsinki" version of ServiceNow. How can I get this information on report?

Thanks,

Linda

[Dennis R]

If you'd like a report on when the start/end dates get changed, I don't think that querying the work notes is the way to go.

Have you looked into metrics? In short, you can set up a metric on the date fields in question, and then query the metric_instance table for the appropriate information. If you want the change info available in the report, you'll probably also want to create a database view to join the metric and change request table.

Let me know if you need any more details, but I'd definitely suggest taking a look at metrics in the documentation.

Hope this helps,

--Dennis R

[lkm]

I do not want to change the information. I just want to report when the planned start date or planned end date has changed for a particular change request.

Are you saying that information is not stored or I am unable to access the information so I should create a metric to store the information so I can get that information?

I am fairly new to ServiceNow and do not have the access role to create metric but need to get the information.

Thanks,

Linda

[Adam Stout]

A metric won't get you the before and after, just the count of changes and when.   If that is enough, that is the way to go.   If not, all the changes are in sys_audit.   But that is not a table you want to report on (if you can).   You may want to create a custom table that logs just these changes with before and after.   Basically a sys_audit light for reporting on this.   You can backload the data with a fix script from sys_audit as well to hit the ground running.

[Dennis R]

Hey Linda, yes, that's the gist of it.

To create a metric, on your left-hand navigation menu, go to Metrics > Definitions. Create a new definition with the following information:

• Name: Change planned start
• Table: Change Request [change_request]
• Field: Planned start date
• Type: Field value duration
• Active: Selected
• Description: Creates a metric instance when the planned start date of a change is edited

Create another metric called Change planned end with the same values as above, except with the Field set to Planned end date and the Name and Descriptions updated appropriately. These metric definitions should look something like this:

When done, you will get an entry in the Metric [metric_instance] table whenever anyone changes the planned start or end time of a metric. You can create a report to hit against that table to collect the number of times, along with when it was changed, and the new planned date (in the Value field):

Hope this helps,

--Dennis R