PcAnywhere distribution report with host ip address included

periicles · 06-19-2009, 03:57 AM

Hi everyone,

I need to create a report for Pcanywhere installed in our pc environment, the current defaul report specify host name, but what I need is to include the ip address of the host too....

environment: altiris v7

this is a copy of the default sql

...........start script................

SELECT DISTINCT
i._ResourceGuid [Guid],
i.Name [Host Machine (Target)],
ISNULL(p.[pcA_Version_Major],'') +
CASE WHEN ISNULL(p.[pcA_Version_Minor],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Minor] END +
CASE WHEN ISNULL(p.[pcA_Version_Patch],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Patch] END +
CASE WHEN ISNULL(p.[pcA_Version_Build_Number],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Build_Number] END AS [pcAnywhere Host],
i.[OS Name] [OS]
FROM dbo.Inv_Symantec_pcAnywhere_Host_State hs
JOIN Inv_Symantec_pcAnywhere_Product_Info p
ON p._ResourceGuid = hs._ResourceGuid
JOIN Inv_AeX_AC_Identification i
ON i._ResourceGuid=p._ResourceGuid
JOIN CollectionMembership cm
ON i._ResourceGuid = cm.ResourceGuid
JOIN vCollection c
ON cm.CollectionGuid = c.Guid
WHERE upper(c.Guid) LIKE upper('%Collection%')
ORDER BY i.Name,
ISNULL(p.[pcA_Version_Major],'') +
CASE WHEN ISNULL(p.[pcA_Version_Minor],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Minor] END +
CASE WHEN ISNULL(p.[pcA_Version_Patch],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Patch] END +
CASE WHEN ISNULL(p.[pcA_Version_Build_Number],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Build_Number] END,
i.[OS Name]

...........end of report...........

If you know how to add extra lines to include the ip address on this report, I will really appreciate your help

Thanks

Nick · 06-19-2009, 08:36 AM

I'm going off memory and didn't have a database to confirm but the dbo.Inv_AeX_Ac_TCPIP table should have what you need.

I made the changes (noted in red) to your report. It hasn't been tested though.

Hope it helps

Code:

SELECT DISTINCT 
i._ResourceGuid [Guid],
i.Name [Host Machine (Target)],
z.[IP Address],
ISNULL(p.[pcA_Version_Major],'') + 
CASE WHEN ISNULL(p.[pcA_Version_Minor],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Minor] END +
CASE WHEN ISNULL(p.[pcA_Version_Patch],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Patch] END +
CASE WHEN ISNULL(p.[pcA_Version_Build_Number],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Build_Number] END AS [pcAnywhere Host],
i.[OS Name] [OS]
FROM dbo.Inv_Symantec_pcAnywhere_Host_State hs
JOIN Inv_Symantec_pcAnywhere_Product_Info p
ON p._ResourceGuid = hs._ResourceGuid
JOIN Inv_AeX_AC_Identification i
ON i._ResourceGuid=p._ResourceGuid
JOIN Inv_AeX_AC_TCPIP z
ON z.[IP Address] = i._ResourceGuid
JOIN CollectionMembership cm
ON i._ResourceGuid = cm.ResourceGuid
JOIN vCollection c
ON cm.CollectionGuid = c.Guid
WHERE upper(c.Guid) LIKE upper('%Collection%')
ORDER BY i.Name,
ISNULL(p.[pcA_Version_Major],'') + 
CASE WHEN ISNULL(p.[pcA_Version_Minor],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Minor] END +
CASE WHEN ISNULL(p.[pcA_Version_Patch],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Patch] END +
CASE WHEN ISNULL(p.[pcA_Version_Build_Number],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Build_Number] END,
i.[OS Name]

periicles · 06-21-2009, 03:35 AM

Nick, I tested those entries, but the report still fail, the comment said "An unexpected SQL error occurred when running the RawSqlDataSource"

Sorry I did not know what else to check, I read some log generated but not much info for me to use at this time....

If there some info I can post for you to have an idea which way to approach it...

TRYDL · 06-21-2009, 09:51 PM

Code:

 
JOIN Inv_AeX_AC_TCPIP z
ON z.[IP Address] = i._ResourceGuid

can't join on that, an IP address wont match to an object GUID

try

on z._ResourceGuid = i._ResourceGuid

If that air lookup doesnt work, I'll throw it into SSMS and write it up for you.

periicles · 06-21-2009, 10:42 PM

I tried ON z._ResourceGuid = i._ResourceGuid and that did the trick, fantastic

Thank you for you help, Trydl and Nick, much appreciated

By the way Trydl, I know is not under same script query, but may I ask you where o how to get sample to create a raw queries to collect group of machines base on name (from AD or workgroup) to populate a new filter..I posted a question some time ago but I have not have much luck with respond to that......

Again thank you very much for your help....

Nick · 06-22-2009, 10:11 AM

<laugh> Wow, where was my thought process when I was writing that? Thanks for pointing out my error and correcting it.

Quote:

Originally Posted by TRYDL

Code:

 
JOIN Inv_AeX_AC_TCPIP z
ON z.[IP Address] = i._ResourceGuid

can't join on that, an IP address wont match to an object GUID

try

on z._ResourceGuid = i._ResourceGuid

If that air lookup doesnt work, I'll throw it into SSMS and write it up for you.

TRYDL · 06-23-2009, 10:16 PM

Quote:

sample to create a raw queries to collect group of machines base on name (from AD or workgroup) to populate a new filter

If I understood you correctly, you want to create groups of computers by their NetBIOS name?

Something like

Code:

Select Guid from vComputer where Name LIKE 'ZoneA-%'

will be sufficient.

The % is a wildcard in SQL.

Nick - no worries

periicles · 07-02-2009, 05:46 AM

Hi Trydl, before I continue I would like to thanks you for your help

Regarding to the filter, that is correct I need to populate a filter with machine base on the NetBIOS name....

I tried the "Code" you mentioned and it worked good to get machine names....If I want to import machine from AD to filter, can I include a code here!!!!!! or better do it different way...

.................................

By the way, I would like to ask you one more question (sorry) about the code for Ip address. I wanted to include the IP information in different queries??? this time for a report for machines with altiris agent (see sample) but it did not work...sorry still learning.....how to include the variable for this query....

................start of sample............................
DECLARE @v1_TrusteeScope varchar(99)
SET @v1_TrusteeScope = '%TrusteeScope%'
IF %ResultType% = 1
SELECT DISTINCT TOP %N% vComputer.Guid AS '_ItemGuid', vComputer.Name, vComputer.Domain, vComputer.[OS Name], vComputer.Guid AS [Machine Guid], vComputer.Server
FROM Inv_AeX_AC_Client_Agent
INNER JOIN vComputer ON vComputer.Guid = Inv_AeX_AC_Client_Agent._ResourceGuid
INNER JOIN [fnGetTrusteeScopedResources](@v1_TrusteeScope) AS [fnGTSR_3] ON [vComputer].[Guid] = [fnGTSR_3].[ResourceGuid]
WHERE (vComputer.Domain LIKE '%Domain%') AND (vComputer.[OS Name] LIKE '%OSName%') AND (vComputer.Name LIKE '%Name%') AND
(UPPER(Inv_AeX_AC_Client_Agent.[Agent Name]) IN ('ALTIRIS EXPRESS NS CLIENT', 'ALTIRIS AGENT')) AND (vComputer.Guid IN
(SELECT vComputer.Guid
FROM Inv_AeX_AC_Client_Agent
INNER JOIN vComputer ON vComputer.Guid = Inv_AeX_AC_Client_Agent._ResourceGuid
WHERE (UPPER(Inv_AeX_AC_Client_Agent.[Agent Name]) IN ('ALTIRIS EXPRESS ACLIENT', 'DEPLOYMENT AGENT FOR WINDOWS (ACLIENT)'))))
............end of sample...........

Thanks in advance if you have the opportunity to reply....( In the meantime I am waiting for my boss to send me to some training for these sort of sql for altiris too....I hope soon)