The following sample queries demonstrate how to join Network Access Protection (NAP) views to other views. The NAP views are joined to desired configuration management views by using the CI_ID column and to discovery views by using the ResourceID column.
Joining NAP and Desired Configuration Management Views
The following query lists the bulletin ID; article ID; title; how many clients have been restricted over the last day, last 7 days, and last 30 days; and when the restriction summary was last updated. The query joins the v_NAPRestrictionSummary NAP view with the v_ConfigurationItems and v_LocalizedCIProperties desired configuration management views by using the CI_ID column.
SELECT v_NAPRestrictionSummary.BulletinID,
v_NAPRestrictionSummary.ArticleID,
v_LocalizedCIProperties.DisplayName
AS Title, v_NAPRestrictionSummary.LastDayCount,
v_NAPRestrictionSummary.Last7DaysCount,
v_NAPRestrictionSummary.Last30DaysCount,
v_NAPRestrictionSummary.LastSummaryTime
FROM v_NAPRestrictionSummary INNER JOIN
v_ConfigurationItems ON
v_NAPRestrictionSummary.CI_ID =
v_ConfigurationItems.CI_ID INNER JOIN
v_LocalizedCIProperties ON
v_NAPRestrictionSummary.CI_ID =
v_LocalizedCIProperties.CI_ID
Joining NAP and Discovery Views
The following query lists the NetBIOS name for all client computers that have a NAP restriction start time but do not have a NAP restriction end time. The restriction start time and the last statement of health time are also listed. The results are sorted by NetBIOS name. The query joins the v_ClientRestrictionHistory NAP view with the v_R_System discovery view by using the ResourceID column.
SELECT v_R_System.Netbios_Name0,
v_ClientRestrictionHistory.RestrictionStart,
v_ClientRestrictionHistory.LastSoHGenerationTime
FROM v_ClientRestrictionHistory INNER JOIN
v_R_System ON
v_ClientRestrictionHistory.ResourceID
= v_R_System.ResourceID
WHERE (NOT
(v_ClientRestrictionHistory.RestrictionStart IS NULL))
AND
(v_ClientRestrictionHistory.RestrictionEnd IS NULL)
ORDER BY v_R_System.Netbios_Name0