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

See Also