SMS 2003 Query Statement for IP Subnet

[SLMHC]

I want to creat collections that are tied to the subnet they are on. I have been able to figure this out for Direct statements but not for query.

Anyone do this type of sorting before?

thanks,

Dave

 

[SLMHC]

I have come up with this query to do it by default gateway:

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_NETWORK_ADAPTER_CONFIGURATION on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId where SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.DefaultIPGateway in ( "#.#.#.#" )

where # is your default gateway IP.

I have noticed that some cliets are reporting/showing up in the wrong groups. Did a properties check but DG is not listed.

thanks,

Dave

 

[billybarty]

This is the query language we use to group for one of our subnets. you should be able to modify the last line with the subnet and wildcard to show all systems.
select SMS_R_System.Name, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.NetbiosName from SMS_R_System inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId where SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like "xx.xx.xx.%

 

[SLMHC]

thanks ill check it out.

thanks,

Dave

 

[SLMHC]

billy:

that query worked great except for one thing. I install new clients in my office, subnet 10.1.X.X, then they go into production in one of 7 other subnets. The client does not see to move itself to the correct query group. am i being too impatient or will it not move itself?

thanks,

Dave

 

[billybarty]

Are they not moving even after updating the collection membership?

 

[SLMHC]

Ive updated the membership many times, reset the refresh interval as well...items are still not moving to the correct collections


thanks,

Dave