Sample Queries for Queries 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 query demonstrates how the query view can be joined to a security view. In most cases, the v_Query view will not be used in reports.

Joining Query and Security Views

The following query lists the query ID, query name, user name, and instance permissions for the user on the query object. The v_Query view is joined to the v_UserInstancePermNames security view by using the QueryID from v_Query and InstanceKey from v_UserInstancePermNames. Because there might be other secured objects with the same value as the InstanceKey (for example, MCM00001 could be a custom query or a package), the query also filters specifically for query objects by using the WHERE clause and an ObjectKey value of 7.

SELECT Q.QueryID, Q.Name AS QueryName, UIP.UserName, UIP.PermissionName 
FROM v_Query Q INNER JOIN v_UserInstancePermNames UIP 
ON Q.QueryID = UIP.InstanceKey 
WHERE UIP.ObjectKey = 7 
ORDER BY Q.Name, UIP.UserName