Sample Queries for Network Access Protection in Configuration Manager

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 compliance settings 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 

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