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?
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_DIMIncidentsToNetworkEndpo
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