Query EV/DA/CA Databases for Custom Information

[John_Harbridge]

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

[Wayne_Humphrey · Accepted solution]

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.

[Rob_Wilcox1]

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]

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]

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