The Brief
In my previous article Creating a SQL Report for DLP Scan History, I provided a query that showed the scan history in SQL format for reporting purposes.
Following that, I received a request to provide the information to show the total data scanned across an estate. The example I created is grouped by scanner, but as always this can be changed to suit what is needed. The full query is at the bottom as before.
Here is a picture of what the query looks like when it has been ran.
Dealing With Files And Bytes
One thing to note is that I've included 3 columns which deal with files in this query. These are;
SUM(A.NUMBERRETRIEVEDFILES) Files_Scanned
,SUM(A.ITEMSFILTERED) FILES_FILTERED
,SUM(A.ITEMSFILTERED + A.NUMBERRETRIEVEDFILES) TOTAL_FILES
The reasoning for this is fairly straight forward. When scanning, DLP reports the amount of files it scans. What it also reports on is the files it ignores, whichcould be for reasons such as excluded filetypes etc. The aliases as above are fairly self-explanatory. The final line above is simply the addition of the two preceding columns to give a full indication of the total items in the particular target.
The query then gives an indication of the total GB scanned using the formula as below:
SUM (TRUNC(BYTESSCANNED*1e-9 ,2))as GB_SCANNED
Joins and Grouping
The tables called on in this query are WALK and SCANASSIGNMENT. Walk is the primary table (A) and SCANASSIGNMENT (B) is joined so that we can obtain the names of our targets.
FROM WALK A
LEFT OUTER JOIN SCANASSIGNMENT B ON B.COURSEID = A.COURSEID;
The WALK table is where the bulk of the data for scans are stored in regards to reporting data but almost any query formed surrounding discover scans will pull in other tables by necessity.
Full Query and Conclusion
This article was written in order to provide more information to those who seek to report on DLP in more comprehensive ways. Manipulating the data to show what a client wants to see, is arguably superior to the default discover reports.
I hope this article has helped.
Thanks
Chris
SELECT
B.NAME
,SUM(A.NUMBERRETRIEVEDFILES) Files_Scanned
,SUM(A.ITEMSFILTERED) FILES_FILTERED
,SUM(A.ITEMSFILTERED + A.NUMBERRETRIEVEDFILES) TOTAL_FILES
,SUM (TRUNC(BYTESSCANNED*1e-9 ,2))as GB_SCANNED
FROM WALK A
LEFT OUTER JOIN SCANASSIGNMENT B ON B.COURSEID = A.COURSEID
GROUP BY B.NAME;
