Home› Asset Management

Cireson WebPortal Database Table [Access_CI$User_ConfigurationItem] using too much hard drive space

Conner_WoodConner_Wood Customer Ninja IT Monkey ✭✭✭✭
in Asset Management

Hi all,

I was tempted to put this particular question under the Portal section, however this deals with permissions on config items and that means it's using Asset Management Items as well.

Today in SSMS, I saw an enormous amount of memory being used by the Cireson Database: ServiceManagement 



I am aware that it reserved/allocated a huge amount of free space compared to actual used space which can be defragmented down with the following SQL command:

ALTER TABLE [ServiceManagement].[dbo].[Access_CI$User_ConfigurationItem] REBUILD;

However that's not my concern, my concern is the fact it is storing such a large amount of CI and User GUIDs.  Is there a way to limit this, be it by config items or otherwise?  Is there a way to dynamically force the portal to calculate these permissions?

Best Answers

  • Michael_BaldryMichael_Baldry Customer Advanced IT Monkey ✭✭✭
    Answer ✓
    Did you recently enable a SCCM connector? Have you set the Cireson portal up to only care about specific types of CIs?

    We had something similar happen to us, when we were first setting SCSM up: the SCCM connector brings in ALL of the information on its computers, down to a very detailed level (network adapters, physical hard drives, partitions, etc). This resulted in the cache builder seeing those items, and adding a row for each user + each CI they have access to, which ended up being some ridiculously large number. 

    To fix the problem, we followed the steps in the "How to define the ConfigItemClasses setting" KB article (link: https://support.cireson.com/KnowledgeBase/View/1258#/), and specified only the types of items that we care about. After doing a full cache builder resync (including truncating all of the Access_ tables), the time it took to run the cache builder went down significantly, as well as the size of the database.

Answers

  • Michael_BaldryMichael_Baldry Customer Advanced IT Monkey ✭✭✭
    Answer ✓
    Did you recently enable a SCCM connector? Have you set the Cireson portal up to only care about specific types of CIs?

    We had something similar happen to us, when we were first setting SCSM up: the SCCM connector brings in ALL of the information on its computers, down to a very detailed level (network adapters, physical hard drives, partitions, etc). This resulted in the cache builder seeing those items, and adding a row for each user + each CI they have access to, which ended up being some ridiculously large number. 

    To fix the problem, we followed the steps in the "How to define the ConfigItemClasses setting" KB article (link: https://support.cireson.com/KnowledgeBase/View/1258#/), and specified only the types of items that we care about. After doing a full cache builder resync (including truncating all of the Access_ tables), the time it took to run the cache builder went down significantly, as well as the size of the database.
  • Conner_WoodConner_Wood Customer Ninja IT Monkey ✭✭✭✭
    I've made a basic list after following that KA and added it to the Admin Setting Items 'ConfigItemClasses'.
    Cireson.AssetManagement.CostCenter,Cireson.AssetManagement.HardwareAsset,Cireson.AssetManagement.SoftwareAsset,Cireson.Survey.Base,Cireson.Survey.Report,Cireson.Survey.Result,Cireson.Survey.Template,Microsoft.AD.Group,Microsoft.AD.User,Microsoft.AD.UserBase,Microsoft.SystemCenter.BusinessService,Microsoft.SystemCenter.ServiceDesigner.Service,System.Domain.User,System.LogicalEntity,System.Service,System.User
    However after I did a full resync of the ServiceManagement Database doing the below:

    -- 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 rebuild the database tables to free up space:
-- USE [ServiceManagement];
-- EXEC sp_MSforeachtable N'ALTER TABLE ? REBUILD;';


-- Then Start Cache Builder
-- Then Start Application Pool - Start Portal

    I then waited and it still seems to be generating a crazy amount of data.... I will run the above again as I've restarted the servers, both web and database to see if that maybe helps.

  • Conner_WoodConner_Wood Customer Ninja IT Monkey ✭✭✭✭
    Damn, it appears our TEST ENV. still generates the 500+ million rows.

    On our Test Env. we have ~30,000 Config Items.
    On our PROD ENV. we have ~180,000 Config Items.
    On both they have the same connectors for AD Users and AD Groups and both exist on the same domain, but have different SCSM Management Group Names because that's the way it has to be.

    Both databases and web portals are hosted on their separate servers.  The Cache Builder is running just fine for both, the LastModified Table shows they've been updated today.

    But for our PROD Cireson Portal Database, there are 0 rows in [Access_CI$User_ConfigurationItem]!!!!!!
    ..... I'm completely lost on why this is.

    -- Made Query to see how many permissions per config item
SELECT [ConfigItemId], COUNT([UserId]) AS 'UserPermissionCount'
FROM [ServiceManagement].[dbo].[Access_CI$User_ConfigurationItem]
GROUP BY [ConfigItemId] ORDER BY UserPermissionCount

-- Result was ~19,000 User Ids per CI and there were ~27,000 unique CIs.
-- (This does add up to the 500+ million rows, also no CI had less or more permissions)
    User Roles, Library Queues, Library ConfigItem Groups are mostly the same.... however we've been altering them on TEST for various purposes.

    One difference I noticed was the 'Global Operators Group' is configured differently.
    1. On PROD, the only section filled out is the Included Member section with a list of AD Groups.
    2. On TEST, the only section filled out is the Dynamic Members section with the criteria of ConfigItem Object Status does not equal Pending Delete

    I'll try changing it but can someone confirm that the Library Groups are what the Cireson Cache Builder uses when populating the [Access_CI$User_ConfigurationItem] or if there are any other dependencies it uses???  Or if not, what the hell is going on!  This is just so convoluted trying to troubleshoot this issue!
  • Vitalii_NeshcheretVitalii_Neshcheret Customer IT Monkey ✭
    edited December 2017
    I have more than 100 CI Groups with "dynamic member". What should I do in this situation? The number of in the Configuration Items - Users - 123000. And my problem  - the Cache Builder even can't fill out that table [Access_CI$User_ConfigurationItem] because of the error on the 600467500 row:
    The Transaction Log for database 'ServiceManagement' is full due to 'Active_Transaction'.
  • Conner_WoodConner_Wood Customer Ninja IT Monkey ✭✭✭✭
    Wow I completely forgot about this issue!  
    Amazing how I managed to accurately troubleshoot #madskillz.  

    @Vitalii_Neshcheret you need to create your own thread and submit a ticket to Cireson.  
    I also recommend you reference this thread for proof that your issue exists.
    I say this because you will not gain the needed attention of Cireson by posting under this thread.

    It's clear you are running out of space.  
    Either you've hit the max log file size or more likely filled up the hard disk due to Quintillions of generated rows. 
    Cireson will either need to change their logic so that no longer happens, or you will need to research if a workaround exists that still meets your business requirements.

    I also recommend you explain how your Dynamic Member CI Groups are defined and why they are defined that way.  This will help others brainstorming solutions for your particular scenario.
  • Vitalii_NeshcheretVitalii_Neshcheret Customer IT Monkey ✭
    edited December 2017
    Thank you @Conner_Wood I've opened an incident and received rather reasonable answer:
    Hi Vitalii, I see you are copying a lot of scoping information from the ServiceManager database. There are over six hundred million rows of data, which is about 20GB of data. The cache builder will bulk copy these rows of data into the tables. Make sure you have enough space on the drive and make sure that the backup model on the ServiceManagement database is set to simple. Also make sure that the maintenance plan is backing up the ServiceManagement database regularly. I would also review how you are scoping items in the database. If you need to give end users access to whole classes of configuration items, I would do that using the SDK rather than doing it through scoping rules in SCSM. We provide a tool on softwaredownloads.cireson.com called the Asset Management Permissions App. This can be used to give selected roles implicit access to configuration items in the database. See solution 2 on this link for an explanation of how this works: https://technet.microsoft.com/en-us/library/hh519624(v=sc.12).aspx. You can use our tool or the PowerShell script from that link to grant users access to CIs rather than scoping them in the Console.
    The DELETE statement is there to clear out any duplicate rows after the bulk copy operation has completed. As delete commands are logged in the transaction logs, it may be that we are trying to delete too many duplicate rows from the database. I would check the roles you have defined in SCSM to be sure that the users are not being scoped in too many roles. If you have two roles for End Users for instance, make sure they are not scoped on the same Config Items under each role.

Sign In or Register to comment.