Discovery Accelerator Reporting

[Liam_Finn1]

We all know how powerful Discovery Accelerator is and most of us know of it's abilities to search and find data for both our companies legal and compliance teams.

For any of the DA users out there that would like to dig deeper into the system to do further investigation on the actual data stored in DA, those of us who are more inquisitive or just so passionate about knowing everything or to put it another way...nosy, we find that the ability to dig into the details on the who, what, where, when and why everything does what it does find the ability to report on the data very frustrating as the software which does come fully loaded with discovery,  production / export and review options is lacking in the reporting on the content.

OK yes i know I'm just directing this at DA and yes Enterprise Vault has the same shortcomings but if i was to write on both products this would never finish.

OK so as I was saying. We had a requirement from our customers to find more details on what is stored in Discovery Accelerator. Now I'm no SQL guru so we put the question to Symantec who said that they know this an "area for improvement within the product" and who were happy to direct us to some third party companies who write custom reports designed for customers specific needs.
So we arranged to speak with them and asked for pricing. Within one week we received the quote.
To put it mildly we were shocked. $3000 for one report, $6000 for another and the mother of them all they were charging $16000.

This was excessive

With some digging and making use of the tools available to all Discovery Accelerator administrators, tools like SQL and SQL reporting services we saved a fortune in SQL reports

Here are two of the reports we created for our customers.

This one searches your DA database to identify all items in Discovery Accelerator Per Case Per Year.

This was created to provide the customer an idea of in which cases the concentration of the discovered data is located

<pre>
SELECT c1.[Name], c2.[<=1995], c3.[1996], c4.[1997], c5.[1998], c6.[1999], c7.[2000], c8.[2001], c9.[2002],
c10.[2003], c11.[2004], c12.[2005], c13.[2006], c14.[2007], c15.[2008], c16.[2009]
FROM tblCase c1
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '<=1995'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
AND idi.MailDate <= '1995-12-31 23:59:59.999'
GROUP BY tc.[Name]) c2 ON c1.name=c2.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '1996'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
AND idi.MailDate between '1996-01-01 00:00:00.001' and '1996-12-31 23:59:59.999'
GROUP BY tc.[Name]) c3 ON c1.name=c3.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '1997'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
AND idi.MailDate between '1997-01-01 00:00:00.001' and '1997-12-31 23:59:59.999'
GROUP BY tc.[Name]) c4 ON c1.name=c4.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '1998'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
AND idi.MailDate between '1998-01-01 00:00:00.001' and '1998-12-31 23:59:59.999'
GROUP BY tc.[Name]) c5 ON c1.name=c5.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '1999'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
AND idi.MailDate between '1999-01-01 00:00:00.001' and '1999-12-31 23:59:59.999'
GROUP BY tc.[Name]) c6 ON c1.name=c6.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2000'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
AND idi.MailDate between '2000-01-01 00:00:00.001' and '2000-12-31 23:59:59.999'
GROUP BY tc.[Name]) c7 ON c1.name=c7.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2001'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
AND idi.MailDate between '2001-01-01 00:00:00.001' and '2001-12-31 23:59:59.999'
GROUP BY tc.[Name]) c8 ON c1.name=c8.name
LEFT JOIN (

SELECT tc.[Name], COUNT(1) AS '2002'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
AND idi.MailDate between '2002-01-01 00:00:00.001' and '2003-12-31 23:59:59.999'
GROUP BY tc.[Name]) c9 ON c1.name=c9.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2003'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
AND idi.MailDate between '2003-01-01 00:00:00.001' and '2003-12-31 23:59:59.999'
GROUP BY tc.[Name]) c10 ON c1.name=c10.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2004'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
AND idi.MailDate between '2004-01-01 00:00:00.001' and '2004-12-31 23:59:59.999'
GROUP BY tc.[Name]) c11 ON c1.name=c11.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2005'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
AND idi.MailDate between '2005-01-01 00:00:00.001' and '2005-12-31 23:59:59.999'
GROUP BY tc.[Name]) c12 ON c1.name=c12.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2006'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
AND idi.MailDate between '2006-01-01 00:00:00.001' and '2006-12-31 23:59:59.999'
GROUP BY tc.[Name]) c13 ON c1.name=c13.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2007'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
AND idi.MailDate between '2007-01-01 00:00:00.001' and '2007-12-31 23:59:59.999'
GROUP BY tc.[Name]) c14 ON c1.name=c14.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2008'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
AND idi.MailDate between '2008-01-01 00:00:00.001' and '2008-12-31 23:59:59.999'
GROUP BY tc.[Name]) c15 ON c1.name=c15.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2009'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
AND idi.MailDate between '2009-01-01 00:00:00.001' and '2009-12-31 23:59:59.999'
GROUP BY tc.[Name]) c16 ON c1.name=c16.name
WHERE c2.NAME IS NOT NULL or c3.NAME IS NOT NULL or c4.NAME IS NOT NULL or
c5.NAME IS NOT NULL or c6.NAME IS NOT NULL or c7.NAME IS NOT NULL or c8.NAME IS NOT NULL or c9.NAME IS NOT NULL or c10.NAME IS NOT NULL or
c11.NAME IS NOT NULL or c12.NAME IS NOT NULL or c13.NAME IS NOT NULL or c14.NAME IS NOT NULL or c15.NAME IS NOT NULL or c16.NAME IS NOT NULL
<pre>

This next Query is very simular but the difference in this one is that it provides the details of the number of items in each case per year that is on legal hold
This can provide you with some informantion when you are planning expiry. If you run expiry in report mode it itentifies all items that are on of age to expire but what report mode does not provide is that if any item is on hold it is still incouded in the report mode for expiry so when you run the expiry your deletes are less thatn expected.

This SQL query will provide you with the number of items on hold so as you can run the expiry in report mode then run this query, then subtract the results of this query from what is in expiry in report mode. The result will give you a better idea of what numbers will actually be deleted

<pre>
SELECT c1.[Name], c2.[<=1995], c3.[1996], c4.[1997], c5.[1998], c6.[1999], c7.[2000], c8.[2001], c9.[2002],
c10.[2003], c11.[2004], c12.[2005], c13.[2006], c14.[2007], c15.[2008], c16.[2009]
FROM tblCase c1
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '<=1995'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
WHERE idi.LegalStatus = 426
AND idi.MailDate <= '1995-12-31 23:59:59.999'
GROUP BY tc.[Name]) c2 ON c1.name=c2.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '1996'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
WHERE idi.LegalStatus = 426
AND idi.MailDate between '1996-01-01 00:00:00.001' and '1996-12-31 23:59:59.999'
GROUP BY tc.[Name]) c3 ON c1.name=c3.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '1997'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
WHERE idi.LegalStatus = 426
AND idi.MailDate between '1997-01-01 00:00:00.001' and '1997-12-31 23:59:59.999'
GROUP BY tc.[Name]) c4 ON c1.name=c4.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '1998'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
WHERE idi.LegalStatus = 426
AND idi.MailDate between '1998-01-01 00:00:00.001' and '1998-12-31 23:59:59.999'
GROUP BY tc.[Name]) c5 ON c1.name=c5.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '1999'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
WHERE idi.LegalStatus = 426
AND idi.MailDate between '1999-01-01 00:00:00.001' and '1999-12-31 23:59:59.999'
GROUP BY tc.[Name]) c6 ON c1.name=c6.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2000'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
WHERE idi.LegalStatus = 426
AND idi.MailDate between '2000-01-01 00:00:00.001' and '2000-12-31 23:59:59.999'
GROUP BY tc.[Name]) c7 ON c1.name=c7.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2001'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
WHERE idi.LegalStatus = 426
AND idi.MailDate between '2001-01-01 00:00:00.001' and '2001-12-31 23:59:59.999'
GROUP BY tc.[Name]) c8 ON c1.name=c8.name
LEFT JOIN (

SELECT tc.[Name], COUNT(1) AS '2002'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
WHERE idi.LegalStatus = 426
AND idi.MailDate between '2002-01-01 00:00:00.001' and '2003-12-31 23:59:59.999'
GROUP BY tc.[Name]) c9 ON c1.name=c9.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2003'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
WHERE idi.LegalStatus = 426
AND idi.MailDate between '2003-01-01 00:00:00.001' and '2003-12-31 23:59:59.999'
GROUP BY tc.[Name]) c10 ON c1.name=c10.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2004'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
WHERE idi.LegalStatus = 426
AND idi.MailDate between '2004-01-01 00:00:00.001' and '2004-12-31 23:59:59.999'
GROUP BY tc.[Name]) c11 ON c1.name=c11.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2005'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
WHERE idi.LegalStatus = 426
AND idi.MailDate between '2005-01-01 00:00:00.001' and '2005-12-31 23:59:59.999'
GROUP BY tc.[Name]) c12 ON c1.name=c12.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2006'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
WHERE idi.LegalStatus = 426
AND idi.MailDate between '2006-01-01 00:00:00.001' and '2006-12-31 23:59:59.999'
GROUP BY tc.[Name]) c13 ON c1.name=c13.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2007'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
WHERE idi.LegalStatus = 426
AND idi.MailDate between '2007-01-01 00:00:00.001' and '2007-12-31 23:59:59.999'
GROUP BY tc.[Name]) c14 ON c1.name=c14.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2008'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
WHERE idi.LegalStatus = 426
AND idi.MailDate between '2008-01-01 00:00:00.001' and '2008-12-31 23:59:59.999'
GROUP BY tc.[Name]) c15 ON c1.name=c15.name
LEFT JOIN (

SELECT tc.[Name], COUNT(*) AS '2009'
FROM tblIntDiscoveredItems idi
INNER JOIN tblCase tc ON tc.CaseID = idi.CaseID
WHERE idi.LegalStatus = 426
AND idi.MailDate between '2009-01-01 00:00:00.001' and '2009-12-31 23:59:59.999'
GROUP BY tc.[Name]) c16 ON c1.name=c16.name
WHERE c2.NAME IS NOT NULL or c3.NAME IS NOT NULL or c4.NAME IS NOT NULL or
c5.NAME IS NOT NULL or c6.NAME IS NOT NULL or c7.NAME IS NOT NULL or c8.NAME IS NOT NULL or c9.NAME IS NOT NULL or c10.NAME IS NOT NULL or
c11.NAME IS NOT NULL or c12.NAME IS NOT NULL or c13.NAME IS NOT NULL or c14.NAME IS NOT NULL or c15.NAME IS NOT NULL or c16.NAME IS NOT NULL
<pre>

For both of the above queries, if you have more than one Discovery Accelerator customer database you can repeat the query and put the USE DATABASE_NAME at the top.

I plan to create more of these in the future and post them to help others out there struggling to find details of whats in their DA and EV environments.

I hope you find this helpful

[NickW]

Hey Liam,

Thanks for this article. We love it! These are great reporting scripts and the only additional thing I'd love for people to note is that before deploying against a production customer database, do test these in your own environment to ensure you don't run into any unexpected localised or environmental issues. It may also be good to find a 'reporting window' that suits your own environment, during which minimal other processing (searches, user access, etc) is occuring.

We also hear your feedback regarding reports and costs, loud and clear. The stuff you've done above is fantastic and we really appreciate you sharing it with the community, and look forward to more in future. Finally, we'll also update everyone as things change and we (hopefully) make fofr a more standardised approach to community and customer development of reporting in future versions of the Accelerator products.

All the best,

------------------------------------------------------
Nick Wade
Group Product Manager, Enterprise Vault
Symantec Corporation
------------------------------------------------------
http://www.enterprisevault.com
http://twitter.com/EnterpriseVault
------------------------------------------------------