What Are the Supported SQL Server Views to Use with SCCM Reporting?

Have you ever wondered what SQL Server views you can use with SCCM current branch reporting? You wouldn’t be alone in asking this question because the views are not clearly documented. I’m here to tell you that the answer is quite simple. The only supported SQL Server views are the ones which have SQL Server smsschm_users security rights. Those are the only supported views to be used by reporting regardless of whether it’s Power BI or SQL Server Reporting Services (SSRS). This is also true for table-value functions that are used with the Role-Based Administration (RBA) feature. The only supported table-value functions are the ones which have SQL Server smsschm_users security rights.

Unsupported SQL Server Views

I’m seeing a lot of unsupported SQL Server views floating around these days. They appear in forum post answers and in blog posts. In the case of some recent blog posts, I’ve read where some folks are telling others to use a whole host of unsupported views, tables, stored procedures, functions, etc.

If that wasn’t bad enough, in several cases where a problem occurs after one of these unsupported SQL Server views is put into production, the advice is to change the SCCM database rights. This is definitely not supported by Microsoft. In these scenarios, changing database rights appears to be the only way to solve problems, such as access denied or unavailable details, when running reports using unsupported SQL Server views or table-value functions.

Supported SQL Server Views

This SQL Server query shows what views and table-value functions are supported. By the way, there are about 1500 supported objects in my lab. These views and table-value functions are the only SQL Server objects you should use within SCCM reporting.

SELECT Distinct

Case So.type

When ‘V’ then ‘View’

When ‘U’ then ‘Table’

When ‘FN’ then ‘SQL scalar function’

When ‘IF’ then ‘Table Function (RBA)’

When ‘TF’ then ‘Table Function (RBA??)’

When ‘P’ Then ‘SQL Stored Procedure’

When ‘SQ’ then ‘Service queue’

When ‘FS’ then ‘Assembly (CLR) scalar-function’

When ‘S’ then ‘System base table’

When ‘FT’ then ‘Assembly (CLR) table-valued function’

Else so.type

end as ‘Object type’,

CASE

WHEN SO.name like ‘v[_]RA[_]%’ THEN ‘Resource Array’

WHEN SO.name like ‘v[_]R[_]%’ THEN ‘Resource’

WHEN SO.name like ‘v[_]HS[_]%’ THEN ‘Inventory History’

WHEN SO.name like ‘v[_]GS[_]%’ THEN ‘Inventory’

WHEN SO.name like ‘v[_]CM[_]%’ THEN ‘Collection’

WHEN SO.name like ‘%Summ%’ THEN ‘Status Summarizer’

WHEN SO.name like ‘%Stat%’ THEN ‘Status’

WHEN SO.name like ‘%Permission%’ THEN ‘Security’

WHEN SO.name like ‘%Secured%’ THEN ‘Security’

WHEN SO.name like ‘%Map%’ THEN ‘Schema’

WHEN SO.name = ‘v_SchemaViews’ THEN ‘Schema’

ELSE ‘Other’

END As ‘Type’,

SO.name As ‘ViewName’

FROM

–Role/member associations

sys.database_role_members members

JOIN sys.database_principals roleprinc ON roleprinc.principal_id = members.role_principal_id

–Roles

LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = roleprinc.principal_id

–Permissions

LEFT JOIN sys.columns col on col.object_id = perm.major_id AND col.column_id = perm.minor_id

–Table columns

LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id

Left join sysobjects so on perm.major_id = SO.id

WHERE

— SO.name like ‘v_ApplicationAssignment’

— and

So.type in (‘IF’,’V’)

and SO.name not like ‘v_CM_RES_COLL%’

and SO.name not like ‘fn_RBAC_CM_RES_COLL%’

and roleprinc.name = ‘smsschm_users’

order by

1,

SO.name

User Voice Items

What should you do if there isn’t a supported SQL Server view or table-value function that has the details you need? I had this exact same question for David James, Microsoft’s SCCM Director of Engineering. His answer to me was: Create a SCCM User Voice item. Tell his team why it is needed, how it is important, and what costs/time-saving benefits it will have for you.

For example, you could say that each week your service desk team logs into each server in order to check “x.” Then they must create a report on the results. This task takes your team about 45-minutes. It is important to have these “x” details in a reporting column because… (you fill in the blanks!). If it was a supported SCCM reporting SQL Server view, your service desk team would no longer need to spend 45-minutes each week to complete this task.

Also, by creating a User Voice item, it shows Microsoft that reporting is important to you. Therefore, they need to invest more into it!

 

If you have any questions about what supported SQL Server views or table-value functions to use with SCCM current branch reporting, please feel free to contact me at @GarthMJ.