Querying the NC repository

Like the entripriser manganer for metadata, I wish we have a similar set up for the narrowcast repository. There are times when I struggled to find some information access 100 of narrowcast jobs. 

To save time, I have written few queries which would run against the narrowcast repository and give us the required information. I always wanted to build a UI for thism but vener got chance.

Hope these queries help some one out there..

Get the list of emails of the subscribers, subscribed to a given service, based on the name of the service or the Object ID of the service.

select distinct a11.MR_PHYSICAL_ADD
from (Select MR_OBJECT_ID, MR_OBJECT_NAME from MSTROBJNAMES where MR_OBJECT_TYPE =19) a, 
MSTROBJDEPN b, 
MSTRSUBSCRIPTIONS c, 
MSTRADDRESSES a11
where a.[MR_OBJECT_ID] = b.[MR_INDEP_OBJID] and 
b.[MR_DEPN_OBJID] = c.[MR_SUB_SET_ID] and 
c.MR_USER_ID = a11.MR_USER_ID AND 
c.MR_ADDRESS_ID = a11.MR_ADDRESS_ID
AND a.MR_OBJECT_NAME = ‘ABC’ — (This will be the name of the service)
–AND c.MR_SUB_SET_ID = ‘0000000000000000000000’ — (This will be ID of the subscription set, any one of the two filters could be used)

Track all the services a user is subscribed to, using the email address

select distinct a.[MR_OBJECT_ID] AS MR_OBJECT_ID,
a.[MR_OBJECT_NAME] AS MR_OBJECT_NAME
from (Select MR_OBJECT_ID, MR_OBJECT_NAME from MSTROBJNAMES where MR_OBJECT_TYPE =19) a,
[MSTROBJDEPN] b,
MSTRSUBSCRIPTIONS c,
MSTRADDRESSES ad
where a.[MR_OBJECT_ID] = b.[MR_INDEP_OBJID] and
b.[MR_DEPN_OBJID] = c.[MR_SUB_SET_ID] and
c.MR_USER_ID = AD.MR_USER_ID AND
c.MR_ADDRESS_ID = ad.MR_ADDRESS_ID
and ad.[MR_PHYSICAL_ADD] = ’emailid@domain.com’ — (this will be email address of the subscriber)

There are few other quiries, but thought these would be most used ones. Hope this helps, comments are welcome.