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