The following sample queries demonstrate how to join the most common software distribution views to other views.
Joining Software Distribution and Collection Views
The following query lists all advertisements by advertisement ID, advertisement name, and the collection that was targeted for the advertisement. The v_Advertisement view is joined to the v_Collection view by using the AdvertisementID column.
SELECT ADV.AdvertisementID,
ADV.AdvertisementName,
COL.CollectionID, COL.Name as
CollectionName
FROM v_Advertisement ADV INNER JOIN v_Collection
COL
ON ADV.CollectionID = COL.CollectionID
ORDER BY ADV.AdvertisementID
Joining Software Distribution and Package Status Views
The following query lists all packages by package ID and package name, the current status of each package, the Network Abstraction Layer (NAL) path for the distribution point, and the last time the package was refreshed on the distribution point. The v_Package view is joined to the v_PackageStatusDetailSumm status view and v_DistributionPoint software distribution view by using the PackageID columns.
SELECT PCK.PackageID, PCK.Name as PackageName,
PSD.Targeted,
PSD.Installed, PSD.Retrying, PSD.Failed,
DP.ServerNALPath,
DP.LastRefreshTime
FROM v_Package PCK INNER JOIN
v_PackageStatusDetailSumm PSD
ON PCK.PackageID = PSD.PackageID INNER JOIN
v_DistributionPoint DP
ON PCK.PackageID = DP.PackageID
ORDER BY PCK.PackageID
Joining Software Distribution, Discovery, and Collection Views
The following query lists the advertisement ID and advertisement name for all advertisements at the site, clients that have been targeted with the advertisement, IP address of the client, name of the collection that was targeted, and the last state for the advertisement. The v_Advertisement view is joined to the v_ClientAdvertisementStatus status view by using the AdvertisementID column and the v_Collection collection view by using the CollectionID column. The v_ClientAdvertisementStatus status view is joined to the v_R_System and v_RA_System_IPAddresses discovery views by using the ResourceID columns.
SELECT ADV.AdvertisementID,
ADV.AdvertisementName, SYS.Netbios_Name0,
SYSIP.IP_Addresses0, COL.Name AS
TargetedCollection, CAS.LastStateName
FROM v_Advertisement ADV INNER JOIN
v_ClientAdvertisementStatus CAS
ON ADV.AdvertisementID = CAS.AdvertisementID
INNER JOIN
v_R_System SYS ON CAS.ResourceID = SYS.ResourceID
INNER JOIN
v_Collection COL ON ADV.CollectionID =
COL.CollectionID INNER JOIN
v_RA_System_IPAddresses SYSIP ON SYS.ResourceID =
SYSIP.ResourceID
ORDER BY ADV.AdvertisementID,
SYS.Netbios_Name0