Home Service Manager

Work Item Time Pending/Active

James_JohnsonJames_Johnson Customer Advanced IT Monkey ✭✭✭

The data warehouse has a view IncidentStatusDurationFactvw which shows time an incident spent active or pending. I'm trying to rid us of the data warehouse and just build reports off of the ServiceManager db. I can't seem to find a table or view that contains this information though.

Is this something that is just created based of the entity change log or am I missing something?


Thanks,

James

Best Answer

  • James_JohnsonJames_Johnson Customer Advanced IT Monkey ✭✭✭
    Answer ✓

    @Peter_Miklian I ended up making a sql function that can calculate this value for a work item.


    USE [ServiceManager]
    GO
    /****** Object: UserDefinedFunction [dbo].[GetPendingTime]  Script Date: 6/30/2021 2:22:47 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION [dbo].[GetPendingTime](@baseId Nvarchar(50)) RETURNS float
    
    AS
    BEGIN
      DECLARE @time float
    
      set @time = 0
    
      Declare @Data TABLE(pre_stats nvarchar(max), post_status nvarchar(max), etlID integer primary key, timeadded datetime, endPending bit)
    
    insert into @Data
    SELECT
    	string.DisplayName
    	,string2.DisplayName
    	,etl.EntityTransactionLogId
    	,etl.TimeAdded
    	,endPending = case 
    		when
    		(
    			Pre_Status_785407A9_729D_3A74_A383_575DB0CD50ED = 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE' and Post_Status_785407A9_729D_3A74_A383_575DB0CD50ED != 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE'
    		)
    		then
    			1
    		else 0
    		end
    	
    
    from [ServiceManager].[dbo].[MT_System$WorkItem$Incident_Log] il
    join DisplayStringView string on string.MPElementId = il.Pre_Status_785407A9_729D_3A74_A383_575DB0CD50ED and string.LanguageCode = 'ENU'
    join DisplayStringView string2 on string2.MPElementId = il.Post_Status_785407A9_729D_3A74_A383_575DB0CD50ED and string2.LanguageCode = 'ENU'
    join EntityChangeLog ecl on ecl.EntityChangeLogId = il.EntityChangeLogId
    join EntityTransactionLog etl on etl.EntityTransactionLogId = ecl.EntityTransactionLogId
    
    where BaseManagedEntityId = @baseId and (Pre_Status_785407A9_729D_3A74_A383_575DB0CD50ED = 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE' or Post_Status_785407A9_729D_3A74_A383_575DB0CD50ED = 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE')
    order by etl.EntityTransactionLogId asc
    
    set @time = (select sum(isnull(datediff(mi, x.timeadded, d.timeadded), 0)) 
    from @Data d 
    	outer apply(
    		select top 1 timeadded from @Data t2
    		where t2.etlID < d.etlID order by t2.etlID desc
    	) as x
    where d.endPending = 1)
    
      RETURN @time
    END
    


    Then this can be called in any reports just by passing in the BaseManagmentEntityID and it will return in minutes how long a WI has spent pending.

    Example: Calculating how long it took to resolve an incident minus the pending time based on ServiceManager database -

    DATEDIFF(mi, i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688, i.ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E) - ISNULL(servicemanager.dbo.GetPendingTime(i.BaseManagedEntityId), 0)
    

Answers

  • Peter_MiklianPeter_Miklian Customer Advanced IT Monkey ✭✭✭

    I miss this information, too.

    I hope you can find that information somewhere in the DB.

    But parsing the work item history is the only way I was able to get to timestamps of work item (WI) status changes using PowerShell.

    We consider filling custom DB using Orchestrator upon WI status changes and support group/analyst assignments to have those records at hand and to be able to measure duration in each state/assignment.

  • James_JohnsonJames_Johnson Customer Advanced IT Monkey ✭✭✭
    Answer ✓

    @Peter_Miklian I ended up making a sql function that can calculate this value for a work item.


    USE [ServiceManager]
    GO
    /****** Object: UserDefinedFunction [dbo].[GetPendingTime]  Script Date: 6/30/2021 2:22:47 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION [dbo].[GetPendingTime](@baseId Nvarchar(50)) RETURNS float
    
    AS
    BEGIN
      DECLARE @time float
    
      set @time = 0
    
      Declare @Data TABLE(pre_stats nvarchar(max), post_status nvarchar(max), etlID integer primary key, timeadded datetime, endPending bit)
    
    insert into @Data
    SELECT
    	string.DisplayName
    	,string2.DisplayName
    	,etl.EntityTransactionLogId
    	,etl.TimeAdded
    	,endPending = case 
    		when
    		(
    			Pre_Status_785407A9_729D_3A74_A383_575DB0CD50ED = 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE' and Post_Status_785407A9_729D_3A74_A383_575DB0CD50ED != 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE'
    		)
    		then
    			1
    		else 0
    		end
    	
    
    from [ServiceManager].[dbo].[MT_System$WorkItem$Incident_Log] il
    join DisplayStringView string on string.MPElementId = il.Pre_Status_785407A9_729D_3A74_A383_575DB0CD50ED and string.LanguageCode = 'ENU'
    join DisplayStringView string2 on string2.MPElementId = il.Post_Status_785407A9_729D_3A74_A383_575DB0CD50ED and string2.LanguageCode = 'ENU'
    join EntityChangeLog ecl on ecl.EntityChangeLogId = il.EntityChangeLogId
    join EntityTransactionLog etl on etl.EntityTransactionLogId = ecl.EntityTransactionLogId
    
    where BaseManagedEntityId = @baseId and (Pre_Status_785407A9_729D_3A74_A383_575DB0CD50ED = 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE' or Post_Status_785407A9_729D_3A74_A383_575DB0CD50ED = 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE')
    order by etl.EntityTransactionLogId asc
    
    set @time = (select sum(isnull(datediff(mi, x.timeadded, d.timeadded), 0)) 
    from @Data d 
    	outer apply(
    		select top 1 timeadded from @Data t2
    		where t2.etlID < d.etlID order by t2.etlID desc
    	) as x
    where d.endPending = 1)
    
      RETURN @time
    END
    


    Then this can be called in any reports just by passing in the BaseManagmentEntityID and it will return in minutes how long a WI has spent pending.

    Example: Calculating how long it took to resolve an incident minus the pending time based on ServiceManager database -

    DATEDIFF(mi, i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688, i.ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E) - ISNULL(servicemanager.dbo.GetPendingTime(i.BaseManagedEntityId), 0)
    
Sign In or Register to comment.