Filters based on subnet

Something I've been trying to do since upgrading to NS7 was to reproduce my company's subnet based collections. Even the Altiris Support tech I was working with had to go talk to an Engineer because of some error in NS7 when trying to handle a period (her words). Below is the raw SQL Altiris gave me to bypass the issue, hope it helps someone else out there.

SELECT
[vri1_Computer].[GUID] AS [_ResourceGuid],
[ajs3_Inv_AeX_AC_TCPIP].[IP Address],
[ajs3_Inv_AeX_AC_TCPIP].[Host Name],
[ajs2_vComputerResource].[IsManaged]
FROM
[vRM_Computer_Item] AS [vri1_Computer]
INNER JOIN [vComputerResource] AS [ajs2_vComputerResource]
ON ([vri1_Computer].[Guid] = [ajs2_vComputerResource].[Guid])
INNER JOIN [Inv_AeX_AC_TCPIP] AS [ajs3_Inv_AeX_AC_TCPIP]
ON ([vri1_Computer].[Guid] = [ajs3_Inv_AeX_AC_TCPIP].[_ResourceGuid])
WHERE
[ajs2_vComputerResource].[IsManaged] = 1
and [ajs3_Inv_AeX_AC_TCPIP].[IP Address] like 'XXX.XXX.XXX.%'

I do not have NS7 installed yet.. and I state yet... as I have 2k3 x32 installed and waiting on our dba to install sql2005 standard for me..

but in the NS 6 world I was using code to do this... simple code too...
what i did to come up with that code was go create a collection and choose enter SQL directly... a portion of the code was there... I just added the bottom...
select Guid
from vResource
where ResourceTypeGuid in
(
select ResourceTypeGuid
from ResourceTypeHierarchy
where BaseResourceTypeGuid='493435f7-3b17-4c4c-b07f-c23e7ab7781f'
)
and GUID IN
-- All code edited below for collection creations
(
Select _ResourceGuid
From Inv_AeX_AC_TCPIP
where subnet like 'xxx.xxx.xxx.%'
)

Maybe you can use the same thing and try it with NS 7.... create a new collection and see if anything is pre defined.... if all you are trying to do is create a collection then I do not see you needing all the information... what you are trying to do is create a collection based off of resourceguid. the name and all that other stuff is good for reports...


p.s. I am not a professional at this stuff.. do have my ACE certs but that just means I read many much gooder than others... :rofl:
but hope it helps ya some.. now if I can get NS installed.... then I can look into it a bit more for ya...

The code I posted works perfect in NS7. I posted it so others that don't know SQL and needed subnet based collections could do so without going crazy. I'm sure there are more efficient ways of doing the same thing, but this route works for now and is as easy as cut/paste.

Oh yeah, collections are now filters and the old way of setting up subnet based collections in NS6 are gone in NS7. I think we used the same method, or close to it, in our NS6 environment. When you get into NS7 you'll want to at least reference the above.

Thanks for the post

Astro - Thanks for the post it works great and has solved an issue for me.:woohoo:

I'm just happy someone else was able to save them self a headache from my post. It took the Altiris tech's over a day to get me that code.

https://kb.altiris.com/article.asp?article=48851&p=1

Check the very bottom line. I'm very curious about this.

that is the way I do it.. even after the report generator (in 6.5) is done the code is hard to read... I like cleaning it up from say..

name.inv_aex_ac_identification to just plain t0.name

High i have used your query for with success for class c and B subnets fine but i have tried to addapt it to a between fuction as i have some split subnets. but i don't think it is bring back all the info i need.

for example:

[ajs3_Inv_AeX_AC_TCPIP].[IP Address] between '10.38.1.1' and '10.38.29.254'

it dose not seem to bring back any 10.38.3.* or 10.38.4.* ect ect

what am i doing wrong. thanks guys

Your syntax is off is why, I made the same mistake too. Look below, this is how it should be setup.

SELECT
[vri1_Computer].[Guid] AS [_ResourceGuid],
[ajs3_Inv_AeX_AC_TCPIP].[IP Address],
[ajs3_Inv_AeX_AC_TCPIP].[Host Name],
[ajs2_vComputerResource].[IsManaged]
FROM
[vRM_Computer_Item] AS [vri1_Computer]
INNER JOIN [vComputerResource] AS [ajs2_vComputerResource]
ON ([vri1_Computer].[Guid] = [ajs2_vComputerResource].[Guid])
INNER JOIN [Inv_AeX_AC_TCPIP] AS [ajs3_Inv_AeX_AC_TCPIP]
ON ([vri1_Computer].[Guid] = [ajs3_Inv_AeX_AC_TCPIP].[_ResourceGuid])
WHERE
[ajs2_vComputerResource].[IsManaged] = 1
and ([ajs3_Inv_AeX_AC_TCPIP].[IP Address] like 'XXX.XXX.0.%'
or [ajs3_Inv_AeX_AC_TCPIP].[IP Address] like 'XXX.XXX.1.%'
or [ajs3_Inv_AeX_AC_TCPIP].[IP Address] like 'XXX.XXX.2.%'
or [ajs3_Inv_AeX_AC_TCPIP].[IP Address] like 'XXX.XXX.3.%'
or [ajs3_Inv_AeX_AC_TCPIP].[IP Address] like 'XXX.XXX.4.%')

thanks for the reply
yeah thats how i have got around it for now but i just wondered if there was an easier way.

There most likely is an easier and cleaner way to accomplish this task, but I have no SQL knowledge and this is what Symantec support gave me for a solution. It works, though it might not be the prettiest.