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