Pull Subscriber Activity from Unity

[chad_meyer]

We are running Unity Connection 11.5 and are looking to pull a report for a large amount of users and the built in report output is pretty atrocious when exporting to CSV.  Using CUDLI I think I have the correct tables and mappings, however I need to perform an inner join to the UnityRptDB database on the tbl_messageactivity table.  I cannot seem to get the syntax correct to call that database.  I either get syntax errors or CUDLI thinks the DB name is part of the table name, also resulting in an error.  Can someone please take a look at what I have and provide the correct syntax? I am running the query with the UnityDirDB selected.

select alias from vw_alias
INNER JOIN tbl_mailboxmap
ON tbl_mailboxmap.userobjectid = vw_alias.object_userobjectid
INNER JOIN UnityRptDB.tbl_messageactivity
ON UnityRptDB.tbl_messageactivity.mailboxobjectid = tbl_mailboxmap.mailboxid
where mailboxobjectid = '1b91155c-cb00-42ee-b72e-7d0fa5ceb757'

Any assistance is greatly appreciated.

[chad_meyer]

While I wasn't able to get the direct answer to the question I was able to create the following set of queries which will identify users configured in specific search scopes:

**Get objectID of the search scope 'friendly name'

select objectID from tbl_searchspace
where name in ('HQ_searchscope', 'site1_searchscope')

**Get user names of all people defined in objectIDs from previous query

select alias from vw_subscriber
where searchbynamesearchspaceobjectid in ('<objectID_from_previous_query>')

**Find mailbox ID for users

select tbl_mailboxmap.MailboxId, vw_alias.Alias from vw_alias
INNER JOIN tbl_mailboxmap
ON tbl_mailboxmap.userobjectid = vw_alias.object_userobjectid
where alias in ('<alias_list_from_previous_query>')

Finally, switch to the UnityRptDB and get the messages list..I added cases which correlate the numerical values to the status of the message (New, Read, or Deleted)

select createddatetime, case action when 0 then 'NEW' when 1 then 'READ' when 2 then 'DELETED' end as action, sender
from tbl_messageactivity
where mailboxobjectid in ('<mailboxIDs_from_previous_query>')

Hopefully this helps someone down the line.  If someone has a better way of obtaining this info feel free to share.