DIM incident count disparity

Products

• Information Centric Analytics

Issue/Introduction

Comparing Data In Motion (DIM) incident counts in the Symantec Data Loss Prevention (DLP) Enforce console against the DIM Incident Count measure in the Information Centric Analytics (ICA) Analyzer may reveal anything from minor to significant differences that suggest data loss or a failure by ICA to fully synchronize with DLP.

Environment

Release : 6.x

Component : Symantec DLP Integration Pack

Cause

ICA does not maintain a persistent connection to the DLP database. Instead, it imports incidents from DLP with the nightly RiskFabric Processing job and (optionally) at intervals throughout the day with the RiskFabric Intraday job. Because new incidents are created continuously in DLP, the number of incidents in DLP will always be greater than the number of incidents imported into ICA.

In addition to this, the queries used in ICA's staging and loading procedures for importing and processing Symantec DLP DIM incidents include filters that will exclude some incidents. These filters will exclude any incident that has been marked for deletion; any incident that has been flagged to be hidden in reports; and will include only incidents generated off Endpoint, Network, and REST message types.

During staging, these are the predicates used in the OPENQUERY call against the DLP database hosted in Oracle:

• ISDELETED = 0
• SHOULDHIDEFROMREPORTS = 0
• INCIDENT.MESSAGESOURCE IN ('ENDPOINT','NETWORK','REST')

During processing, when data from ICA's staging tables are moved into ICA's logical data warehouse or core tables (e.g., dbo.LDW_DIMIncidents), ICA filters-out incidents created against disabled or deleted DLP policies.

Resolution

A difference in incident counts between DLP and ICA is expected; however, failures during staging, core processing, or cube processing may exacerbate the difference. To determine whether there were failures in the nightly or intraday processing jobs, execute the following query in SQL Server Management Studio (SSMS):

SELECT      j.[name] AS "Job",
            s.step_id AS "StepID",
            s.step_name AS "StepName",
            h.[message] AS "Message",
            msdb.dbo.agent_datetime(run_date, run_time) AS "RunDateTime",
            ((h.run_duration/10000*3600 + (h.run_duration/100)%100*60 + h.run_duration%100 + 31 ) / 60) AS "Minutes"
FROM        msdb.dbo.sysjobs AS j
INNER JOIN  msdb.dbo.sysjobsteps AS s 
        ON  j.job_id = s.job_id
INNER JOIN    msdb.dbo.sysjobhistory AS h WITH (NOLOCK)
        ON  s.job_id = h.job_id AND
            s.step_id = h.step_id AND
            h.step_id <> 0
WHERE       (
                j.[name] = 'RiskFabric Processing' OR
                j.[name] = 'RiskFabric Intraday Processing'
            ) AND
            msdb.dbo.agent_datetime(run_date, run_time) >= CAST(GETDATE() AS date) AND
            h.sql_severity <> 0
ORDER BY    h.instance_id DESC;

If there have been any job step failures, the following query will return additional details to assist with determining the cause:

SELECT   LogID,
         LogGroupID,
         LoginName,
         LogName,
         CAST(LogDescription AS nvarchar(255)) AS "LogDescription",
         StartDate,
         EndDate,
         RunMinutes,
         StatusFlag,
         SQLAgentJobName,
         StoredProcedureName,
         SourceTableName,
         DestinationTableName,
         DMLAction,
         RowsAffected,
         ErrorCode,
         ErrorDesc,
         ErrorSource,
         ErrorDate
FROM     RiskFabric.dbo.Log_DataTransformation WITH (NOLOCK)
WHERE    CAST(StartDate AS date) >= CAST(GETDATE() AS date)
AND      StatusFlag = 'F'
ORDER BY 1 DESC;

If there have been no failures during the job steps Run Staging Procedures (4), Update LDW_Core (11), or Risk Fabric cube processing (16), the following query can be used to get a count of incidents that have been created in DLP but not yet imported into ICA:

DECLARE @DLP NVARCHAR(MAX);
DECLARE @LinkedServerName varchar(MAX);
DECLARE @Watermark datetime;

SET     @DLP = '';
SET     @LinkedServerName = (SELECT LinkedServerName FROM RiskFabric.dbo.LinkedServers WHERE LinkedServerType = 'DLP');
SET     @Watermark = (SELECT MAX(RFCreatedDate) FROM RiskFabric.dbo.LDW_DIMIncidents WITH (NOLOCK));

SELECT  @DLP = @DLP + 'SELECT COUNT(*) AS "New_Incidents" FROM OPENQUERY(' + QUOTENAME(@LinkedServerName) + ',''SELECT INCIDENTID, CREATIONDATE FROM PROTECT.INCIDENT WHERE ISDELETED = 0 AND SHOULDHIDEFROMREPORTS = 0'') WHERE CREATIONDATE >' + @Watermark
EXEC    sys.sp_executesql @DLP;