Home Analyst Portal

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

Best Answer

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 Super 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.
  • 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 Ninja 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 Ninja 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 Ninja IT Monkey ✭✭✭✭
    edited December 2017
    @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 Ninja 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 Ninja 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.
  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Ninja IT Monkey ✭✭✭✭
    As far I am aware, the Cireson Database still does not groom its own WorkItems even in the newest release.
    Oh okay. Well @Suleyman_Ozden, then it's absolutely a must to clear the cached database regularly to stay compliant with GDPR.
  • Alberto_FumagalliAlberto_Fumagalli Customer IT Monkey ✭

    Just a remark: as far as I know, if you execute the following lines

    TRUNCATE TABLE [ServiceManagement].[dbo].[Relates_RequestOffering_KnowledgeArticle];
    TRUNCATE TABLE [ServiceManagement].[dbo].[Relates_ServiceOffering_KnowledgeArticle];
    

    you will completely lose the association(s) between your kb article(s) and your offering(s).

    After removing all records in the above tables, if you go to an article -> related items, you'll see that your relationships have been lost. I suppose that such relationships are not synced by Cachebuilder: they're instead directly written in the DB by the website engine. So...If you truncate the tables, then CB is not able to automatically rebuild this kind of information.

Sign In or Register to comment.