Collection View Sample Queries
The following sample queries demonstrate how to join some of the most commonly used collection views to other views.
Joining Collection Views
The following query lists the SMS clients and all collections in which they are members. The v_FullCollectionMembership view is joined to the v_Collection view by using the CollectionID column.
SELECT FCM.Name, FCM.SiteCode, FCM.CollectionID, COL.Name,
COL.LastRefreshTime
FROM v_FullCollectionMembership FCM INNER JOIN v_Collection COL
ON FCM.CollectionID = COL.CollectionID
ORDER BY FCM.Name, FCM.CollectionID
Joining Collection and Resource Views
The following query lists all of the discovered resources that are not SMS clients. The query lists the domain, computer name, and IP address using data by joining three views. The v_CM_RES_COLL_SMS00001, v_R_System, and v_RA_IPAddresses views are all joined together by using the ResourceID column.
SELECT SYS.Resource_Domain_OR_Workgr0, COLL1.Name,
SYSIP.IP_Addresses0
FROM v_CM_RES_COLL_SMS00001 COLL1
INNER JOIN v_R_System SYS
ON COLL1.ResourceID = SYS.ResourceID
INNER JOIN v_RA_System_IPAddresses SYSIP
ON COLL1.ResourceID = SYSIP.ResourceID
WHERE COLL1.IsClient = 0
ORDER BY SYS.Resource_Domain_OR_Workgr0, COLL1.Name
Joining Collection and Advertisement Views
The following query lists all of the SMS clients that have been targeted for an advertisement. It also lists the source site code, advertisement ID and name, program name, and collection name, and then sorts the data by the resource name. The v_FullCollectionMembership, v_Advertisement, and v_Collection views are all joined by using the CollectionID column.
SELECT FCM.Name AS ResourceName, FCM.ResourceID,
ADV.SourceSite, ADV.AdvertisementID, ADV.AdvertisementName,
ADV.ProgramName, COL.Name AS CollectionName
FROM v_FullCollectionMembership FCM INNER JOIN v_Advertisement ADV
ON FCM.CollectionID = ADV.CollectionID INNER JOIN
v_Collection COL ON ADV.CollectionID = COL.CollectionID
ORDER BY FCM.Name