The Brief
Continuing what feels like a series of articles on the utilisation of SQL within DLP (1,2) this piece highlights another, very useful application of a Structured Query within DLP. The ability to query how many times a specific rule has been violated within an incident and provides a total for that specific rule. It also provides the location of the violation and the incident ID assosciated. The idea of these articles is to document in as much detail as possible, how DLP and the Oracle database work together.
Previously, the required information was only available through the dashboard by going into each incident, as per the below screenshot:
The following query allows this information to be obtained in SQL format and grants the ability to report on efficiently and in greater numbers than incident-by-incident, saving time, resources and money for customers and consultants alike.
As before, the full query is at the bottom of the article. Here I'll break it down step by step so we can understand what we're doing.
What We Want To See
Here is what the query looks like when it is ran:
We see the IncidentID, rule violated, location of the file that produced the violation, and the total amount of times that this rule was violated.
The first part of the query is:
SELECT
B.INCIDENTID
,C.NAME RULE_NAME
,E.DISCOVERURL FILE_LOCATION
,SUM(A.VIOLATIONCOUNT) TOTAL_MATCH_COUNT
FROM CONDITIONVIOLATION A
Simple enough.
Joins: The Tricky Bit
As you may have noticed from above, we are selecting from four different tables. This query isn't stored in one simple place, and has to be put together manually.
LEFT OUTER JOIN INCIDENT B ON A.INCIDENTID = B.INCIDENTID
LEFT OUTER JOIN DETECTIONRULE C ON B.POLICYID = C.POLICYID AND A.CONDITIONID = C.TOPLEVELCONDITIONID
LEFT OUTER JOIN POLICY D ON C.POLICYID = D.POLICYID
LEFT OUTER JOIN DISCOVERITEM E ON E.DISCOVERITEMID = B.DISCOVERITEMID
Important things to note are the four tables that this query draws from. They all contain relevant information but joining solely on policy id will cause the rules to tie to the policies as a whole instead of their own rule.
The key to separating the rules from their policy is CONDITIONID from the DETECTIONRULE table. CONDITIONID is assigned to each rule individually. Meaning if we can call on them and link them to a bigger picture, we can get granular data from the system for reporting purposes.
Last Bits
Final parts of this query revolve around our WHERE clause. The DETECTIONRULE table and POLICY table both contain name columns. Depending on how the policies are configured, we may need both of these to filter on so it is important to distinguish between the two. See the following:
WHERE C.NAME LIKE 'RULE_IN_QUESTION'
AND D.NAME LIKE 'POLICY_IN_QUESTION'
Both tables use NAME columns, but they refer to the rule itself in DETECTIONRULE, and the POLICY table (unsurprisingly) refers to the policy name.
Finally, as we've used an aggregate function, we need to group our query.
GROUP BY B.INCIDENTID, C.NAME, D.NAME, E.DISCOVERURL
;
Once again, I hope this article has helped you.
Thanks
Chris
SELECT
B.INCIDENTID
,C.NAME RULE_NAME
,E.DISCOVERURL FILE_LOCATION
,SUM(A.VIOLATIONCOUNT) TOTAL_MATCH_COUNT
FROM CONDITIONVIOLATION A
LEFT OUTER JOIN INCIDENT B ON A.INCIDENTID = B.INCIDENTID
LEFT OUTER JOIN DETECTIONRULE C ON B.POLICYID = C.POLICYID AND A.CONDITIONID = C.TOPLEVELCONDITIONID
LEFT OUTER JOIN POLICY D ON C.POLICYID = D.POLICYID
LEFT OUTER JOIN DISCOVERITEM E ON E.DISCOVERITEMID = B.DISCOVERITEMID
WHERE C.NAME LIKE 'RULE_IN_QUESTION'
AND D.NAME LIKE 'POLICY_IN_QUESTION'
GROUP BY B.INCIDENTID, C.NAME, D.NAME, E.DISCOVERURL
;
