Video Screencast Help

Query EV/DA/CA Databases for Custom Information

Created: 16 Apr 2013 • Updated: 23 Apr 2013 | 4 comments
John Harbridge's picture
This issue has been solved. See solution.

EV/DA/CA 10.0.3 installed.

My customer's security team is looking for a way to write custom SQL queries to the Enterprise Vault databases. Specifically, they want to produce their own custom reports via their own facilities in order to audit the Users, Roles and activities.

Unfortunately, they are rejecting the use of the built-in auditing and reporting features.

Is there any way that they can obtain the EV/DA/CA SQL structure in order to extract from the databases?

Thanks in Advance,

John

Operating Systems:

Comments 4 CommentsJump to latest comment

Wayne Humphrey's picture

The esiest way would be to SQL Enterprie Manager to generate a datamap, however your problem would be understanding the information that the map is showing you.

TBH I dont think this is or will be public knowledge, ask more direct questions of what you would like to get out.

SOLUTION
Rob.Wilcox's picture

The best is to have a look at some of the views in the SQL Databases.  They combine some of the data together to give you something a little bit more like 'information'... i.e. it's more meaningful.

 

It's worth noting that the Audit database is quite simple, and easy to produce your own reports.

John Harbridge's picture

Thanks for the responses. My customer has there SQL folks reviewing the databases. Can you answer these questions?

Which databases and tables contain the Discovery Accelerator Users and Roles?

Which databases and tables contain the Compliance Accelerator Users and Roles?

JesusWept3's picture

This should do what you want it to
Make sure to set your database to your customer database name 

SELECT TP.PrincipalName "Admin Name",
       TP.PrincipalLogin "Admin Username",
       C.Name "Case Name",
       R.RoleName "Role Name",
       P.Name "Permission Name"
FROM   tblIntRolePermission IRP,
       tblPermission P,
       tblRole R,
       tblIntSecurity I,
       tblPrincipal TP,
       tblCase C
WHERE  C.CaseID = I.CaseID
  AND  I.PrincipalID = TP.PrincipalID
  AND  I.RoleID = R.RoleID
  AND  I.RoleID = IRP.RoleID
  AND  IRP.PermissionID = P.PermissionID
ORDER BY TP.PrincipalName, C.Name, R.RoleName