Home Analyst Portal

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

Suleyman_OzdenSuleyman_Ozden Customer Advanced IT Monkey ✭✭✭

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?

Best Answers

Answers

  • Peter_SettlePeter_Settle Customer Advanced IT Monkey ✭✭✭
    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_OzdenSuleyman_Ozden Customer Advanced IT Monkey ✭✭✭

    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_SettlePeter_Settle Customer Advanced IT Monkey ✭✭✭
    Datamart will probably be set to retain data for 90 days.
  • Brett_MoffettBrett_Moffett Cireson PACE Super IT Monkey ✭✭✭✭✭
    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_OzdenSuleyman_Ozden Customer Advanced IT Monkey ✭✭✭

    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?

  • Suleyman_OzdenSuleyman_Ozden Customer Advanced IT Monkey ✭✭✭
    I deleted them from Dwdatamart and servicemanagement, directly in sql
  • Suleyman_OzdenSuleyman_Ozden Customer Advanced IT Monkey ✭✭✭
    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_OzdenSuleyman_Ozden Customer Advanced IT Monkey ✭✭✭
    edited June 2017

    Fixed it by running this instead

    Stop the cachebuilder service

    Run this SQL query against ServiceManagement: TRUNCATE TABLE LastModifiedStart the cachebuilder service
Sign In or Register to comment.