Populate the Database with Endpoint Privilege Management Event Data

Event data enters the database using four staging tables:

  • Staging for process type events
  • Staging_UserLogon for user logon events
  • Staging_ServiceStart for service start events
  • Staging_ServiceStop for service stop events

The data in the tables is normalized across the database using the CopyFromStaging stored procedure.

This procedure is normally called every 10 seconds by a Service Broker contract. This is setup by the database installer.

CopyFromStaging Locks

The CopyFromStaging stored procedure requires exclusive access to the staging data otherwise data could be lost. To prevent it being run concurrently in separate sessions a configuration table called Config manages which process currently has a lock on the system.

A row in the table has a configid of CopyFromStagingLocked and a BitValue. If the BitValue is set to 1 the CopyFromStaging stored procedure terminates without processing any events. The StringValue column is used to show what has a lock on the system.

Restart the Database

If the database is restarted when CopyFromStaging is running the lock table remains in place.

Use the following procedure before restarting the database (or rebooting the database host).

  1. Prevent CopyFromStaging from being called by renaming it.
  2. If CopyFromStaging is currently executing you should wait for it to complete. If it is still executing then the Config table shows that the BitValue column equals 1 where the ConfigId is CopyFromStaging. If you instructed CopyFromStaging to run for a certain amount of time by setting the @MinDurationToRunForInMinutes parameter you can stop CopyFromStaging after the next batch by executing InterruptCopyFromStaging.

CopyFromStagingprocesses a batch of 10000 events at a time. It checks the new events against existing events for duplicates so if you have a large database, then the processing of each batch can take some time.

  1. Restart the database / machine.
  2. Rename CopyFromStaging back to its original name.

Recover from a Restart Leaving the Lock in Place

If you restarted the database without following the above procedure, then the lock may remain in place and CopyFromStaging terminates without processing any events. If you are sure that no events are being processed, then you can delete the lock by executing the procedure ReleaseStagingLock CopyFromStaging. This results in any data in the current batch being lost. If you want to recover without losing the current data, contact BeyondTrust Technical Support.

Database Error Management

If an error occurs during the execution of CopyFromStaging the batches in StagingTemp and the three other StagingTemp_x tables are copied to StagingTempBadBatches and StagingTemp_xBadBatches and the error message is stored in the StagingErrors table. Processing of new events then continues as normal.

If a batch has an error then the whole batch is copied to StagingTempBadBatches, not just the bad rows. To process the data in StagingTempBadBatches and leave just the bad rows you can call RetryCopyFromStaging. This processes the rows one at a time and leaves only the offending ones.

If CopyFromStaging is running, then RetryCopyFromStaging does not run. If you are using the Service Broker you can temporarily rename CopyFromStaging to stop it being run again. If you are using jobs you can disable the job.

If CopyFromStaging is set to run for a long time you can call InterruptCopyFromStaging to stop CopyFromStaging after the current batch is processed.

Fixed Column

The StagingTempBadBatches table contains a column called Fixed. Occasionally, data issues or bugs may redirect rows to the StagingTempBadBatches table. Once these issues have been identified and remedied, set Fixed to 1 and the rows are automatically reprocessed. By default, 100 rows are copied into the Staging table on each CopyFromStaging cycle. However, this can be changed by updating a row in the config table. An example of the T-SQL query is outlined below.

  
UPDATE Config
SET BigIntValue = 100
WHERE ConfigId = 'BadRowRetrySize'