Confirming Correct Mapping DIM incidents to Parent / Top Level Domains in ICA

Products

• Information Centric Analytics
• Data Loss Prevention Core Package

Issue/Introduction

In the ICA environment, the table ‘LDW_DIMIncidentsToIPDestinations’ contains only a single row of data: One DIM Incident and one IP address.  Is there an alternative way to match IP addresses to DIM incidents and to then associate this with Parent/Top Level Domains?

N/A

Cause

N/A

Environment

ICA 6.5.2.1

Resolution

The following SQL query will provide the data requested:

--Query to Associate Top Level Domains to Dim Incidents.  

SELECT       E.STATUSNAME, D.TopLevelDomain, COUNT(*) INCIDENT_COUNT

FROM         LDW_DIMIncidents A

JOIN         LDW_DIMIncidentStatuses E

       ON           A.STATUSID = E.STATUSID

LEFT JOIN    LDW_DIMIncidentsToNetworkEndpointDestinations B

       ON           A.DIMIncidentID = B.DIMIncidentID

LEFT JOIN    LDW_NetworkEndpoints C

       ON           B.NetworkEndpointID = C.NetworkEndpointID

LEFT JOIN    LDW_Domains D

       ON           C.DomainID = D.DomainID

GROUP BY     E.STATUSNAME, D.TopLevelDomain

ORDER BY     1, 2