Manage the Endpoint Privilege Management Database Cache

When Endpoint Privilege Management Reporting is deployed for larger corporate networks the amount of data can affect the performance of the reports. Reports are less interactive and load times can increase.

Upgrades to the processor, disk, or memory only make a negligible difference to the load times of reports once the database gets to a certain size. BeyondTrust has developed a solution to improve the user experience. This new feature introduces tables that store summary data to help return the information quickly.

Data Summarization

The data summarization is linked to the current mechanism that normalizes data from the staging area. This is called by either the SQL Agent or Service Broker. Service Broker is installed by default but you can use the script Create_ER_Database_Agent.sql to use the SQL Agent.

The data is stored against the following intervals:

  • 12 Months
  • 6 Months
  • 30 Days
  • 7 Days
  • 24 Hours

Each interval has an associated configuration that determines the amount of time until the data needs to be refreshed.

The data is refreshed depending on the interval:

  • Once a day for 12-month, 6-month, and 30 day intervals
  • Once an hour for 7-day interval
  • Refreshed with new data for a 24-hour interval

Summarizing the grouping data adds overhead to the CopyFromStaging process. Summarizing is not done for every iteration on large installations.

The 24-hour interval is an exception; all data is refreshed since only cached data is stored for the Discovery dashboard.

Enable and Disable Report Caching

The cache feature can be enabled or disabled when you install the Endpoint Privilege Management Reporting database. After installation, change cache settings using one of the following ways:

  • In SQL Server Management Studio, connect to the database and run the following query:
    UPDATE Config
SET BitValue = 1
WHERE ConfigId = 'CacheEnabled'
  • Open SQL Server Reporting Studio and navigate to the Admin folder. Open the ErpCacheAdmin report and click Enable or Disable, depending on the current state.

Enable or Disable the Database Cache setting for the Endpoint Privilege Management Reporting database.

The grouping data is cached in a table and refreshed at a configurable interval. This can be every x minutes or at a specific time of the day. The values that determine this behavior are in the dbo.Config table.

The values can be manually edited by running the following script in SQL Server Management Studio.

DECLARE @Interval NVARCHAR(40)= NULL -- Set the interval Last12Months, Last6Months, Last30Days, Last7Days
Set the local time of the server to run fresh cache
-- daily for 12 / 6 Month, 30 Day eg '13:00'
-- Time on the hour for 7 Day eg '13:05'
DECLARE @LocalRefreshTime TIME = NULL
DECLARE @ForceCache BIT = NULL -- Reset the force cache
DECLARE @MyRefreshTime DATETIME
IF @LocalRefreshTime IS NOT NULL AND @Interval IS NOT NULL
BEGIN
DECLARE @UTCOffset INT = DATEDIFF(MINUTE, GETDATE(), GETUTCDATE())
DECLARE @MinutesPastMidnight INT = DATEDIFF(MINUTE, '00:00:00.000', @LocalRefreshTime)
SET @MyRefreshTime = DATEADD(MINUTE, @MinutesPastMidnight + @UTCOffset, CAST(CAST(GETUTCDATE() AS DATE) AS DATETIME))
UPDATE [dbo].[Config]
SET DateTimeValue = ISNULL(@MyRefreshTime, DateTimeValue)
WHERE ConfigId = 'Cache' + REPLACE(REPLACE(@Interval, 'Last', ''), ' ', '') + 'Refresh'
END
IF @ForceCache IS NOT NULL
UPDATE [dbo].[Config]
SET BigValue = @ForceCache,
DateTimeValue = GETUTCDATE()
WHERE ConfigId = 'ForceCache'