SCCM

Create SCCM Device Collection for Visual Studio

In this post I will show you how to create SCCM device collection for Visual Studio. I will share the query to create device collections for both Visual Studio 2019 and Visual Studio 2017.

Sometime ago I shared a post on how to deploy Visual Studio 2019 using SCCM. Yes you can silently deploy Visual Studio 2019 using SCCM and even deploy Visual Studio updates with latest SCCM release.

You can use the built-in reports to find out the computers installed with Visual Studio 2017 and 2019 or any other software. However sometimes you may require a device collection for computers running Visual Studio software in some cases.

SQL Query to Find Computers Installed with Visual Studio

If you are looking for a SQL query that shows you the computers installed with Visual Studio you can use the following query. The below query outputs all the computers running Visual Studio 2019 Professional edition. If you wish to query the Visual Studio 2019 enterprise edition, you can change the DisplayName0 to ‘%Visual Studio Enterprise 2019%’.

The query may vary while creating the device collection because you can use different ways to gather a particular piece of software. I found this query working perfectly in my setup hence I am sharing it. If you have got a better query than what I shared, use it.

Launch the SQL Server Management studio and connect to database engine. Right click Configuration Manager database and click New Query. Run the following query.

Select Distinct
 sys.Netbios_Name0,
 sys.User_Domain0,
 sys.User_Name0,
 sys.AD_Site_Name0
 FROM
 v_R_System sys
 JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
 WHERE
 sys.ResourceID in (select sys.ResourceID
 from
 v_R_System sys
 JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
 where
 DisplayName0 like '%Visual Studio Professional 2019%')
SQL Query to Find Computers Installed with Visual Studio
SQL Query to Find Computers Installed with Visual Studio

Create SCCM Device Collection for Visual Studio

The steps to create SCCM device collection for Visual Studio are as follows.

  • Launch the SCCM console.
  • Navigate to Assets and Compliance\Overview\Device Collections.
  • Right click Device Collections and click Create Device Collection.
  • Specify the collection name as Visual Studio 2019 Professional and set the limiting collection.
Create SCCM Device Collection for Visual Studio
Create SCCM Device Collection for Visual Studio

On the Membership Rules window, click Add Rule > Query Rule. Specify a name for the query and click Edit Query Statement. Select the Criteria tab and enter the below query. This SCCM query will output all the computers installed with Visual Studio 2019 Professional software.

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Visual Studio Professional 2019"
Create SCCM Device Collection for Visual Studio
Create SCCM Device Collection for Visual Studio

Note – You can also press the green arrow icon to run the query instantly. In the output you should find all the computers installed with Visual Studio 2019.

After you enter the query click OK and complete the remaining steps. The device collection will take some time to populate the devices that are installed with Visual Studio 2019. To speed up this operation you can right click the device collection and click Update Membership.

I will share few more queries related to Visual Studio installs. Using the same method as shown above you can create device collections for Visual Studio 2017 as well.

SCCM Query to find all computers installed with Visual Studio Enterprise 2019

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Visual Studio Enterprise 2019"

SCCM Query to find all computers installed with Visual Studio Professional 2017

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Visual Studio Professional 2017"

SCCM Query to find all computers installed with Visual Studio Enterprise 2017

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Visual Studio Enterprise 2017"

I guess you understood how to create SCCM device collection for Visual Studio. If you have any questions please add them in comments section.

Prajwal Desai

Hi, I am Prajwal Desai. For last few years I have been working on multiple technologies such as SCCM / Configuration Manager, Intune, Azure, Security etc. I created this site so that I can share valuable information with everyone.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button

Thanks for Supporting PrajwalDesai.com

We deliver articles on Configuration Manager, Intune, Windows Servers and other technologies at no cost to you. To do this, we display ads from only trusted partners. To continue on our site, simply turn off your ad blocker or whitelist this site and refresh the page.