Advance Request Offering MP - Last Modified Date - Grooming

Christopher_Montgome

I am planning to implement v7.x including the new Advance Request Offering management pack.  I am concerned with the impact this will have on grooming.  We groom IR’s / SR’s every 90 days and currently have ~25K closed IR’s and ~45K closed SR’s.  with the last modified date being updated for all IR’s and SR’s, in 90 days I would be grooming ~70K workitems.  I am guess that could have an impact on the performance of my SCSM environment.

Here is what I propose to do.  This will be tested in our Dev and Test environments before doing this in Production.

1. Disable the data warehouse job schedules.  I don't want the updated last modified dates going to the DW
2. Implement the Advance Request Offering MP
3. Run the query below to update the Last Modified Date for Closed IR's / SR's
4. Enable the data warehouse job schedules

Any thoughts / concerns with this approach?

Thanks, Chris

-- Query to update IR's

update dbo.[BaseManagedEntity]

Set LastModified = IR.ClosedDate_C529833E_0926_F082_C185_294CBC8BB9FD

 FROM dbo.[BaseManagedEntity] BME

JOIN dbo.MT_System$WorkItem$Incident IR ON IR.BaseManagedEntityId = BME.BaseManagedEntityId

 WHERE BME.[IsDeleted] = 0

                              AND BME.BaseManagedTypeId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68'

                              AND IR.Status_785407A9_729D_3A74_A383_575DB0CD50ED = 'BD0AE7C4-3315-2EB3-7933-82DFC482DBAF'

                              AND IR.ClosedDate_C529833E_0926_F082_C185_294CBC8BB9FD  IS NOT NULL

 

Geoff_Ross

Hi Christoper,

Looks good in theory and it will probably work without issue. However, Microsoft do not support editing the database directly, so you way want to consider a different approach, editing the work items via the SDK in some way.

You could use C#, PowerShell or even the Cireson Portal APIs.

My preference would be PowerShell and can provide some pointers if you need.

Geoff

Christopher_Montgome

Thanks Geoff, I appreciate the feedback. 

Conner_Wood

Christopher_Montgome said:

I am planning to implement v7.x including the new Advance Request Offering management pack.  I am concerned with the impact this will have on grooming.  We groom IR’s / SR’s every 90 days and currently have ~25K closed IR’s and ~45K closed SR’s.  with the last modified date being updated for all IR’s and SR’s, in 90 days I would be grooming ~70K workitems.  I am guess that could have an impact on the performance of my SCSM environment.

Here is what I propose to do.  This will be tested in our Dev and Test environments before doing this in Production.

1. Disable the data warehouse job schedules.  I don't want the updated last modified dates going to the DW
2. Implement the Advance Request Offering MP
3. Run the query below to update the Last Modified Date for Closed IR's / SR's
4. Enable the data warehouse job schedules

Any thoughts / concerns with this approach?

Thanks, Chris

Yikes, I know you want to set the last modified date to the closed date, but I think those changes will still sync over.... if you actually want to prevent additional syncing altogether, I recommend using SCSM - Last Modified Date Manager for IR and SR... I made it specifically for this purpose.

I found it extremely useful since other areas of IT rely of Last Modified Date for some reports because if I alter all the Last Modified Dates to something else then obviously the report isn't accurate to what it really should be, hence why I made the program.  A long time ago I told Cireson about it and I think they said they'd put a link to it in one of their knowledge articles (I think through email), but haven't checked if they actually did.

Christopher_Montgome

Thanks Conner, I will definitely take a look at this option.

Geoff_Ross

Connor, that is awesome. Go ahead and create a thread in the community uploads section for that as it will be really useful to many.