Video Screencast Help

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

Created: 16 Apr 2014 • Updated: 22 Apr 2014 | 14 comments
This issue has been solved. See solution.

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!

Operating Systems:

Comments 14 CommentsJump to latest comment

TonySterling's picture

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 picture

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 picture

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 picture

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 picture

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.

 

Ken Adams

Backline Support for CA, DA, ACE, UCE, PSTD, ARMS, EVDC
US Support Region

SOLUTION
TonySterling's picture

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

goatboy's picture

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 picture

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

SOLUTION
Kenneth Adams's picture

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

ORDER BY titttgh.TargetGroupID

which Tony figured out.

Ken Adams

Backline Support for CA, DA, ACE, UCE, PSTD, ARMS, EVDC
US Support Region

goatboy's picture

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

Many thanks!

Kenneth Adams's picture

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.

 

Ken Adams

Backline Support for CA, DA, ACE, UCE, PSTD, ARMS, EVDC
US Support Region

goatboy's picture

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

Kenneth Adams's picture

You're welcome.

Ken Adams

Backline Support for CA, DA, ACE, UCE, PSTD, ARMS, EVDC
US Support Region