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

[goatboy]

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!

[TonySterling]

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]

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]

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]

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 · Accepted solution]

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]

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

[goatboy]

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 · Accepted solution]

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

[goatboy]

gentlemen, many thanks! Just what I needed.

[Kenneth_Adams]

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

ORDER BY titttgh.TargetGroupID

which Tony figured out.

[goatboy]

Can I also get the historical email addresses for these disabled users?

Many thanks!

[Kenneth_Adams]

If you want the SMTP and Display Name entries for the disabled accounts along with the target group information, try this modified query from above:

SELECT titttgh.TargetGroupID
     , ttg.Name AS 'Target Group Name'
     , titttgh.AddressOwnerID AS 'Disabled User ID'
     , tau.DisplayName AS 'Disabled User Display Name'
     , tah.AddressTypeID AS '1 = SMPT, 5 = Display Name'
     , tah.Address
     , tah.HistEndDate AS 'Date Address Disabled'
     , 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
JOIN tblAddressHistory AS tah
  ON titttgh.AddressOwnerID = tah.AddressOwnerID
ORDER BY titttgh.TargetGroupID

And, yes, I corrected my previous typo on the ORDER BY statement.  That does not mean that I've not made another typo in the new lines that I've added.  My apologies in advance for any such typos.

[goatboy]

Wonderful, thank you very much! Works fine. Much appreciated.

[Kenneth_Adams]

You're welcome.