Queries for Boundary,Boundary Groups and Devices info

Query Devices,IP Address and IP Subnet per Device

select distinct A.Name0 as ‘PC Name’,c.IPAddress0 as ‘IP Address’,D.IP_Subnets0 as ‘IP Subnet’ from v_R_System A inner join
v_FullCollectionMembership B on A.ResourceID=B.ResourceID
Inner join v_GS_NETWORK_ADAPTER_CONFIGUR C ON A.ResourceID=C.ResourceID
Inner Join v_RA_System_IPSubnets D ON A.ResourceID=D.ResourceID
where CollectionID=’SMS00001′ and C.IPEnabled0=’1′
group by A.Name0,c.IPAddress0 ,D.IP_Subnets0
order by A.Name0,c.IPAddress0 ,D.IP_Subnets0

Get all Boundary Info

select * from vSMS_Boundary

Machine Count per Boundary Group

SELECT GroupName.Name, count(ip_subnets0) as ‘Machine Count’
FROM v_RA_System_IPSubnets
left join vSMS_Boundary AS bondary on v_RA_System_IPSubnets.ip_subnets0 = bondary.Value
left join vSMS_BoundaryGroupMembers AS GroupMembers on bondary.BoundaryID=GroupMembers.BoundaryID
left join vSMS_BoundaryGroup AS GroupName on GroupMembers.GroupID=GroupName.GroupID
Group by GroupName.Name
/* order by ‘Machine Count’*/
order by GroupName.Name

Report Boundary Info

select sys1.Name, sys1.DefaultSiteCode,
(select SUBSTRING(sys2.ServerNALPath, CHARINDEX(‘\\’, sys2.ServerNALPath) + 2,
CHARINDEX(‘”]’, sys2.ServerNALPath) – CHARINDEX(‘\\’, sys2.ServerNALPath) – 3 ) +
CASE sys2.Flags WHEN ‘1’ Then ‘ (Slow)’ WHEN ‘0’ THEN ” END + ‘; ‘ as ‘data()’
from vSMS_BoundaryGroupSiteSystems as sys2 where sys1.GroupID=sys2.GroupID
for XML path(”)) as ‘Site System’,
(select sys4.Value + ‘; ‘ as ‘data()’ from vSMS_BoundaryGroupMembers as sys3
left join vSMS_Boundary as sys4 on sys3.BoundaryID=sys4.BoundaryID where sys1.GroupID=sys3.GroupID
for XML path(”)) as ‘Boundary’, sys1.ModifiedOn, sys1.ModifiedBy
from vSMS_BoundaryGroup as sys1

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

Source : http://www.madanmohan.com/2011/01/sccm-sql-query-to-list-ip-subnets-of.html

Create collections based on subnets

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_Boundary on SMS_Boundary.Value = SMS_R_System.IPSubnets where SMS_Boundary.DisplayName = “BoundaryDescription”

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: