IT Management Suite

I need to create Filters based on IP ranges, so that I can group sites based on that.. Anyone done this before? Our AD subnet and sites info is not reliable.

I've done this very thing (for the very same reasons).  I don't have the actual SQL scripts handy at the moment, but the gist is that you have to make an automation policy that runs regularly (nightly) on the devices in the CMDB.  You'll end up with one automation policy per group you want to sort devices into.  So, (for example) one policy will grab anything device who's last octet of their IP ends in .1 through .6, and places it in organizational view "Org1" Another will grab any device who's last octet of their IP ends in .100 and places it in organizational view "Org2". I'll make a reminder for myself to grab the SQL I used and post it here for you and others to use tomorrow.

-Prep

 

Hi,

start with this query:

 

SELECT vc.[Guid], vc.[Name], vc.[IP Address], tcpip.Subnet, vc.[IsManaged]
FROM [vComputer] vc
 JOIN Inv_AeX_AC_TCPIP tcpip ON vc.Guid = tcpip._ResourceGuid
WHERE
 vc.[IsManaged] = '1'
 AND
 tcpip.Subnet = '192.168.10.0'

 

This query gives you all computers in the subnet 192.168.10.0.

 

If you want to filter by ip-adress you can change the above statement to something like this:

SELECT vc.[Guid], vc.[Name], vc.[IP Address], vc.[IsManaged]
FROM [vComputer] vc
 WHERE
 vc.[IsManaged] = '1'
 AND
 vc.[IP Address] LIKE '192.168.10.%'

 

Regards

Nice, looks like MD Investigate got you what you wanted!  I'll also offer that you can do something similar based off of machine name.  For example, if you wanted only devices who's name started with "PC" and ended in an even digit, you'd use the following querry in your automation policy:

SELECT [Guid]
      ,[Name]
  FROM [Symantec_CMDB].[dbo].[RM_ResourceComputer]
  where IsManaged = 1
  and Name like 'PC%'
  and ( Name like '%0'
  or Name like '%2'
  or Name like '%4'
  or Name like '%6'
  or Name like '%8')

Once you have the querry returing the devices you want to dump in a group, set the automation policy to run the "Assign to Organizational Group" task under "Jobs and Tasks->System Jobs and Tasks->Notification Server->Automation Policy Tasks" with the input parameter set to a custom value of the group you want the devices dumped into.  Good luck!

thanks guys, i will try it out.