SQL query to find out SCCM clients registered within the last 60 days

Let’s find out SQL query to find out SCCM clients registered within last 60 days. In this post, we will find out the different options to find out SCCM client registration time and last scan hardware inventory information, etc.

You can create a collection of WQL queries also using similar logic. You can create dynamic collections using the easiest method to create WQL queries. I have also shared some of the best practices that you will need to follow before creating collections with WQL queries.

There are a lot of SCCM default reports available to help SCCM admins to get more data. I recommend going through the list of ConfigMgr default reports before trying to create a custom SQL report similar to the one explained in this post.

SMS_UUID_Change_Date0 Good for SCCM Client Registration Tracking?

As per my analysis, yes. The SMS_UUID_Change_Date0 is one of the easiest options to track SCCM client registration date and time. You can use the Creation_Date0 value also to track the registration of client devices. Even though technically it’s not client registration. I think Creation_Date0 helps to store the date and time when DDR for that device is created.

In my opinion, it’s better to go with the UUID change date to track the registration of SCCM clients. But you are the best judge for your environment. It’s worth noting the following points:

• If you have enabled AD discovery methods, then it’s not ideal track client registration with Creation Date value because the AD system discovery witll create the DDR for the device during the discovery process.
• UUID of the device will get changed during client cert and smscfg.ini regeneration processes .
• I have not validated UUID change date value with respect to tenant attach scenarios.

SQL query to find out SCCM clients registered within last 60 days

You can have several different combinations of queries with SMS_UUID_Change_Date0, Creation_Date0, and LastHWScan properties from the view called v_R_System. I have selected the following query to get more accurate data in the lab environment.

As I mentioned in the above section, you are the best judge for your environment. Let’s follow the steps mentioned below to find out SCCM clients registered within last 60 days.

• Open the SQL Management Studio.
• Connect your Database Engine.
• Right Click on your database CM_XXX and click on ‘New Query’
• Copy the following SQL query to find the report of client registration date details.
• Click on the Execute button.

 select  ResourceID, Netbios_name0,
  SMS_UUID_Change_Date0, Creation_Date0 FROM v_R_System where SMS_UUID_Change_Date0 <= Dateadd(day, -1, getdate()) 
  and SMS_UUID_Change_Date0 > Dateadd(day, -60, getdate())

NOTE! – UUID property value is set to NONE if there is no client installed on a system record already present in SCCM DB.

SCCM Query to Find out Clients Registered before 60 days

Now, let’s find out client devices that are registered to SCCM before 60 days using the following SQL query. You can make it more complex with the last hardware inventory property to get more accurate values. However LastHWscan value is not mandatory to get the details of clients that are registered before 60 days from now.

You can use the steps mentioned in the above section of the post and use the following query to get the details of the devices registered before 60 days. SQL query to find out SCCM clients registered within last 60 days is already shared in the above section of the post.

select  h.ResourceID, h.Netbios_name0,
   t.LastHWScan, h.SMS_UUID_Change_Date0, h.Creation_Date0 FROM v_R_System h INNER JOIN v_GS_WORKSTATION_STATUS t ON h.ResourceID = t.ResourceID 
   where h.Creation_Date0 <= Dateadd(day, -60, getdate()) and h.SMS_UUID_Change_Date0 <= Dateadd(day, -60, getdate()) and t.LastHWScan <= Dateadd(day, -60, getdate())

Author

Anoop is Microsoft MVP! He is a Solution Architect in enterprise client management with over 17 years of experience (calculation done in 2018). He is Blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc…..…