Splitting Subnets Into Multiple Collections
Updated: 29 Jan 2009 | 1 comment
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.
- Edit Line 8 where ip.[Subnet] Like '192.%' to your appropriate subnet.
- Edit Line 9, the very last 0 should be changed to be the beginning of your range.
- 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)))
blog entry Filed Under:
About Endpoint Management Community Blog
The Endpoint Management Community Blog is the perfect place to share short, timely insights including product tips, news and other information relevant to the Endpoint Management community. Any authenticated Connect member can contribute to this blog. Filter by:
Blog Tags
Configuring Altiris Client Management Suite Altiris Deployment Solution Symantec Management Platform (Notification Server) Wise Packaging Basics Altiris Server Management Suite Best Practice Compatibility Tip/How to Helpdesk Solution Altiris IT Asset Management Installing Workflow Solution Ghost Solution Suite Windows Reporting Performance Upgrade Features ServiceDesk 7.x Database Agents Dell Management Products
Comments
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')Would you like to reply?
Login or Register to post your comment.