Audit and Control Management Reporting

  • Article

 

Summary: Sample report queries for Audit and Control Management Server 2013 and Discovery and Risk Assessment Server 2013.

Applies to:

Reporting in ACM

The following are several sample reports designed to run against the ACM database to help you get started with reporting for the ACM Server. The first report query shows users in Active directory that have system level permissions, specifically what role they have a on a given site. The second report query displays the roles and permissions each user has on a folder level. These reports are useful to scan for users or groups no longer needing these permissions or those who have left the department or company.

Also included are sample queries to report information about the documents being monitored by the ACM Server, along with descriptions used in the queries.

Important

It is strongly recommended that any reports are run against an offline copy of the database to make sure that there is no negative effect on production. Running reports is a highly intensive process and could affect system performance. Many companies choose to have a dedicated reporting server that contains copies of the databases so production data is not compromised. Please refer to SQL Server documentation for recommended ways of replicating to an offline copy.

What reporting tools should I use?

It is up to you which reporting tool that you use to query the ACM database. Just you whatever you are comfortable with. While the following reports show specifically how to use these reports using SQL Management Studio, you can also use Microsoft Excel to connect to SQL, Microsoft Excel Power BI tools, or SQL Report Builder and more.

Sample Reports

Important

The following queries are offered as sample code. Microsoft does not provide any warranty for their use.

Important

It will be necessary to convert SID of the user from Active Directory to a friendly name. There are different ways to do this.

  1. You can create a SQL function (scalar) in your reporting database to convert the text SID stored in the database to the binary SID, which can be used with built-in SQL Server functions to find the human readable user/group account. There are several articles that explain how to create such a function:

    • Do a Bing search for fn_StringToSID and fn_SIDToString. You will find several articles explaining how to do this

    • Covert the text SID to binary in T-SQL

    • Find the SQL User name with "Windows Login" SID

  2. Use the new SQL function you created to convert the string SID in the UserID and UserGroupSecurityId fields in the sample queries below to a binary SID, which can be used to find the user/group names in a human readable format, such as “myDomain\JohnDoe”. For example, the UserID field in the sample query below would be converted with syntax similar to SUSER_SNAME(dbo.fn_StringToSID(CA.UserID)).

System level permissions report

  1. Open SQL Management Studio.

  2. Under Databases, right-click ACM and select New Query.

  3. Paste in the following code:

    SELECT 0 as SiteID, 'CentralAdmin' as Site, 'Central Administrator' as Role, CA.UserID as UserGroupSecurityID
FROM AdminUsers CA
UNION
SELECT R.AccountID SiteID, A.Name Site, R.Name Role, s.UserGroupSecurityId
  FROM [SecurityRoles] SR INNER JOIN ROLES R ON R.Id = SR.RoleId
       INNER JOIN Accounts A on A.AccountID = R.AccountID
       INNER JOIN Security S ON s.Id = sr.SecurityId AND a.AccountID = s.AccountID
WHERE s.TemplateId IS NULL

  4. Click Execute.

Folder permissions report

  1. Open SQL Management Studio.

  2. Under Databases, right-click ACM and select New Query.

  3. Paste in the following code:

    SELECT R.AccountID SiteID, A.Name Site, NT.Path Folder, R.Name Role, r.id RoleID, r.System, s.UserGroupSecurityId
  FROM [SecurityRoles] SR INNER JOIN ROLES R ON R.Id = SR.RoleId
       INNER JOIN Accounts A on A.AccountID = R.AccountID
       INNER JOIN Security S ON s.Id = sr.SecurityId AND a.AccountID = s.AccountID
       INNER JOIN FileConfigTemplates FCT ON FCT.Id = S.TemplateId and FCT.AccountID = S.AccountID
       INNER JOIN NTFolders NT on NT.Config=FCT.Id 
ORDER BY A.Name, FCT.Name, R.Name

  4. Click Execute.

Monitored documents Reports

The following queries can help you with reporting on documents that are being monitored by the ACM Server.

Important

The following queries are offered as sample code. Microsoft does not provide any warranty for their use. If you choose to use them, we recommend that you perform your own testing as if you had created the queries yourself.  

The main difference between these reports is the amount of detail. The first query joins to the ReposFiles table and gives you a report showing only the most recent version of each file. The second query gives you a report displaying all versions of the files.

Table Name Description

Accounts

This table contains the list of site names in ACM Server, which are displayed near the top right of the web pages. IF you have more than on site in your ACM Server, this is important, since you may not want to see data from more than one site in the query results.

FileConfig

This table contains the list of monitored files, including their file path and name, as well as the Modified Date of the file. ClientDocID is unique and corresponds to a specific version of each filepath/name.

Files

Contains metadata about each file version. Join to FileConfig to get most of the file-level information in the ACM Server.

ReposFiles

Contains information about the most recent version of each Filepath/name. Join to this table to restrict data in Files and FileConfig to only the most recent version of each filepath/name.

List of monitored files showing only the most recent version of each file

  1. Open SQL Management Studio

  2. Under Databases, right-click ACM and select New Query.

  3. Paste in the following code:

         SELECT A.name SiteName, FileConfig.FilePath AS FullPath, FileConfig.FileName, 
              CAST(Files.RevisionMajor AS VARCHAR(10)) + '.' + CAST(Files.RevisionMinor AS VARCHAR(10)) AS Version,
              FileConfig.ModDate, Files.CreatedUser AS ModifiedBy, Files.CreatedDate AS LastProcessed
        FROM FileConfig INNER JOIN
             Files ON FileConfig.ClientDocId = Files.ClientDocId INNER JOIN
             ReposFiles ON Files.ClientDocId = ReposFiles.ClientDocId
             INNER JOIN Accounts A on A.AccountID = Files.AccountID
        order by FullPath, FileName

  4. Click Execute.

List of monitored files showing all versions of each file

  1. Open SQL Management Studio

  2. Under Databases, right-click ACM and select New Query.

  3. Paste in the following code:

         SELECT A.Name SiteName, FC.FilePath, CAST(F.RevisionMajor AS nvarchar(10)) + '.' + CAST(F.RevisionMinor AS nvarchar(10)) as Rev, 
             F.CreatedDate DateProcessed, FC.ModDate, 
             Max(F.CreatedDate) OVER (PARTITION BY FC.ServerDocID) as LastRevProcDate,
             F.CreatedUser
        FROM dbo.FileConfig FC INNER JOIN FILES F ON FC.ServerDocId=F.ServerDocId and FC.ClientDocId=F.ClientDocID
             INNER JOIN Accounts A on A.AccountID = F.AccountID
        GROUP BY FC.ServerDocID, FC.FilePath, F.RevisionMajor, F.RevisionMinor, 
                 CAST(F.RevisionMajor AS nvarchar(10)) + '.' + CAST(F.RevisionMinor AS nvarchar(10)), 
                 F.CreatedDate, FC.ModDate, F.CreatedUser, A.Name
        ORDER BY FC.FilePath, F.RevisionMajor DESC, F.RevisionMinor DESC

  4. Click Execute.