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%')
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.
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"
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.
