Audit and Control Managment Reporting

  • 發行項

 

上次修改主題的時間： 2014-06-16

Summary: Two sample reports for Audit and Control Management Server 2013 and Discovery and Risk Assessment Server 2013.

適用於：

Reporting in ACM

The following are two simple starting reports to run against the ACM database to easily monitor who has system and folder level permissions. The first report shows lists users in Active directory that have system level permissions, specifically what role they have a on a given site. The second report 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.

重要

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 effect 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

重要

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

重要

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.