Report to capture unique "phrase" by unique user

[Shree Nag]

Hello,

Appreciate any help in advance.

I need to create a report to  get a number of incidents by user where  a phrase "abc def" is put in either resolved by notes or in additional comments.

I did create a database view for incident and sys_journal_field to capture

element, element_id and value from sys_journal_field table and

Close_notes, description, resolved _by,sys_id, assigned to, number  from incident table.

When a user is entering the phrase in resolved by, the report captures one entry  of the incident umber in the report.

If the user enters the phrase in additional comments and resolved by, the report is capturing the number twice.

By accident if the user entered the phrase in additional comments twice. It needs to capture only once.

I need the incident number to be displayed only once, even the user enters the phrase in either resolved by notes or additional comments.

Please do help.

Thank you for your time.

[Tanushree Maiti]

Hi @Shree Nag 

Ensure you have joined incident and sys_journal_field on 

incident.sys_id = sys_journal_field.element_id  (where clause)  //Share the screen shot

refer" https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0696891#:~:text=Views%20Engli....

[Shree Nag]

Thank you for responding @Tanushree Maiti 

Attached is the database view that I created.

If a user puts the phrase in Resolution notes and additional comments, teh incident number shows twice. It show up only once.

Please let me know your thoughts on what AM I missing to add.