How to fetch list of incidents which were assigned to my group at least once

[ramandeep]

Dear Friends,

I want to fetch list of incidents which were assigned to my group at least once. For example INC00123456 is an incident and earlier it was assigned to my group "ABC" but currently it is assigned to "XYZ" group. Now how can I find if this incident was assigned to my group earlier or not? Please suggest solution?

Cheers

Ramandeep

[sunilsafare]

Hi Ramandeep,

My idea is to create a boolean column on the incident table and check it with the help of business rule if the incident is assigned to you. Now create a module by keeping filter that boolean column is true. Then it displays the list of all incidents which are assigned to you at least once.

Please let me know if any questions

Thanks,

Sunil Safare

[ghsrikanth]

If the field in the table is Auditted then the changes in the field value will be stored in the sys_audit table,

There will columns like

TableName which is incident in your case

Field that has changed

Sysid of the incident

Old Value

New Value

Here in the screenshot I gave example where i changed assigned to field for a particular incident multiple times and it got audited

In your scenario, write an ACL or before BR in such a way, query the sys_audit table for the incident that you are trying to open and check whether for that incident is there any record in the sys_audit table where assignment group had your group sys id in old value or new value.

If it had, then return true or false

I really doubt about the performance as sys_audit is quite a big table and querying through that can be little tricky, but I trust on Servicenow platform ability.

So try this approach.

Mark if it is helpful or correct, feedback is appreciated

[ramandeep]

Dear Srikant and Sunil,

Thank you for your suggestions! Isn't there any other simple way? can't we use any other table to find such incidents? this functionality is required for the end users for reporting purposes.

@Srikant:Audit table is huge table and end users should not be able to access that able to avoid any performance issue.

@Sunil: adding a Boolean field will not work because if ticket is assigned to different teams then different teams can update the ticket and I will not know if this field was updated by my group or any other group.

Cheers

Ramandeep

[Pradeep Sharma]

Hey Ramandeep,

Metric defintion is what you are looking for.

Metric Definition Support - ServiceNow Wiki