Software Distribution View Sample Queries
The following sample queries demonstrate how to join the most common software distribution views to other views.
Joining Advertisement to Collection Views
The following query lists all advertisements by ID and 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 Package to Package Status Views
The following query lists all packages by ID and name, the current status of each package, the 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 both the v_PackageStatusDetailSumm and v_DistributionPoint views 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 Advertisement to Resource and Collection Views
The following query lists the ID and name of 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 of the advertisement. The v_Advertisement view is joined to the v_ClientAdvertisementStatus view by using the AdvertisementID column and the v_Collection view by using the CollectionID column. The v_ClientAdvertisementStatus view is joined to the v_R_System and v_RA_System_IPAddresses 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