Deleting the Cireson cashed database automatically
Suleyman_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
-
Conner_Wood
Customer Ninja 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.
6
Answers
Open IIS and Services.msc
Stop Portal - Stop Application Pool
Stop Cache Builder then execute query
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.
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?
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?
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.
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.
Just a remark: as far as I know, if you execute the following lines
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.