Create Report Using Worknotes of Tickets

Raj_Nishant92 · ‎03-14-2023

Hi,

I want to create a report for Incidents using worknotes and it should provide me the output where the incidents

are not being touched by a particular user ( suppose , Automation user).

I am unable to generated worknotes in the query field of report creation.

#Analytics #performance Analytics

BharathChintala · ‎03-14-2023

@Raj_Nishant92

Description

In the scenarios where you may want to tie comments to a particular task. You can do this by creating a database view to join the tables where they are stored.

Cause

Work Notes and Additional Comments are stored in a table named "sys_journal_field". You can report directly on that table, but for a more meaningful report, you can tie those comments to a particular task (an incident, for example) by creating a database view, joining the "task" table to "sys_journal_field" on element_ID = sys_id.

Resolution

Steps to create a database view joining the 'task' table to the 'sys_journal_field' table below:

Navigate to System Definition > Database Views. Click "New"
Give it a name, and then a description and label if you'd like. Click "Submit".
Open the newly created view, and click "New" next to "View Table" and add table "sys_journal_field" with a "Variable prefix" of "jou". Click "Submit".
Now open the "sys_journal_field" View Table, and add View Fields "Element", "Element_id", and "Value".
Add a second View table "task", with Variable prefix "tsk", and "Where clause" tsk_sys_id = jou_element_id.
Personalize the form and add the "Left join" field. Check it to make it "true". Click "Submit".
Open the "task" View Table, and add View Fields "number", "sys_id", and "sys_created_on" (and anything else that you would like to see in a report). Click "Submit"
Create a report against the new Database View.

If my inputs have helped with your question, please mark my answer as accepted solution, and give a thumb up.
Bharath Chintala

View solution in original post

BharathChintala · ‎03-14-2023

@Raj_Nishant92

Description

In the scenarios where you may want to tie comments to a particular task. You can do this by creating a database view to join the tables where they are stored.

Cause

Work Notes and Additional Comments are stored in a table named "sys_journal_field". You can report directly on that table, but for a more meaningful report, you can tie those comments to a particular task (an incident, for example) by creating a database view, joining the "task" table to "sys_journal_field" on element_ID = sys_id.

Resolution

Steps to create a database view joining the 'task' table to the 'sys_journal_field' table below:

Navigate to System Definition > Database Views. Click "New"
Give it a name, and then a description and label if you'd like. Click "Submit".
Open the newly created view, and click "New" next to "View Table" and add table "sys_journal_field" with a "Variable prefix" of "jou". Click "Submit".
Now open the "sys_journal_field" View Table, and add View Fields "Element", "Element_id", and "Value".
Add a second View table "task", with Variable prefix "tsk", and "Where clause" tsk_sys_id = jou_element_id.
Personalize the form and add the "Left join" field. Check it to make it "true". Click "Submit".
Open the "task" View Table, and add View Fields "number", "sys_id", and "sys_created_on" (and anything else that you would like to see in a report). Click "Submit"
Create a report against the new Database View.

If my inputs have helped with your question, please mark my answer as accepted solution, and give a thumb up.
Bharath Chintala

Raj_Nishant92 · ‎03-15-2023

Thanks for the prompt response.

I want to know what level of access I should have as a user to see the System Definition on my ServiceNow instance.

I am unable to locate System Definition on instance.

Will having admin Access work for me ?

BharathChintala · ‎03-05-2024

@Raj_Nishant92 Yes Admin is needed for this.

If my inputs have helped with your question, please mark my answer as accepted solution, and give a thumb up.
Bharath Chintala

Kenny Estrella · ‎12-13-2023

It's incomplete, would you like to share you did you create the report?