In this blog post, we’ll create a new custom report that shows if any errors or exceptions occurred during Discovery Accelerator Searches, in one of your cases. You can build and use this report at your company. Please note that this article focuses on SQL Server 2005 Reporting Services. SQL Server 2008 will be the focus of a future article in this series.
Why? The legal Discovery process is long, often complicated with many moving parts, and must be assured at every step along the way. Reporting what happened, when, and by whom is often how you “prove out” your processes, especially if a Discovery matter proceeds to court. Symantec Enterprise Vault™ ships with a number of reports out-of-the-box, as does Symantec Discovery Accelerator, but you can also build your own. One of the most common reports you’ll want to use, beyond those that we already ship, is a report that shows what searches where run, when, and if they completed successfully or if any error condition prevented the search from completing normally. This is usually called a “Search Exceptions” report. Any exceptions that occurred, aka searches that did not complete successfully, would then need to be followed up in order that they finish after the source of any errors is repaired, or that another process is used to complete the Discovery task at hand.
How? For any custom report around Enterprise Vault and Discovery Accelerator, you’ll use the SQL Server Reporting Services portal that ships with and can be installed with Enterprise Vault itself. So in order to get started, you’ll need this installed, and you’ll need an administrative login (or at least, a login that has reporting rights against your Discovery Accelerator databases, and report publishing rights in your report portal). For details on how to do this, consult the Symantec Enterprise Vault Installation and Configuration Guide – you’ll need it to be installed and configured to report against the Discovery Accelerator SQL instances your company is using.
Assuming that’s done, let’s get started.
1. Go to your SQL Server Reporting Services site for your Discovery Accelerator installation, and log in as a user with administrative rights (check with your SQL Admins if necessary in order to have appropriate rights granted to your account).
2. Create a new folder called something like “Search Exceptions” (or whatever you’d prefer, really it’s just a container for your custom reports). A good location for this is under the “Discovery Chain of Custody” folder that already exists (providing you have previously installed the Discovery Accelerator reports).
3. In that folder, create a new DataSource. It’s easiest to copy the Connection String from an existing report – just go to the existing reports properties. Alternatively, you can have your SQL Admin supply you with a Connection String to your Discovery Accelerator Customer database instance (and possibly a login). In most systems, providing your user has appropriate access (again, check with your SQL Admin), then Windows Integrated Security is the most appropriate login model. It should look something like this;
4. Once created, click on the new DataSource (the Search Exceptions Data Source in the example picture above), then choose to “Generate Model”. Name it something like SearchExceptionModel (or whatever you choose).
This step may take a few minutes. When it completes, go back to your new folder. You should now see the Data Source, and the Model (with whatever name you gave it).
5. Open the Report Builder. You may be asked a security question about whether or not you want to run this application. Answer “Run”.
6. Select the Model that you just created and then click “OK” to begin creating a new Table report.
7. You’ll then see a blank report template with a list of available tables (from your Discovery Accelerator Customer database) in the top left-hand pane, and a list of fields in the bottom left-hand pane. The tables you’re interested in will be;
a. Tbl Int Search
b. Tbl Case
c. Tbl Search Vaults Archived
d. Tbl Statuses
8. Select Tbl Int Search from the left-hand top pane. Grab (with your mouse) and drag the Name, Description, and Create Date fields across to the report template. Drop them in the box provided, right where it says “Drag and drop column fields”. You can order them how you like, just re-order them by dragging around with your mouse. It’ll now look something like this;
Tip: Drag and drop these fields right onto the report template in the box provided. Do this by using your mouse
That was a pretty easy start, right? In order to make this report useful though, we’ll need to add some more fields to it, that come from different tables (from the list above).
9. Underneath Tbl Int Search in the left-hand top pane, now click Tbl Search Vaults Archiveds. Grab and drag the Start Date, End Date, Total Num Hits and Info fields across to your report template next. It should now look like this;
At this point, we’ve already built a basic report of all searches in the system and could run it, but as the list of cases in your system grows, so would this report. So we’ll add a final couple of fields for the case, and also a selectable filter (on that field) in order to ensure the report only runs against one case at a time.
10. Underneath Tbl Search Vaults Archiveds in the left-hand top pane, now click Search. Under that, now select Case. Grab and drag the Name field across to the left-most position of your new report template. It should now look like this;
11. Underneath Tbl Search Vaults Archiveds in the left-hand top pane, now click Status. Grab and drag the Name field across to the position between Total Num Hits and Info on your new report template. You can also select the Info field data element, and make it bold. It should now look like this;
12. Now’s the time to add a title to your report, and a selectable filter. Type in a suitable name like “Example Inc. – Discovery Accelerator Search Exceptions Report” and resize the title placeholder box with your mouse as needed. Then click on the “Filter” button in the Report Builder toolbar;
You’ll be presented with a new dialog called “Filter Data”.
13. In the “Filter Data” dialog, again select Search, then Case in the left-hand top pane. Grab and drag the Name field over to the grey area in that dialog. It should look like this;
14. Now right-mouse click the word Case, next to the filter condition, and select “Prompt” from the popup menu, and click “OK”. This enables the report with a selectable filter for the case, every time the report is run;
15. That’s it. You’ve built a report. Let’s test it – click “Run Report” in the Report Builder toolbar. You should see the first report screen where you must select the case of interest from a drop-down menu, and a “View Report” button. Pick a case, and click “View Report”. If it looks something like this, your basic report is done, and ready to be saved up to the SSRS site;
NOTE: The key fields of interest are the Status field and the Info field. Here you will see any error states (Status), and accompanying messages (Info), if any search against any Archive included in the case did not complete successfully.
16. Click “Design Report” on the Report Builder toolbar to close your test run of your new report. Select “File”, then “Save As”, give your report a name like “Example Inc Search Exceptions”, and click “Save”.
>> That’s it! You’re done, and you can now close the Report Builder, and simply use your new report directly from the SSRS site.Other things you can do with custom reporting;
There are many things you can do with custom reports, within Microsoft’s Report Builder. You may want to learn more of the possibilities by taking one of many fine tutorials available on the internet (just search Google or Bing for “Report Builder” +tutorial +Microsoft). You’ll also want to be familiar with, and close to your SQL Admins; unless of course you are the SQL Admin for your company. In general, if you stay away from large tables such as Tbl Int Discovered Items, then you’re unlikely to cause any performance issues such as SQL deadlocks on your Discovery Accelerator SQL Servers. However, we urge you to check the details of your new report with your SQL Admins before deploying and using any custom reports.
- Remove the default Information field boxes that Report Builder added by default – these aren’t necessary as your report already has totals, and a selected Case name right within the report. They’re named “Total Tbl Search Vaults Archiveds: 0” and “Filter: Tbl Search Vaults Archiveds with: Case = ….”. Just delete both boxes from the report.
- Add banner graphics, change the layout, colours, fonts and other aspects of the data on the report page, etc. You can really customize this report to your company if you like.
- Build other reports of interest. Just keep in mind to stay away from using Tbl Int Discovered Items and any other large table that your SQL Admins advise should not be used.
- Deploy your report templates for other users, or deploy new report templates in order to embed them directly within Discovery Accelerator 8.0 and above (on the Reports tab). To do this, you’ll need Visual Studio and a good understanding of Microsoft’s Reporting Services though, which is quite outside the scope of this article, so work with your SQL Admins and your internal dev resources if you want to do this, and make sure you know how to rollback any changes if something goes wrong.
Happy reporting from the EV team at Symantec!