2 Replies Latest reply on Jul 3, 2012 2:36 PM by cbartlett

    Directory Service Groups Table


      Hey All,


      I am making some Custom Crystal Reports against our AD, and my goal is to report on certain ProviderSID only for certain users, however these users are not static it is based on their membership to a group. Therefore I would like it to dynamically add a user to the report should they get added to a grouping, lets say for conversation sake its "Domain Admins".


      Now I know I can add a Directory Services Group in LEM and it populates the userlist, this also updates every 5 minutes given that the Directory services tool is active. I was wondering if anyone was aware of what table in LEM stores this information so that I can build my Report around it. I also tried doing a direct connection to my active directory however it is apparent that this wont work with Crystal Reports as the field I require "members" is not a true text field as it has multiple entries and Crystal does not know how to deal with this and thus blanks the field out.


      Any help would be appreciated! Thanks!

        • Re: Directory Service Groups Table
          nicole pauls

          Unfortunately the DS groups aren't stored in the alert database, which is the only place Crystal can access. The only kind of "group" that is actually in the database is the subscription group (which shows which rules a user is subscribed to), the rest all just exist as metadata and are inaccessible to reports. We'll have to look at a way to expose our resources downstream, this is a good example of how it's useful.


          So, you'll need to find a way to deal with this outside of the LEM database. You could use a text file as a resource and have it periodically updated? If you run "net group <groupname> /domain" on the command line it would output a list of all of the users that are members of that group by their account name. The output of that one might need some work to be fed directly in, though. On a server you can also use the dsquery tool, but chances are you aren't.


          I can't say we've tried using Crystal with AD directly, but if you can specify the single item/property you want from the directory, it's most likely sAMAccountName.

            • Re: Directory Service Groups Table

              Thanks Nicole, I was afraid that this was going to be the case. Since these group memberships almost never change, I believe I am going to just setup an Alert to send an email on group membership change, with a Note in the Email template to update the Crystal Report.


              Not as dynamic as I wanted however the work to make it dynamic "Run a DSQuery > Dump to XLS (On Schedule) > Reference XLS as a Datasource" doesnt make sense for me as my time is already a precious commodity . If we start seeing a lot of changes I might have to revisit this in the future.