Change Task count by updated by

[rberninim]

I'm building a dashboard and I need to find a way to have a metric for Change Tasks, to count by analyst the interactions with such CTASK, even if not assigned or close by them, I currently have a criteria for change task metric and group by Updated by (cht_sys_updated_by) but is only measures and count the last person that entered an update on CTASK comments/notes, I need to count not only the last person but everyone who did an update to the journal

[UPPADAM]

Hi @rberninim ,

-> Create a Report or Indicator Source Based on sys_journal_field
Go to Reports or Performance Analytics > Indicator Sources and follow these conditions:

Table: sys_journal_field

Filter:

element_id → belongs to change_task table → use script or dot-walk

You can filter like: Element is work_notes or comments

Optional: Add element_id.table is change_task (in script or advanced condition)

element_id.sys_class_name is change_task — (if available)

sys_created_on → (use time range as needed)

-> Group by Analyst
Group by sys_created_by to get the actual person who made the journal entry.

Optional: Group by element_id.number to get counts per Change Task.

-> Create Dashboard Widget
Use the report or indicator you created to build a dashboard widget.

or else

i have used bgs to run a script to count.
Script:

var gr = new GlideRecord('sys_journal_field');
gr.addQuery('element_id.sys_class_name', 'change_task');
gr.addQuery('element', 'IN', 'work_notes,comments');
gr.query();
var analystCount = {};

while (gr.next()) {
var user = gr.sys_created_by.toString();
if (!analystCount[user]) {
analystCount[user] = 0;
}
analystCount[user]++;
}

Please Mark : Correct if this solves your query and also mark: Helpful if you find my response worthy based on the impact.

Regards,
Mahesh Babu Uppada

[rberninim]

Hi Uppada

Thanks a lot for your answer, however Table: sys_journal_field or indicator sys_journal_field which is the core for this query is not popping up under table or indicators options, attached the image of it...

How can I get such table or indicator added?

[UPPADAM]

Hi @rberninim ,

Go to System Definition > Tables.
Search for: sys_journal_field.
Open the table record.
Check the box for: "Allow access to this table via web services"
(Optional but recommended) Check: "Can Read" or "Allow configuration"
Save the record.

Reload Data Sources
Now go back to your dashboard/report/indicator creation area and try again:
Search again for sys_journal_field as a table
It should now appear

If you're still not seeing it:
Make sure your role has visibility to the table:
Either add admin role (temporarily) or
Add your role to the Table > Access Control Rules for sys_journal_field.

Try this in a background script - to confirm table is accessible or not:
var gr = new GlideRecord('sys_journal_field');
gr.addQuery('element_id.sys_class_name', 'change_task');
gr.query();
gs.info('Count of journal entries on change tasks: ' + gr.getRowCount());

Please Mark : Correct if this solves your query and also mark: Helpful if you find my response worthy based on the impact.

Regards,
Mahesh Babu Uppada

[rberninim]

Ok, I guess I need to talk to the sys admin for SaaS because I'm afraid I have no access for the below

Go to System Definition > Tables.
Search for: sys_journal_field.
Open the table record.
Check the box for: "Allow access to this table via web services"
(Optional but recommended) Check: "Can Read" or "Allow configuration"
Save the record.

Reload Data Sources

Unable to find system definition on menu