Search Submitter, Accepter, and Search Criteria
In this blog post, we’ll extend our last custom report (see Part 1 of this series) to also include the system ID of the user who created a search, accepted the search results into the case, and what the search criteria were. We'll also build an additional new report that allows you to determine who those ID's belong to. 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.
.
1. Open your custom search report from before (see Part 1 of this series).
.
2. In the "Entities" column (upper left) select the "Tbl Search Vaults Archiveds" entity, then select the "Search" node underneath it.
.
3. In the "Fields" column, grab and drag the "Created By Id", "Modified By ID" and "XMLText" fields and drag them over into your report in order to add them. A good place for this is between the "Search Name" field and the "Description" field.
.
Your report should now look something like this;
.
4. You can rename the "CreatedBy" field to "Submitter" and the "ModifiedBy" field to "Accepter" if you prefer, as these fields represent who submitted the search to the system, and who accepted the results of the search into the case. Often these are the same people, but sometimes not.
.
That's it! You've now added the ID of the person(s) who submitted the search and accepted it's results, along with the search criteria to your Search Exceptions Report. You can either save this change, or save this as a new report - either way is fine.
.
Now, we want to create a small and simple report that shows who each of those ID's actually is.
.
1. Create a new report template in Report Builder, again using your data source (see article Part 1) and again choosing a tab (columnar) report template.
The table of interest for this report is;
a. tblPrincipal
.
2. Select Tbl Principal from the "Entities" pane at top left. Grab and drag the "PrincipalID", "Principal Name", "Principal Login", and "SID" fields from the "Fields" pane at bottom left.
.
3. Give your report a title; we suggest something like "Discovery Accelerator Search User Report". Now your new report should look something like this;
.
4. Now we want to filter out the unnecessary types of users, e.g., groups and system users, which are types that don't submit or accept searches. Click "Filter" on the Report Builder toolbar, and in the Filter Data window, grab and drag "User Type" over to the pane on the right, like this;
.
Select the "user" entry so your filter looks like this;
.
and then Click OK.
.
6. Now you can test your report by clicking "Run Report" in the Report Builder toolbar. If the report has a short list of users (usually 5 to 25) and looks like this, then you're done;
.
As before, to go back to the Design view, just click "Design Report" on the Report Builder toolbar, and then save your report up to the report server. Give it a name like "Search User Report".
.
Now you can run this report alongside your searches report, and you'll be able to see detailed search information, and determine (by cross-referencing the two reports) who the submitter and accepter for each search was.
.
You can also export the contents of each report to a number of formats, right from the Report Manager portal, including Excel and CSV. This allows you to easily create your own Excel or other report blending data from the two Report Builder reports into one seamless report.
.
Finally, with SQL Server 2008, we hear that Microsoft enhanced Report Builder and that we may be able to create just one search report in future using a SQL JOIN query in the data to show the user's name in each field for Submitter and Accepter. If that proves to be true, it'll be the subject of a future report Article in this series.
.
Meantime, Happy Reporting, again from the team at Symantec!
