Advance Request Offering MP - Last Modified Date - Grooming
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.
- Disable the data warehouse job schedules. I don't want the updated last modified dates going to the DW
- Implement the Advance Request Offering MP
- Run the query below to update the Last Modified Date for Closed IR's / SR's
- Enable the data warehouse job schedules
Any thoughts / concerns with this approach?
-- Query to update IR's
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
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.
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.
Thanks Conner, I will definitely take a look at this option.