Login to participate
Endpoint Management & Virtualization BlogsRSS

Splitting Subnets Into Multiple Collections

vortex0007's picture
vortex0007
January 29th, 2009
Filed under: , ,

A bit of SQL that I have found very useful for building Collections in Altiris Notification Server 6 (NS6) is posted below.

This SQL is very handy for helping to configure NS6 policies. For example a configuration policy might need different settings based on what subnet a machine falls into.

To use this, open the NS console > choose Resources > under Collections choose My Collections > Right-click on My Collections > Choose New Collection. Use the option to enter SQL directly and cut and paste the SQL below.

You will need to edit 3 places for this to work in your environment.

  1. Edit Line 8 where ip.[Subnet] Like '192.%' to your appropriate subnet.
  2. Edit Line 9, the very last 0 should be changed to be the beginning of your range.
  3. Edit Line 10, the very last number 110 should be changed to be the end of your range.

Save the results and you are complete.

select Guid from vResource
where ResourceTypeGuid in
(select ResourceTypeGuid from ResourceTypeHierarchy
	where BaseResourceTypeGuid='493435F7-3B17-4C4C-B07F-C23E7AB7781F')
and ( Guid in 
		(select [_ResourceGuid] 
			from [Inv_AeX_AC_TCPIP] ip
			where ip.[Subnet] LIKE '192.%'
AND (SUBSTRING(RIGHT(ip.[Subnet], (LEN(ip.[Subnet]) - 3)),0,PATINDEX('%.%',RIGHT(ip.[Subnet],(LEN(ip.[Subnet]) - 3)))) >= 0)
AND (SUBSTRING(RIGHT(ip.[Subnet], (LEN(ip.[Subnet]) - 3)),0,PATINDEX('%.%',RIGHT(ip.[Subnet],(LEN(ip.[Subnet]) - 3)))) <= 110)))
0 votes
MReavis's picture
MReavis
42 weeks 2 days ago

Similar query to build dynamic collections from sites/subnets

This query will locate all of the computers on your NS where the first 2 octets of the IP address match the first 2 octets of any IP range assigned to (in this example XXX) the defined site. If your IP schema is different you would need to modify the SQL parsing, as in you need to match down to the 3rd octet.

select distinct vr.guid
from vSiteSubnetMap sm
join Item sub          
	on sub.Guid = sm.SubnetGuid         
join item site          
	on site.guid = sm._resourceguid 
	and site.name = 'XXX' --Enter the name of your site from sites&subnets configuration
join Inv_AeX_AC_TCPIP ip
	on substring(ip.[ip address],1,charindex('.', ip.[ip address])+         
		charindex('.',substring(ip.[ip address],charindex('.', ip.[ip address])+1,len(ip.[ip address])-charindex('.', ip.[ip address])+1)) -1)         
	= substring(sub.Name,1,charindex('.', sub.name)+         
		charindex('.',substring(sub.name,charindex('.', sub.name)+1,len(sub.name)-charindex('.', sub.name)+1)) -1) 
join vResource vr         
	on vr.guid = ip._resourceguid         
	and ResourceTypeGuid in          
		(select ResourceTypeGuid          
		from ResourceTypeHierarchy          
		where BaseResourceTypeGuid='493435f7-3b17-4c4c-b07f-c23e7ab7781f')
0 votes