SCCM SQL Query to list IP Subnets of all the system that are in a collection


select distinct v_r_system.Name0, v_RA_System_IPSubnets.IP_Subnets0 from v_R_System inner join v_FullCollectionMembership on v_FullCollectionMembership.resourceid= v_r_system.resourceID inner join v_RA_System_IPSubnets on v_RA_System_IPSubnets.ResourceID=V_R_System.ResourceID where v_FullCollectionMembership.CollectionID=@CollID
Create a Prompt with the name COLLID using the following query.


select CollectionID,Name from v_Collection
If you want to only see the unique IP subnets, then use the following query.

select distinct v_RA_System_IPSubnets.IP_Subnets0 from v_R_System inner join v_FullCollectionMembership on v_FullCollectionMembership.resourceid= v_r_system.resourceID inner join v_RA_System_IPSubnets on v_RA_System_IPSubnets.ResourceID=V_R_System.ResourceID where v_FullCollectionMembership.CollectionID=@CollID

0 comments:

Post a Comment