IT Monkey:   Click Here to Help Me Build the Agenda for Upcoming Cireson Webinars!

Retention Dates in Portal

Jacob_EshoJacob_Esho Member IT Monkey ✭

In admin portal, it would be nice to see/set all the retention dates along with integrations of your auto close in one area. 
# Get retentionperiod for DataMart, OMDataMart, CMDWDataMart Get-SCDWRetentionPeriod –ComputerName SCSMManagementServer –DatamartComputerName SCSMSQL\DW -DatamartDatabaseName DWDataMart

Get-SCDWRetentionPeriod –ComputerName SCSMManagementServer –DatamartComputerName SCSMSQL\DW -DatamartDatabaseName OMDWDataMart

Get-SCDWRetentionPeriod –ComputerName SCSMManagementServer –DatamartComputerName SCSMSQL\DW -DatamartDatabaseName CMDWDataMart

# So to change the retention of the DWDatamart to 5 years  ( 5 years * 365 days * 24 hours * 60 minutes = 2628000 minutes)

Set-SCDWRetentionPeriod –ComputerName SCSMManagementServer –DatamartComputerName SCSM-SQL\DW -DatamartDatabaseName DWDataMart -DurationInMinutes 2628000

Comments

  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
    It's important to note that the "DWRepository" does affect the retention of the DWDataMart Relationships being marked as deleted.

    If DWRepository is not same or greater than the SCSM Ticket Retention Settings you can have duplicate relationship/enumeration duration data to occur.  The reason this happens is because it does the following:
    1. For a Ticket, Retention Time is up in DWRepository, remove ticket and relationships
    2. DWDataMart takes a look, oh, can't find some relationships, mark them as deleted (hence why the DeletedDate from UTC to LocalTime is about Midnight.
    3. DWRepository syncs ticket and relationships on a new monthly table
    4. DWDataMart sees new data and adds it accordingly.

    I personally believe it's a lack of functionality and code logic that causes relationships in the DWDataMart to be marked as deleted in this way, and makes it difficult to maintain accurate reporting, since archived data is being altered by something else later on besides what was done on the actual ticket. One must question if that is allowed under ITIL standards.  Y'know, a year later and archived tickets have relationships marked as deleted.

    The current workaround I've found is to alter both RetentionTimes (DWRepository and DWDataMart) to the same amount (and ensuring it's equal to or greater than any Ticket Retention Time in SCSM Settings), obviously there is more data in the DWRepository, but accuracy over efficiency in this case.

    Ex.
    See how many incidents that are resolved/closed have their very last relationship of Resolved By User marked as deleted.  (I noticed each day the number got bigger and bigger, but by doing workaround it's remained at 0)
    USE DWDataMart;
    
    SELECT COUNT(*)
    FROM [dbo].[IncidentResolvedByUserFactvw] R
    
    INNER JOIN
    (
    	SELECT IR.Id
    		  ,IR_ResolveUser.IncidentDimKey
    		  ,IR_ResolveUser.[CreatedDate]
    		  ,IR_ResolveUser.[DeletedDate]
    		  ,ResolvedUser.DisplayName
    	  FROM [dbo].[IncidentResolvedByUserFactvw] IR_ResolveUser (nolock)
    
    	  --Filter down to incidents that are closed/resolved
    	  JOIN [dbo].[IncidentDimvw] IR (nolock)
    	  ON IR.IncidentDimKey = IR_ResolveUser.IncidentDimKey 
    	  AND IR.Status_IncidentStatusId IN (3, 4)
    
    
    	  --Get latest date
    	  inner join (
    		select IncidentDimKey, max(CreatedDate) as MaxDate
    		from [dbo].[IncidentResolvedByUserFactvw] (nolock)
    		group by IncidentDimKey
    	  ) tm on IR_ResolveUser.IncidentDimKey = tm.IncidentDimKey and IR_ResolveUser.[CreatedDate] = tm.MaxDate
    
    	  inner join [dbo].[UserDimvw] ResolvedUser (nolock)
    	  ON ResolvedUser.UserDimKey = IR_ResolveUser.TroubleTicketResolvedByUser_UserDimKey
    
    	--Note, CreatedDate AND ~~~DimKey are the composite Primary Key, both must match
    
    	WHERE IR_ResolveUser.DeletedDate IS NOT NULL
    ) AS ResolvedFilter ON ResolvedFilter.IncidentDimKey = R.IncidentDimKey AND ResolvedFilter.CreatedDate = R.CreatedDate
Sign In or Register to comment.