Deleting the Cireson cashed database automatically

Suleyman_Ozden

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

Conner_Wood

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.

David_Morris1

This is all you will need to do to completely re-sync the cached data from SCSM to the Cireson portal

Justin_Workman

@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_Wood

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_Ozden

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_Morris1

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_Ozden

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_Wood

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-Bo

Suleyman_Ozden said:

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_Ozden

True but default age in SCSM DB is 3 years and that seems to be an acceptable period for the GDPR.

David_Morris1

would it just make more sense to just change the data retention in the portal to match?

Konstantin_Slavin-Bo

@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_Ozden

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-Bo

@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_Wood

Konstantin_Slavin-Bo said:

Perhaps someone from Cireson can confirm this?

Nicholas_Velich said on Incidents found in Portal that only exist in DWH:

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-Bo

Conner_Wood said:

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_Fumagalli

https://community.cireson.com/discussion/comment/12076#Comment_12076

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.