Get the Most Accurate and Up-to-Date Data Using the v_R_System_Valid SQL Query in Configuration Manager 2012
By Garth Jones
When you delete a PC from the System Center 2012 Configuration Manager (CM12) console it is NOT deleted from the database at the same time. Yes you read that right! The CM12 console does NOT delete a PC from the database right away. Depending on how you write your queries, this may affect your results and you may not get the most up-to-date information from CM 12’s database.
Let’s look at the following two simple queries.
The first query will give you a count of all software installed within your CM12 environment using the v_GS_COMPUTER_SYSTEM SQL view.
The second query will give you a count of all software installed within your CM12 environment using the v_R_System_Valid SQL view.
Notice the difference between the two results. There are over 500 missing software titles in the “Total RV” count! I only deleted 4 PCs in my test lab to show you this difference, but imagine the incorrect results that you would get for a continually changing environment with 10,000 PCs or more.
v_R_System and v_R_System_Valid
According to the CM12 online documentation the v_R_System SQL view, “Lists all discovered system resources by resource ID, resource type, whether the resource is a client, what type of client, client version, NetBIOS name, user name, operating system, unique identifier, and more.”
What the CM12 online documentation does not say is that the v_R_System SQL view filters out systems that are flagged as deleted, but haven’t actually been removed from the database yet.
However, this may still give you some unexpected results, as obsolete PCs are still listed within this SQL view. Therefore, if you want to remove obsolete PCs from your query then you need to use the v_R_System_Valid SQL view.
This is what the CM12 online documentation says about the v_R_System_Valid SQL view:
Lists all discovered system resources that are not in an obsolete or decommissioned state. This view is a subset of the v_R_System view…
What is the difference between deleted and obsolete PCs? In CM12 and CM07 a record for a PC is flagged as obsolete usually when it is replaced by a newer one for the same client. The newer record becomes the client’s current record and all old records are flagged as obsolete.
This is why you may want to filter out obsolete PCs by using the v_R_System_Valid query.
CM07 uses both the v_R_System and v_R_System_Valid SQL views, but in CM07 when you delete a PC it removes the PC from the database immediately! The only difference between the SQL views is that v_R_System_Valid filters out obsolete PC records.
Here’s the bottom line, in order to exclude deleted PCs and obsolete PCs from you query results you should always use the v_R_System_Valid SQL view. That is why at Enhansoft, in order to give you the most accurate results, we always use the v_R_System_Valid SQL view for all of our SSRS reports.