Client Management Suite

Does anyone have a query that will show me what altiris site a computer belongs to?  

I have a query that partially works; however, in my query if my subnet is part of a subnet encompassed by a larger subnet/mask then its not showing me.  I essentially need to find out how to relate the encompassed subnets to the larger site/subnet information in order to get the name.  Any help is appreciated

can you not do it by site server ?

C

Is there something that shows me that already?

I am looking to create a report of  all computers of a specific model that shows me the computer name, primary user, and location of that PC. 

For location I was looking at somehow associating it to what site it is in.  If there is something that shows what sire server it communicates with I assume I can find out the proper tables or form of a query that would get me by

each agent on a client has the site server listed so will be in one of the tables

also in My portal there is a view you can choose that shows site servers and all of the computers that are assigned to them so there will be a table for that

I particularly like that view as it shows active and inactive computers reminds me of our old DS ....sigh!!!

HTH C

Do you happen to know what the table names are?  I can't find any information that shows the site server associated with the client.  I know the client shows download source but if it moves around and hasn't downloaded anything recently it might now be current.  I dont see anywhere that shows the site server.  Just the task server which we only have a couple of those vs many site servers.

Hi

sorry thinking of my  own environment where all site servers are task servers and package servers

so i guees you have site servers as just package servers?

C

I have a report that can show site by subnet. Use this SQL

SELECT

*,

CASE

WHEN [IP Address] LIKE '10.190.%' THEN 'New York'
WHEN [IP Address] LIKE '10.200.%' THEN 'Glasgow'
WHEN [IP Address] LIKE '10.170.%' THEN 'Rome'
WHEN [IP Address] LIKE '192.168%' THEN '-Remote Connection - undefined'

ELSE 'Paris' END AS 'Site'

FROM vComputer vc

Thanks Joe, but that would be very complex for our environment as we have over 1000 vlans.  I have 500 sites which in the site/subnet I am utilizing the subnet mask to cover a broad range of vlans for each locations.  So I am trying to utilize that in order to not have to type out 1000 IP addresses in the query. 

For example I have a site 10.50.128.0 with a subnetmask of 255.255.248.0 which covers a few vlans and since that is associated with a site called Chicago or whatever I would like to utilize that data that is already there to build a query to determine if the pc resides in that site.

If I had only 20 or so sites I could see where hardcoding ip in the query qouldn't be that bad but for our environment it would take me a day or two just to type the query lol

Inv_Client_Task_Resource holds the guid of each machine and their corresponding Site Server GUID.  It should be easy to write a query to convert them to computer name and Site Server name.

Perhaps something like this:

Hi,

go to Reports--> Notification Server Management -->Server---> Resource Reports- --> Site Servers in Subnet Report

That report doesn't show the workstations in the location, just site servers, which I don't have one at each location.  Also it goes by just the full subnet.  I am looking for a report that I can use that goes more by the site vs the subnet since my site contains a subnet mask that covers multiple subnets. 

for example if I'm using a subnet/mas of 10.24.132.0/24  I dont want to have to call out each subnet under that mask.

We're going to assume you are looking for any computer assigned to Task Server X, right?  Package servers are trickier to figure out.

OR

Are you looking for an actual "site" assignment, which may include multiple Task Servers?

The two are very different rerpots.

Going with the "guess" that you're looking for systems based on Task Server assignment, there's a report you can start with (clone it) called Task Server Summary found under Reports \ Task Server \ Status.  The essence of the SQL (minus security) looks like this:

select vts.[ResourceGuid], vc.[Name] as 'Server Name', cts.[Build], ( select count(*) 
from [dbo].[Inv_Client_Task_Resources] ctr
where ctr.[ClientTaskServerGuid] = cts.[ClientTaskServerGuid] ) as 'Client Count',
vc.[IP Address] as [IP Address], vc.[Domain], vc.[OS Name] as [OS Name]
from [dbo].[vActiveTaskServiceComputers] vts
join [dbo].[vComputer] vc on vc.[Guid] = vts.[ResourceGuid]
join [dbo].[Inv_Client_Task_Servers] cts on cts.[_ResourceGuid] = vts.[ResourceGuid]

select vc1.Name "Client", vc2.Name "Server"
from vComputer vc1
join Inv_client_task_resources ctr on ctr._ResourceGuid = vc1.Guid
join vComputer vc2 on vc2.Guid = ctr.ClientTaskServerGuid

I'm actually looking for a report for "These are the clients in X site, and they're connected to Y task server".

I'm running the Reset Client Task Agent task daily but I still have many systems that don't connect to their appropriate server so I'd use this info for troubleshooting agents.

Thanks!

I am not looking for anything related to task servers at all.  The reason I am not looking for this is that I have 450 sites with 450 package servers but only say 10 site servers since I do not have IIS installed on most of the site servers, and can not install it.

for the sake of argument pretent I have 0 task servers in my environment.

I am looking for what computers fall withing a site from the sites and subnets section of the system.

High Tower, half of what you're looking for is above in my previous post - that is, what task server you're connected to.

Sonic, there's a fundamental problem with what you're asking for, though it can be done.

Sort of.

The simple truth is: we don't actually assign computers to sites.  YOU can do so through "manual site assignments" and we have a stored procedure that lookst that up.  But we don't.

We let you define a site by subnet range.

When a client computer requests something that has site boundaries (e.g. package download) we look at the subnet for that client, query for what package servers service that subnet based on their site assignments, and deliver a list accordingly.  The client is not actually "in" the site per-se.

So the report is going to be interesting.

You'll have to query for all subnets in a site, then match all computers based on that subnet list, and quite frankly, it'll be a slow report if you don't also do something like build a stored procedure and/or views.

CAN it be done?  Yes.  I'd sort of like to toy with it.  But it's not out-of-the-box, because the product frankly doesn't care, so we don't track it already.

I hope that makes sense.  We'll see what we can do - the community and I.  :D

Here is something I came up with to have a filter that would show me all computers in a site by calling out the specific site name.  So I was trying to engineer something based off of this but without specifically calling out the name and just having the associated name show up on the report, but I was unable to figure something out that did not require me to enter the site name, in this query %_sitename% is a parameter I have in the filter, that I replace with the exact name I called the site in sites and subnets.

SELECT [vc].[Guid] FROM [vRM_Computer_Item] AS [vc]
 INNER JOIN [Inv_AeX_AC_TCPIP] AS [tcp] ON [tcp].[_ResourceGuid] = [vc].[Guid]
 INNER JOIN [vSite] AS [vs] ON [vs].Name = '%_sitename%'
 INNER JOIN [vSiteSubnetMap] AS [ss] ON [ss]._ResourceGuid = [vs].[Guid]
 INNER JOIN [Inv_Subnet] AS [sn] ON [sn].[_ResourceGuid] = [ss].[SubnetGuid]
 WHERE
      dbo.[fnIsIPInSubnet2]([tcp].[IP Address],[sn].[Subnet],[sn].[Subnet Mask]) = 1      

How about this?
 

SELECT [vc].[Guid],

vc.Name AS 'Computer Name',

vc2.Name AS 'Task Server Name',

vs.Name AS 'Site Name'

FROM [vComputer] AS [vc]

INNER JOIN [Inv_AeX_AC_TCPIP] AS [tcp] ON [tcp].[_ResourceGuid] = [vc].[Guid]

INNER JOIN [vSite] AS [vs] ON [vs].Name LIKE '%'

INNER JOIN [vSiteSubnetMap] AS [ss] ON [ss]._ResourceGuid = [vs].[Guid]

INNER JOIN [Inv_Subnet] AS [sn] ON [sn].[_ResourceGuid] = [ss].[SubnetGuid]

INNER JOIN [Inv_Client_Task_Resources] ctr on ctr._ResourceGuid = vc.Guid

INNER JOIN vComputer vc2 on vc2.Guid = ctr.ClientTaskServerGuid

WHERE

dbo.[fnIsIPInSubnet2]([tcp].[IP Address],[sn].[Subnet],[sn].[Subnet Mask]) = 1

I'm not good enough at SQL to know whether or not there's too much in this query to get what we want.

Edit:  Actually, I just realized that something is wrong with the joins.  I get duplicate computer names showing up in multiple sites with this query.  I didn't realize it until I sorted differently.

Edit2:  This wasn't incorrect.  I just figured out that I have a couple of subnets that are in more than one site.  One site discretely, one site due to IP scoping.  Oops!

SonicGT, did my previous post give you the info that you were looking for?

No SQL expert but how about

SELECT
   vc.Guid AS ComputerGuid,
   vc.Name AS ComputerName,
   vs.Guid AS SiteGuid,
   vs.Name AS SiteName
FROM vComputer vc
   INNER JOIN ResourceAssociation sc
      ON (sc.ResourceAssociationTypeGuid = '5E8E3C61-A80C-4b0a-A228-DBF97607CEE4' --Computer To Site
         AND sc.ParentResourceGuid = vc.Guid)
INNER JOIN vSite vs
      ON vs.Guid = sc.ChildResourceGuid -- bind Computer to Site
WHERE vc.IsManaged = 1 AND LOWER(vs.Name) LIKE LOWER('%') -- any Site

Thanx High Tower