Historical Incident data for specific Assignment group

[kumar22]

Hi Team, 

How can I fetch incidents that were handled by Network Assignment users, meaning that at some point in the incident’s lifecycle it was assigned to a Network user? I tried creating a database view for this, but I’m not able to retrieve the data. Has anyone achieved this before and can guide me?

Order	Table	Variable prefix	Where clause
100	metric_instance	mi
200	metric_definition	md	md.name = mi.definition && mi.definition = 'Assigned to duration' && md.table = 'incident'
300	incident	inc	inc.sys_id = mi.id
400	sys_user	u	u.sys_id = mi.value
500	sys_user_grmember	grm	grm.user = u.sys_id && grm.group = 'd625dccec0a8016700a222a0f7900d06'

Thanks in advance!

[kumar22]

Hi @Dr Atul G- LNG , I am not sure, i dont get any error as well. 
Also I am trying with History and History line tables but same issue again, not data is fetching in the DB views.

Order	Table	Variable prefix	View	Where clause
100	sys_history_line	hl	u_servicedesk_handled_incidents_14aug	hl.field = 'assigned_to'
200	sys_history_set	hs	u_servicedesk_handled_incidents_14aug	hs.sys_id = hl.set
300	incident	inc	u_servicedesk_handled_incidents_14aug	inc.sys_id = hs.id
400	sys_user	u	u_servicedesk_handled_incidents_14aug	u.sys_id = hl.new
500	sys_user_grmember	grm	u_servicedesk_handled_incidents_14aug	grm.user = u.sys_id & grm.group = 'd625dccec0a8016700a222a0f7900d06'

[Dr Atul G- LNG]

Hi @kumar22 

Let’s break this into pieces—don’t join all tables in one go. Pick the first two tables and see if you’re getting results. Also, to confirm the results are there, you can check if the data exists in the list view.

[Bhuvan]

@kumar22 

You can track more information of incident 'assignment group' field using sys_history_set and sys_history_line tables. You can create a report using these tables to show incidents where 'assignment group' was 'Network' and 'assigned to' was not empty.

Thanks,

Bhuvan