IT Monkey:   Join the Cireson Community today for your chance to win $50!

Deleting the Cireson cashed database automatically

Suleyman_OzdenSuleyman_Ozden Customer Advanced IT Monkey ✭✭✭

As a part of the EU GDPR https://www.eugdpr.org/ I am looking to automatically delete the Cireson database a couple of times a year.


Should these steps be enough?

Stop the cachebuilder service

Run SQL query against ServiceManagement: TRUNCATE TABLE LastModified

Start the cachebuilder service

Answers

  • David_Morris1David_Morris1 Member Advanced IT Monkey ✭✭✭
    This is all you will need to do to completely re-sync the cached data from SCSM to the Cireson portal
  • Justin_WorkmanJustin_Workman Cireson Support Adept IT Monkey ✭✭
    @Suleyman_Ozden - You should be able to TRUNCATE most tables in the ServiceManagement DB with a couple of exceptions.  The most notable exception would be the KnowledgeArticle table(and the related $Comment and $Rating tables).  Most other tables would get rebuilt when cachebuilder runs.
  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
    Remote Desktop to your SCSM Web Portal Server
    Open IIS and Services.msc

    Stop Portal - Stop Application Pool
    Stop Cache Builder then execute query
    /** ULTIMATE CIRESON DB CACHE RESET */
    
    DELETE [ServiceManagement].[dbo].[DisplayString] WHERE ElementID IN (SELECT EnumerationID FROM [ServiceManagement].[dbo].[Enumeration] WHERE CreatedBy = 'c6745a66-5ccc-4fbc-b1d8-ab9797cdea2d');
    DELETE [ServiceManagement].[dbo].[Enumeration] WHERE CreatedBy = 'c6745a66-5ccc-4fbc-b1d8-ab9797cdea2d';
    
    TRUNCATE TABLE [ServiceManagement].[dbo].[Access_CI$User_ConfigurationItem];
    TRUNCATE TABLE [ServiceManagement].[dbo].[Access_CI$User_RequestOffering];
    TRUNCATE TABLE [ServiceManagement].[dbo].[Access_CI$User_ServiceOffering];
    TRUNCATE TABLE [ServiceManagement].[dbo].[Access_CI$User_WorkItem];
    
    TRUNCATE TABLE [ServiceManagement].[dbo].[Affected_CI$WorkItem_ConfigurationItem];
    TRUNCATE TABLE [ServiceManagement].[dbo].[Reviewer_WorkItem_CI$User];
    
    TRUNCATE TABLE [ServiceManagement].[dbo].[CI$DomainGroup];
    TRUNCATE TABLE [ServiceManagement].[dbo].[CI$User];
    TRUNCATE TABLE [ServiceManagement].[dbo].[RequestOffering];
    TRUNCATE TABLE [ServiceManagement].[dbo].[ServiceOffering];
    TRUNCATE TABLE [ServiceManagement].[dbo].[WorkItem];
    TRUNCATE TABLE [ServiceManagement].[dbo].[ConfigurationItem];
    
    TRUNCATE TABLE [ServiceManagement].[dbo].[Related_CI$WorkItem_ConfigurationItem];
    TRUNCATE TABLE [ServiceManagement].[dbo].[Relates_RequestOffering_KnowledgeArticle];
    TRUNCATE TABLE [ServiceManagement].[dbo].[Relates_ServiceOffering_KnowledgeArticle];
    
    TRUNCATE TABLE [ServiceManagement].[dbo].[LastModified];
    

    Optionally you can then rebuild the database tables to free up reserved DB space:
    USE [ServiceManagement];
    EXEC sp_MSforeachtable N'ALTER TABLE ? REBUILD;';

    Then Start Cache Builder

    Then Start Application Pool - Start Portal


    Note that by the time the Cache Builder syncs the Enumerations, you'll need to stop and start the App Pool again to force the Web Portal to grab the Enumerations from the Database, so after doing the above I recommend checking for enumerations in the database being present and then giving the servers a restart/app pool restart, otherwise it'll take all day to sync them over.

    The same applies for end user VS analyst VS admin functionality (what view do you see) if you choose to truncate the [GroupMembership_CI$DomainGroup_CI$User] table.

  • Suleyman_OzdenSuleyman_Ozden Customer Advanced IT Monkey ✭✭✭

    Davis morris. So I dont need to do what Connor is proposing?

    I imagine running it automated trough a powershell script in the weekend (saturday)

    Connor have you done that automated?



  • David_Morris1David_Morris1 Member Advanced IT Monkey ✭✭✭
    Hi Suleyman,

    it depends what your trying to achieve, the method you stated will only rebuild cached data in the database, if you're wanting to strip the database and rebuild it Conner's method will do that for the tables in the script, do you have a list of data that you are actually wanting to rebuild?
  • Suleyman_OzdenSuleyman_Ozden Customer Advanced IT Monkey ✭✭✭
    Well what I am trying to acheive is to be compliant with the EU GDPR. The purpose is to delete all that can be considered personal information in the database. So the cached database would not be enough, Seems like Connors method is the way to go.
  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
    I do not have it automated due to priorities.  Currently it is preferred it is done manually as needed (when an issue arises).  However it is possible that you could turn that into a sql stored procedure/sql function and do a PowerShell script that runs on the Cireson Website server that does it all with timed delays in between much like you imagine.
  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Advanced IT Monkey ✭✭✭
    Well what I am trying to acheive is to be compliant with the EU GDPR. The purpose is to delete all that can be considered personal information in the database. So the cached database would not be enough, Seems like Connors method is the way to go.
    Anything you delete from the Cireson database will just be synced again from the SCSM CMDB, so you will not gain anything, as long as the data still lives in the CMDB.
  • Suleyman_OzdenSuleyman_Ozden Customer Advanced IT Monkey ✭✭✭
    True but default age in SCSM DB is 3 years and that seems to be an acceptable period for the GDPR.
  • David_Morris1David_Morris1 Member Advanced IT Monkey ✭✭✭
    would it just make more sense to just change the data retention in the portal to match?


  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Advanced IT Monkey ✭✭✭
    edited December 15
    @Suleyman_Ozden
    The default retention period in the CMDB is 90 days (for IRs), whereafter the objects is stored in the Data Warehouse for 3 years.

    But with this in mind, I do not understand, why you would want to clean the Cireson ServiceManagement database, as this is a cache of everything from the CMDB. So cleaning that would only remove the objects until the next sync.

    @David_Morris1
    Those settings are for Analytics, and and analytics database only contains "meta" data, such as support group, assigned to, affected user etc., but no details such as description.
  • Suleyman_OzdenSuleyman_Ozden Customer Advanced IT Monkey ✭✭✭
    I am aware of the 90 days and total of 3 years. Whenever I delete the Cireson DB, I should only be able to sync WI that are max of 3 years.
  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Advanced IT Monkey ✭✭✭
    @Suleyman_Ozden
    Ah okay, I think I understand now. You are worried, that the Cireson database will keep objects, which otherwise are groomed from the CMDB, and therefore exposing data which should have been deleted, yes? I do believe, that the Cache Builder is taking the retention period into account already, as objects which are not found in the CMDB (having been groomed into the DW) will not be visible on the portal, as the Cache Builder simply can't find them. Perhaps someone from Cireson can confirm this?

    But you could still run the query by @Conner_Wood, as that would delete everything in the Cireson database, and the next sync will fetch everything from the CMDB again, making sure that no rouge WIs are stuck in the cached database.

    I'm sorry for misunderstanding your question in the first place.
  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
    Perhaps someone from Cireson can confirm this?
    ServiceManagement database does not currently have any grooming processes for Work Items.
    As far I am aware, the Cireson Database still does not groom its own WorkItems even in the newest release.
Sign In or Register to comment.