04-16-2014 09:37 AM
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!
Solved! Go to Solution.
04-16-2014 01:20 PM
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.
04-16-2014 02:32 PM
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
04-16-2014 10:00 AM
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?
04-16-2014 10:33 AM
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!
04-16-2014 11:29 AM
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
04-16-2014 01:05 PM
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.
04-16-2014 01:20 PM
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.
04-16-2014 01:38 PM
Nice one, Ken! I was just getting ready to dig into this and you saved me a bit of time. :)
04-16-2014 01:41 PM
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.
04-16-2014 02:32 PM
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
04-16-2014 02:40 PM
gentlemen, many thanks! Just what I needed.
04-17-2014 05:38 AM
My apologies for missing a 't' in what should have been
ORDER BY titttgh.TargetGroupID
which Tony figured out.
04-24-2014 08:51 AM
Can I also get the historical email addresses for these disabled users?
Many thanks!
04-28-2014 08:16 AM
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.
04-28-2014 08:22 AM
Wonderful, thank you very much! Works fine. Much appreciated.
04-28-2014 09:13 AM
You're welcome.