How can I tell Who "acknowledged" an incident thereby setting First Response Date
Keena_Turner
Customer IT Monkey ✭
I need to track who set the First Response Date. Is there a field in the database that captures the logged-in user who clicked "Acknowledge" from the tasks in the portal? I can see it in the History, but I need a field that I can get with a SQL query.
Best Answer
-
Roland_Kind
Partner Advanced IT Monkey ✭✭✭
Hi,
one possible approach (or starting point) could be the following SQL query (for incidents):
you can just execute this for testing inside SQL Server Management Studio - and based on that you can create a query for the portal
Hope this helps
regards
Roland
use servicemanager
SELECT Ilog.EntityChangeLogId, ISNULL(ilog.Pre_FirstResponseDate_3FB895CE_EC1D_7011_C099_25AC8F22469A,'01.01.1999 00:00:00') as preFirstResponseDate, Post_FirstResponseDate_3FB895CE_EC1D_7011_C099_25AC8F22469A as postFirstResponseDate, EntChLog.EntityTransactionLogId, EntTTLog.ContextGenerated
FROM [ServiceManager].[dbo].[MT_System$WorkItem$Incident_Log] ILog
left outer join [ServiceManager].[dbo].[EntityChangeLog] EntChLog with (nolock) on ILog.EntityChangeLogId=EntChLog.EntityChangeLogIdleft outer join [ServiceManager].[dbo].[EntityTransactionLog] EntTTLog with (nolock) on Entchlog.EntityTransactionLogId=EntTTlog.EntityTransactionLogId
left outer join [ServiceManager].[dbo].[MT_System$WorkItem$Incident] WI with (nolock) on ILog.BaseManagedEntityId=WI.BaseManagedEntityId
where WI.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C='IR115' and (ISNULL(ilog.Pre_FirstResponseDate_3FB895CE_EC1D_7011_C099_25AC8F22469A,'01.01.1999 00:00:00') != ilog.Post_FirstResponseDate_3FB895CE_EC1D_7011_C099_25AC8F22469A)
5
Answers
Hi,
one possible approach (or starting point) could be the following SQL query (for incidents):
you can just execute this for testing inside SQL Server Management Studio - and based on that you can create a query for the portal
Hope this helps
regards
Roland
use servicemanager
SELECT Ilog.EntityChangeLogId, ISNULL(ilog.Pre_FirstResponseDate_3FB895CE_EC1D_7011_C099_25AC8F22469A,'01.01.1999 00:00:00') as preFirstResponseDate, Post_FirstResponseDate_3FB895CE_EC1D_7011_C099_25AC8F22469A as postFirstResponseDate, EntChLog.EntityTransactionLogId, EntTTLog.ContextGenerated
FROM [ServiceManager].[dbo].[MT_System$WorkItem$Incident_Log] ILog
left outer join [ServiceManager].[dbo].[EntityChangeLog] EntChLog with (nolock) on ILog.EntityChangeLogId=EntChLog.EntityChangeLogId
left outer join [ServiceManager].[dbo].[EntityTransactionLog] EntTTLog with (nolock) on Entchlog.EntityTransactionLogId=EntTTlog.EntityTransactionLogId
left outer join [ServiceManager].[dbo].[MT_System$WorkItem$Incident] WI with (nolock) on ILog.BaseManagedEntityId=WI.BaseManagedEntityId
where WI.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C='IR115' and (ISNULL(ilog.Pre_FirstResponseDate_3FB895CE_EC1D_7011_C099_25AC8F22469A,'01.01.1999 00:00:00') != ilog.Post_FirstResponseDate_3FB895CE_EC1D_7011_C099_25AC8F22469A)