Sample Queries for Wake On LAN 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 Wake On LAN views to application management, discovery, and compliance settings views. The Wake On LAN views are most often joined to other views by using the ObjectID and ResourceID columns, and to other Wake On LAN views by using the ObjectType column.
Joining Wake On LAN, Application Management, and Compliance Settings Views
The following query retrieves the Configuration Manager object type, the deployment ID or advertisement ID, and the name for all objects that have Wake On LAN enabled. The results are sorted by object type and then by object name. The query joins the v_WOLGetSupportedObjects and v_WOLEnabledObjects Wake On LAN views by using the ObjectType column; joins the v_WOLEnabledObjects view with the v_Advertisement software distribution view by performing a LEFT OUTER JOIN on the ObjectType and AdvertisementID columns, respectively; and joins the v_WOLEnabledObjects view with the v_CIAssignment desired configuration management view by performing a LEFT OUTER JOIN on the ObjectType and Assignment_UniqueID columns, respectively. Using the LEFT OUTER JOIN retrieves all records from the v_WOLEnabledObjects view and only the associated records from the v_Advertisement and v_CIAssignment views.
SELECT v_WOLGetSupportedObjects.Name AS [Object Type],
v_CIAssignment.AssignmentID AS DeploymentID, v_Advertisement.AdvertisementID
v_WOLEnabledObjects.ObjectName AS Name
FROM v_WOLGetSupportedObjects INNER JOIN v_WOLEnabledObjects ON
v_WOLGetSupportedObjects.ObjectType = v_WOLEnabledObjects.ObjectType
LEFT OUTER JOIN v_Advertisement ON
v_WOLEnabledObjects.ObjectID = v_Advertisement.AdvertisementID
LEFT OUTER JOIN v_CIAssignment ON
v_WOLEnabledObjects.ObjectID = v_CIAssignment.Assignment_UniqueID
ORDER BY [Object Type], Name
Joining Wake On LAN and Discovery Views
The following query retrieves client computers, by NetBIOS name, that have been targeted for an advertisement or deployment with Wake On LAN enabled, as well as the name of the advertisement or deployment, the type of object, and the advertisement ID or deployment ID. The results are sorted by NetBIOS name, object type, and then object ID. The query joins the v_WOLTargetedClients Wake On LAN view with the v_R_System discovery view by using the ResourceID column, joins the v_WOLEnabledObjects and v_WOLTargetedClients Wake On LAN views by using the ObjectID column, joins the v_WOLGetSupportedObjects and v_WOLEnabledObjects Wake On LAN views by using the ObjectType column.
SELECT v_R_System.Netbios_Name0 AS Computer, v_WOLEnabledObjects.ObjectName,
v_WOLGetSupportedObjects.Name AS ObjectType, v_WOLEnabledObjects.ObjectID
FROM v_WOLTargetedClients INNER JOIN v_R_System ON
v_WOLTargetedClients.ResourceID = v_R_System.ResourceID INNER JOIN v_WOLEnabledObjects ON
v_WOLTargetedClients.ObjectID = v_WOLEnabledObjects.ObjectID INNER JOIN v_WOLGetSupportedObjects ON
v_WOLEnabledObjects.ObjectType = v_WOLGetSupportedObjects.ObjectType
ORDER BY Computer, v_WOLGetSupportedObjects.ObjectType, v_WOLEnabledObjects.ObjectID