How to permanently delete a WorkItem (IR in this case)?

Suleyman_Ozden

Hi  everyone

Du to some external mistakenly using an Outlook rule to send out autoreplies, we have had an issue with emails looping between him and one of our mailboxes used with Cireson exch. connector.

This meant we had tons of incidents created. I wanted to delete those permanently after having them cancelled, since we export data on IR to Power BI and want to exclude IR caused by a loop like this.

I ran this

Get-SCSMObject -Class (Get-SCSMClass -Name System.WorkItem.Incident$) -Filter "Title -eq 'Titlexxxx" | Remove-SCSMObject -Force

It seems like they are gone in the service manager database in the table [ServiceManager].[dbo].[MT_System$WorkItem$Incident] and I cant find them in a console search.

The incidents are there in [DWDataMart].[dbo].[IncidentDim] and that seems to be the issue. They also exist in Cireson

[ServiceManagement].[dbo].[WorkItem] database/table, which surprised me since they are goen in SCSM database.

Anyone know how to get permanently rid of them without breaking Things?

Brett_Moffett

Again, I'm not sure that the DWDataMart will "Clean Up" records that have been hard removed from the DB.
My guess would be that you would need to delete the record manually like you did from the ServiceManager DB.

Peter_Settle

You could (somehow) group all the records together and delete them from the DW, however as they should be flagged as Deleted any reporting could be setup to ignore items where the Is Deleted flag = 0 .

Peter_Settle

Could this just be a caching issue as the Datamart is possibly working on 1 day old data and Cireson cachebuilder needs to run.

Suleyman_Ozden

Should not be a cache issue. Its been 8 days since i deleted them.

I am unsure about what happens when i delete the IR in Service Manager database. Should it get deleted in DW database after a sync?

If so is this the sync job? Seems to run every 24 hours, last run 03 am this night.

Process.SystemCenterWorkItemsCube

Peter_Settle

Datamart will probably be set to retain data for 90 days.

Peter_Settle

Have you looked at this?

https://community.cireson.com/discussion/2257/incidents-found-in-portal-that-only-exist-in-dwh

Brett_Moffett

The Cache Builder service does not delete items from the Cireson DB (ServiceManagement) unless they go through the full process of Active, Resolved, Closed, Groomed.
Because the record was hard deleted direct from the DB the cache builder never sees it move out so never changes the record.

The only way to remove it from the Cireson DB (ServiceManagement) is to truncate the table and do a re-sync.
My suggestion would be to follow this KB article if you ever hard delete records from the ServiceManager DB.
https://support.cireson.com/KnowledgeBase/View/1142#/

Hope this answers your question.

Suleyman_Ozden

Thanks. It would be nice to clean up ServiceManagement also, but my purpose is a little different.

We are exporting data from dwdatamart to Power BI. So question is how I would properly delete a IR from dwdatamart?

Brett_Moffett

Again, I'm not sure that the DWDataMart will "Clean Up" records that have been hard removed from the DB.
My guess would be that you would need to delete the record manually like you did from the ServiceManager DB.

Peter_Settle

You could (somehow) group all the records together and delete them from the DW, however as they should be flagged as Deleted any reporting could be setup to ignore items where the Is Deleted flag = 0 .

Suleyman_Ozden

I deleted them from Dwdatamart and servicemanagement, directly in sql

Suleyman_Ozden

Brett_Moffett said:

The Cache Builder service does not delete items from the Cireson DB (ServiceManagement) unless they go through the full process of Active, Resolved, Closed, Groomed.
Because the record was hard deleted direct from the DB the cache builder never sees it move out so never changes the record.

The only way to remove it from the Cireson DB (ServiceManagement) is to truncate the table and do a re-sync.
My suggestion would be to follow this KB article if you ever hard delete records from the ServiceManager DB.
https://support.cireson.com/KnowledgeBase/View/1142#/

Hope this answers your question.

I ran this

• TRUNCATE TABLE LastModified;
• TRUNCATE TABLE WorkItem;
  

Then restarted the cachebuilder.

But seems like its taking ages to rebuild the cache again. Read taht it would take 2 mins for a large environment. My cachebuilder log does not have any errors

Suleyman_Ozden

Fixed it by running this instead

Stop the cachebuilder service

Run this SQL query against ServiceManagement: TRUNCATE TABLE LastModifiedStart the cachebuilder service