Active Directory Identity Enrichment

You can provide extended data for identities in Custom Query reports or create identity reports for security principals in Active Directory.

Determining Prerequisites

• File Reporter collects Active Directory identity data once per day by default.

  For instructions on running a collection manually, see Active Directory Identity Scans in the File Reporter 24.1 Administration Guide.

• Decide whether you wish to extend an existing Custom Query file system metadata or permissions report or if you wish to report just on Active Directory identities themselves.

  • If extending an existing Custom Query report determine whether that report data already includes the owner or permissions trustee Security Identifiers (SIDs) or GUIDs.

  • If reporting solely on Active Directory identities, determine which of the extended attributes to include in the report.

    See the table and view definitions for ad.domains, ad.ds_objects, and ad.ds_objects_view for details on available attributes.

Designing the Report

This example extends a "Direct User Assignment" Custom Query report which identifies user accounts that have been assigned permissions directly to folders (as opposed to using group membership) and shows a summary of the count of direct permissions per user by share path.

1. From the Start menu, launch the File Reporter 24.1 Report Designer.

   

2. Enter the login credentials and click Login.

   

   All of your saved Custom Query reports are listed.

3. Click New Custom Query, give it a name, then click Create.

   The Report Designer Query Editor is launched.

   

4. Enter the following SQL statements into the Query Editor:

   Copy

   Basic Query - User Direct Permissions Summary

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   SELECT
       ace.trustee_display_name,
       ace.scan_target,
       COUNT(*) AS ace_count
   FROM srs.current_ntfs_aces AS ace
   WHERE ace.trustee_type = 1
       AND ace.ace_flags & 16 <> 16
   GROUP BY
       ace.trustee_display_name,
       ace.scan_target

5. Click Execute to see a preview of the report data.

   This query will produce a basic result similar to the following:

   

6. Click Save to save the SQL entered so far.

7. Augment the data by joining with the ad.ds_objects table to include the Active Directory user display_name and title fields.

   Copy

   Enhanced Query - User Direct Permissions Summary

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   SELECT
       dso.display_name,
       dso.title,
       ace.trustee_display_name,
       ace.scan_target,
       COUNT(*) AS ace_count
   FROM srs.current_ntfs_aces AS ace
   JOIN ad.ds_objects AS dso
     ON dso.object_sid = ace.sid
   WHERE ace.trustee_type = 1
     AND ace.ace_flags & 16 <> 16
   GROUP BY
       ace.trustee_display_name,
       ace.scan_target,
       dso.display_name,
       dso.title

8. Click Execute and see the updated results that include the title and display_name fields.