Forum Discussion

goatboy's avatar
goatboy
Level 6
11 years ago

DA Custodian Manager - how to get a list of AD group members

Hi

EV/DA 9.0.2

I would like to pull a list of AD group members from Custodian Manager so I can get a historical list of who was in a specific group.

Is there a SQL query I can use?

thanks!

Discovery Accelerator
Information Governance
Tip-How to
Windows Server (2003-2008)
  • Kenneth_Adams's avatar
    Kenneth_Adams
    11 years ago

    If you want a listing of all Custodians where were members of some Custodian Group at some point in time, try this:

    SELECT titttgh.TargetGroupID
         , ttg.Name AS 'Target Group Name'
         , titttgh.AddressOwnerID AS 'Disabled User ID'
         , tau.DisplayName AS 'Disabled User Display Name'
         , titttgh.HistEndDate AS 'Date User Disabled'
    FROM tblIntTargetToTargetGroupHistory AS titttgh
    JOIN tblAddressUser AS tau
      ON titttgh.AddressOwnerID = tau.AddressOwnerID
    JOIN tblTargetGroup as ttg
      ON titttgh.TargetGroupID = ttg.TargetGroupID
    ORDER BY tittgh.TargetGroupID

    There can be some minor changes to the above query to provide a listing of who is currently in the Custodian Groups.

     

  • TonySterling's avatar
    TonySterling
    11 years ago

    I modified the query and got it to run, there was a typo in the Order By clause.  Try this:

    SELECT titttgh.TargetGroupID

         , ttg.Name AS 'Target Group Name'

         , titttgh.AddressOwnerID AS 'Disabled User ID'

         , tau.DisplayName AS 'Disabled User Display Name'

         , titttgh.HistEndDate AS 'Date User Disabled'

    FROM tblIntTargetToTargetGroupHistory AS titttgh

    JOIN tblTargetGroup as ttg

      ON  ttg.TargetGroupID = titttgh.TargetGroupID 

     JOIN tblAddressUser AS tau

      ON tau.AddressOwnerID = titttgh.AddressOwnerID 

      ORDER BY titttgh.TargetGroupID

  • TonySterling's avatar
    TonySterling
    Moderator
    11 years ago

    So the data is going to be stored in a few tables.  Do you want this list for only folks that are no longer in the group?

  • goatboy's avatar
    goatboy
    Level 6
    11 years ago

    That works, I can pull current users out of the group from AD to get a combined list.

    That actually is better as that quickly tells me who is no longer in the group.

    thanks!

  • TonySterling's avatar
    TonySterling
    Moderator
    11 years ago

    So i found this: 

    When the Custodian is deactivated, the Custodian's SMTP Address(es) and SMTP Display Name are moved from the tblAddress table to the tblAddressHistory table.

    I had a quick look and would need to do some more digging but running the following query should give you a list of all the users that are no longer part of a group.

    SELECT * FROM tblAddressHistory 

  • goatboy's avatar
    goatboy
    Level 6
    11 years ago

    Thanks, but that doesn't tell me whether they were in a specific group at some point, just whether they are no longer part of any group.

  • Kenneth_Adams's avatar
    Kenneth_Adams
    Level 6
    11 years ago

    If you want a listing of all Custodians where were members of some Custodian Group at some point in time, try this:

    SELECT titttgh.TargetGroupID
         , ttg.Name AS 'Target Group Name'
         , titttgh.AddressOwnerID AS 'Disabled User ID'
         , tau.DisplayName AS 'Disabled User Display Name'
         , titttgh.HistEndDate AS 'Date User Disabled'
    FROM tblIntTargetToTargetGroupHistory AS titttgh
    JOIN tblAddressUser AS tau
      ON titttgh.AddressOwnerID = tau.AddressOwnerID
    JOIN tblTargetGroup as ttg
      ON titttgh.TargetGroupID = ttg.TargetGroupID
    ORDER BY tittgh.TargetGroupID

    There can be some minor changes to the above query to provide a listing of who is currently in the Custodian Groups.

     

  • TonySterling's avatar
    TonySterling
    Moderator
    11 years ago

    Nice one, Ken!  I was just getting ready to dig into this and you saved me a bit of time.  :)

  • goatboy's avatar
    goatboy
    Level 6
    11 years ago

    Is this meant to be run against the Custodian Manager database?

    I get:

    Msg 4104, Level 16, State 1, Line 11

    The multi-part identifier "tittgh.TargetGroupID" could not be bound.

  • TonySterling's avatar
    TonySterling
    Moderator
    11 years ago

    I modified the query and got it to run, there was a typo in the Order By clause.  Try this:

    SELECT titttgh.TargetGroupID

         , ttg.Name AS 'Target Group Name'

         , titttgh.AddressOwnerID AS 'Disabled User ID'

         , tau.DisplayName AS 'Disabled User Display Name'

         , titttgh.HistEndDate AS 'Date User Disabled'

    FROM tblIntTargetToTargetGroupHistory AS titttgh

    JOIN tblTargetGroup as ttg

      ON  ttg.TargetGroupID = titttgh.TargetGroupID 

     JOIN tblAddressUser AS tau

      ON tau.AddressOwnerID = titttgh.AddressOwnerID 

      ORDER BY titttgh.TargetGroupID

  • Kenneth_Adams's avatar
    Kenneth_Adams
    Level 6
    11 years ago

    My apologies for missing a 't' in what should have been

    ORDER BY titttgh.TargetGroupID

    which Tony figured out.