Client Management Suite

 View Only

  • 1.  SQL Query - Sites and Services Info

    Posted Aug 27, 2014 03:57 PM

    Does anyone have a SQL query they can share that will display the all the IP Subnets, their assigned sites as well as the site server assigned to the subnet within in Altiris?

    To summarize:

    Subnet - Subnet Mask  - Site Name - Task Server Name assigned to manage that subnet

    Thanks!



  • 2.  RE: SQL Query - Sites and Services Info

    Posted Aug 28, 2014 01:21 AM

    I have found one query which is match with your criteria

    https://www-secure.symantec.com/connect/forums/queryreport-show-what-site-pc-belongs



  • 3.  RE: SQL Query - Sites and Services Info

    Posted Aug 28, 2014 11:17 AM
      |   view attached

    I have a report which may give you what you want.  It shows:

    Site name, subnet, mask, # of devices, site server Name.  It also shows whether the site server is a pkg server, task server, and/or DS server and free disk space on the agent volume.

    The only thing I couldn't figure out how to show were manually assigned package servers.  This only shows the package servers that are in the subnet for the record shown.

    Import the attached XML in the report folder where you want the report.  If you'd rather not import, you can find the sql in the XML.



  • 4.  RE: SQL Query - Sites and Services Info

    Posted Aug 28, 2014 12:49 PM

    Wow!  Nice query JoeVan!  I have a few questions if you do not mind...

    1. Does this list showing the number of devices represent the number of machines found in each of their respective subnets?
    2. If the server field is "null", does that mean a site server is not found in that subnet
    3. Is there a way to show subnets that are NOT assigned to a site?  I am hoping to have that information at hand too.

    Thanks for the efforts!



  • 5.  RE: SQL Query - Sites and Services Info
    Best Answer

    Posted Aug 28, 2014 03:15 PM

    1) Yes, the number of managed devices on that subnet.  

    2) Empty server field means there is no site server in that subnet.  However, you may have a server manually assigned, the report doesn't show this.  If you have two servers in a subnet, they will show up as two lines.  

    3) This report is primarily to show sites without site servers.  I have another that shows unassigned subnets along with a count of how many devices are in each subnet. Below is the SQL for that report.  
     

    --unassigned subnets

    SELECT

    subnet.[_ResourceGuid]

    , Subnet.Subnet

    , Subnet.[Subnet Mask]

    ,(SELECT COUNT(vc2.Guid)

    FROM vComputer vc2

    LEFT JOIN Inv_AeX_AC_TCPIP IP2 ON IP2.[_ResourceGuid] = vc2.Guid

    LEFT JOIN Inv_Subnet subnet2 ON IP2.[Subnet] = subnet2.[Subnet] and IP2.[Subnet Mask] = subnet2.[Subnet Mask]

    WHERE vc2.[IsManaged] = 1

    and subnet.[_ResourceGuid] = subnet2.[_ResourceGuid]

    ) as [qty machines]

    FROM

    Inv_Subnet subnet

    WHERE subnet.[_ResourceGuid] NOT IN

    (SELECT [SubnetGuid] from vSiteSubnetMap)

    AND subnet.[_ResourceGuid] IN (

    SELECT subnet.[_ResourceGuid]

    FROM vComputer vc

    LEFT JOIN Inv_AeX_AC_TCPIP IP ON IP.[_ResourceGuid] = vc.Guid

    LEFT JOIN Inv_Subnet subnet ON IP.[Subnet] = subnet.[Subnet]

    WHERE vc.[IsManaged] = 1

    )

    ORDER

    BY [qty machines] desc

     



  • 6.  RE: SQL Query - Sites and Services Info

    Posted Aug 28, 2014 03:42 PM

    Here is another query that might help... change the join queries to left join and the join vsubnet to right join will give you subnet not assigned to sites. Beware, there can be whole lot of un-mapped subnets, if you have laptop users connect via vpn. The Basic Inventory on the SMA updates the vsubnet table with those subnets.

    sql query

    select

    isnull(vs.name,'** Not Assigned **') as Site

    ,vi.Name as [Assigned Subnet]

    ,dc.[Device Count]

    --,vsub.Subnet

    --,vsub.[Subnet Mask]

    ,ss.[Assignment Type]

    ,ss.[Site Server] as [Assigned Site Server]

    ,ss.[OS Name]

    ,ss.[IP Address]

    from vSite vs -- site table

    join vSiteSubnetMap sm on sm._ResourceGuid = vs.Guid -- join site/subnet map table to Site table map subnet to Site

    join vSubnet vsub on vsub.Guid = sm.SubnetGuid -- join subnet table to site/subnet map table

    join vItem vi on vi.Guid = vsub.Guid -- join vitem table to get cidr subnet name

    -- joining Site servers to the Site table via site guid

    join (

    -- subquery for manually assigned Site Servers

    SELECT

    c1.Name [Site Server]

    ,c1.[OS Name]

    ,ra1.ChildResourceGuid [SiteGuid]

    ,NULL [IP Address]

    ,'Manually Assigned' [Assignment Type]

    FROM ResourceAssociation ra1

    join vComputer c1 on c1.Guid = ra1.ParentResourceGuid

    WHERE ra1.ResourceAssociationTypeGuid = '5E8E3C61-A80C-4b0a-A228-DBF97607CEE4' -- Manual Computer to Site Service association

    union -- join both subqueries

    -- subquery for Site Server/package server mapped site by subnets

    SELECT

    c2.Name [Site Server]

    ,c2.[OS Name]

    ,NULL [SiteGuid]

    ,tcpip.[Ip Address]

    ,'Package Server in Site Subnet' [Assignment Type]

    FROM vRM_PackageService ps

    JOIN ResourceAssociation ra2 ON ps.[Guid] = ra2.ParentResourceGuid

    AND ra2.ResourceAssociationTypeGuid='5F00E96B-93F3-41f0-94A7-7DBBB8AEF841' -- Site Service To Computer

    JOIN Inv_AeX_AC_TCPIP tcpip ON tcpip._ResourceGuid = ra2.ChildResourceGuid

    JOIN vcomputer c2 on c2.guid = tcpip._ResourceGuid -- join vcomputer table to computer name on os name

    WHERE tcpip.[Subnet Mask] != '255.255.255.255' AND tcpip.[IP Address] != ''

    ) ss on ss.SiteGuid = vs.Guid -- join via siteServer guid

    OR (dbo.fnIsIPInSubnet(ss.[IP Address], vsub.Subnet, vsub.[Subnet Mask]) = 1 -- or If Package Server IP is in site's subnet then join

    AND ss.[IP Address] IS NOT NULL)

    -- subquery to get device count via subnet

    join (

    select

    i.Name [Subnet], count(*) as [Device Count]

    from vComputer c

    join Inv_AeX_AC_TCPIP ip on ip._ResourceGuid = c.Guid and ip.[IP Address] = c.[IP Address] and ip.[MAC Address] = c.[MAC Address]

    join vSubnet sub on sub.Subnet = ip.Subnet and sub.[Subnet Mask] = ip.[Subnet Mask]

    join vItem i on i.Guid = sub.Guid

    group by i.Name

    ) dc on dc.Subnet = vi.Name

    order by vs.Name

     

    SiteSubnet.png

     

     



  • 7.  RE: SQL Query - Sites and Services Info

    Posted Aug 28, 2014 04:20 PM

    Thanks to you all!