Get Accurate Results from ConfigMgr

In order to get the most accurate results out of Microsoft Endpoint Manager Configuration Manager (MEMCM / ConfigMgr) you must first understand the three key elements of reporting. They are: SQL Server queries, how data is stored by ConfigMgr, and reporting itself. By the way, data retention is often the most overlooked element. This blog post will examine a couple of SQL Server queries, show how deleted computers can impact results, and recommend the best SQL view to use. Once you get a good handle on each reporting element, you will be able to get the most accurate results from ConfigMgr.

Why Am I Not Getting Accurate Results from ConfigMgr?

The number one reason for inaccurate results is the combination of deleted computers, obsolete computers and poorly written SQL queries. When you delete a computer from the ConfigMgr console, it is NOT deleted from the database at the same time. Yes, you read that right! The ConfigMgr console does NOT delete a computer from the database right away.

You need to keep this in mind when you write your queries. If you are not careful, the results you get will be inaccurate from the ConfigMgr database.

SQL Server Queries

Now, let’s take a look at the following two very simple queries.

v_GS_COMPUTER_SYSTEM

This query will give you a count of all computers within your ConfigMgr environment using the dbo.v_GS_COMPUTER_SYSTEM.

Select
count(CS.ResourceID) as total
From
dbo.v_GS_COMPUTER_SYSTEM CS

v_R_System_Valid

This query will give you a count of all computers within your ConfigMgr environment using the v_R_System_Valid SQL view.

Select
count(RV.ResourceID) as total
From
dbo.v_R_System_Valid RV

Notice the difference between the two results? One computer is missing. This obviously is not a lot, but keep in mind that this is a tiny lab.

Now, let’s do the same thing, but for the total number of software titles. Wow! There’s a difference of over 400 software titles between the two queries. I only deleted one computer from my test lab to show you this difference, but can you imagine the wildly incorrect results that you would get for a continually changing environment with over 10,000 computers?

How Can I Get the Most Accurate Results from ConfigMgr?

Well, I have actually already given you the answer by showing you the problem. In my opinion, no matter what you query, always add the v_R_System_Valid SQL view to the query. This ensures that you get the most accurate results from ConfigMgr.

Below I will provide an overview of the differences between v_R_System_Valid and v_R_System.

v_R_System vs v_R_System_Valid

Using one over the other is a bit of a debate because, for the most part, they are the same. However, the major difference is that the v_R_System_Valid SQL view only includes ConfigMgr clients whereas the v_R_System includes all discovered computers (as well as “obsolete” ones) excluding deleted computers.

v_R_System_Valid is a subset of v_R_System. Now, I’ll talk about what the docs say regarding each view.

v_R_System

According to the ConfigMgr 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.”

https://docs.microsoft.com/en-us/mem/configmgr/develop/core/understand/sqlviews/discovery-views-configuration-manager

What the 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 computers are still listed within this SQL view. Therefore, if you want to remove obsolete computers from your query then you need to use the v_R_System_Valid SQL view.

v_R_System_Valid

This is what the ConfigMgr online documentation says about the v_R_System_Valid SQL view:

“Lists information about valid computers. This view is sorted by ResourceID and includes the client version, the processor type, the client’s domain, the NetBIOS name, the operating system and more. This view can be joined to other views by using the ResourceID column.”

The old version of these docs used to say, “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…”

Deleted and Obsolete Computers

What is the difference between deleted computers and obsolete computers? In ConfigMgr (all versions) a record for a computer is usually flagged as obsolete 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.

Whereas, delete, as the name suggests is a flag to say that the computer was deleted from MECM.

The simplest way to filter out obsolete computers is by using the v_R_System_Valid query.

Conclusion

Here’s the bottom line, in order to exclude deleted and obsolete computers from your 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 Power BI and SSRS reports. Let me know what you think of this blog post and please feel free to touch base with me @GarthMJ.