SCCM 2012 R2 View Problem

I recently discovered the database view on our SCCM 2012 R2 view v_UsersPrimaryMachines contained no data.  All of the data is present on all primary site servers.  After tracing through all views which the data is based on I narrowed the problem down to the vLocalSystemIDXRef view.  vLocalSystemIDXRef uses two functions, dbo.fnGetSiteRangeStart and dbo.fnGetSiteRangeEnd.  Here is the original SQL for the view:

After examining other views which used these two functions in the WHERE clause it became clear that all of the other views also included an OR statement in the WHERE which called a function dbo.fnIsCasOrStandalonePrimary to always return the data on CAS’s and stand-alone primary site servers.

I opened a case with MS to make them aware of the defect so it could be documented and corrected in a future release.  After several rounds of troubleshooting and explaining my findings they finally admitted a problem (in a round-about way) and we’ve started the process of opening a product change request.  Why a defect requires a product change is beyond me but that’s beside the point…

If you want to correct the v_UsersPrimaryMachines view you must modify the SQL for vLocalSystemIDXRef to the following:

I’ll try to update this post if/when a final resolution is provided by MS.  Knowing MS it’ll take some time though.  I’ve reported other SCCM 2012 R2 bugs which have yet to be resolved after 9+ months…

 

Jason Galbreath