Sample Queries for Network Access Protection in Configuration Manager
Updated: January 1, 2014
Applies To: System Center 2012 Configuration Manager, System Center 2012 Configuration Manager SP1, System Center 2012 R2 Configuration Manager
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 Compliance Settings 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 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
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