Video Screencast Help

Site Management - Need help to create dynamic Filters based on Site

Created: 11 Feb 2014 • Updated: 12 Feb 2014 | 5 comments
This issue has been solved. See solution.



First off, our AD Site to subnet assignment is unusable, too messy.

 

I created Sites and Subnets manually, assigning subnets to sites.  Now I am stump on how to create Filters based on the Sites I created, so that It would display desktops and servers or is there a better way of doing this?

Thanks 

Comments 5 CommentsJump to latest comment

skhs's picture
select                              
comp.[Guid]                
from vSubnet sub                              
left join vSiteSubnetMap vss on vss.[SubnetGuid] = sub.[guid]                              
left join vSite vs on vs.[Guid] = vss.[_ResourceGuid]                              
join vItem vi on vi.[guid] = sub.[Guid]                              
join Inv_AeX_AC_TCPIP tcp on tcp.[subnet] = sub.[Subnet]                              
join vComputer comp on comp.[guid] = tcp.[_ResourceGuid]                             
 
Where vs.Name like '%SITENAMEGOESHERE%'
 
oR 
 
select guid from vcomputer vc 
Left Join [Inv_AeX_AC_TCPIP] tcpip On vc.Guid = tcpip.[_ResourceGuid]
And tcpip.[_id] = ( Select top 1 tcpip2.[_id] From [Inv_AeX_AC_TCPIP] tcpip2  
Where vc.Guid = tcpip2.[_ResourceGuid]   
AND tcpip2.[IP Address] NOT IN ('127.0.0.1', '0.0.0.0', '')   
AND tcpip2.[IP Address] IS NOT NULL  Order By tcpip2.[IP Address] Asc
)
 
left join  vSubnet sub on tcpip.[subnet] = sub.[Subnet]
left join vSiteSubnetMap vss on vss.[SubnetGuid] = sub.[guid]  
left join vSite vs on vs.[Guid] = vss.[_ResourceGuid] 

Where vs.Name like '%SITENAMEGOESHERE%'

 

If this works mark as solution please

Adam Y's picture

I've had a very similar experience with our AD environment being pretty much unusable.

To counter this with filters, I've had to create custom SQL filters following the same information created in the sites you've made.  I know this is kind of double work for organizing subnet data, but it works for us.

So first, is creating a new filter: Raw SQL:

SELECT
   [vri1_Computer].[Guid],
   [vri1_Computer].[Name]
FROM
   [vRM_Computer_Item] AS [vri1_Computer]
      LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS [dca2_AeX AC TCPIP]
         ON ([vri1_Computer].[Guid] = [dca2_AeX AC TCPIP].[_ResourceGuid])
WHERE
            ([dca2_AeX AC TCPIP].[Subnet] between N'160.22.80.0' and N'160.22.99.254') --LAN
            or
            ([dca2_AeX AC TCPIP].[Subnet] between N'160.22.100.0' and N'160.22.114.254') --LAN
            or
            ([dca2_AeX AC TCPIP].[Subnet] between N'160.22.116.0' and N'160.22.127.254') --LAN      
            or
            ([dca2_AeX AC TCPIP].[Subnet] between N'10.20.116.0' and N'10.20.119.255') --Wireless

Then you will have to create an update membership task to keep this data current at regular intervals.

I hope this heps. 

SK's picture

Here is an easier method:-  https://www-secure.symantec.com/connect/blogs/how-...

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

SOLUTION