add ip information in altiris agent report (raw sql)

This issue has been solved. See solution.
pragmmativco's picture
pragmmativco
July 3rd, 2009

Hi there,

One of he company managers requested the list of machines with altiris agent installed, which include IP information, I checked the sql for the current agent report (NS 7), but I do not how or what variables to enter in order to get ip in the report, any help would be appreciated, below is a sample of the current query

......................................................................................................
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

Filed under: , ,
0 votes
jz81's picture
jz81
17 weeks 6 days ago

ip address in vcomputer view

Hello pragmmativco,

As far as I remember the vcomputer view you reference has a column 'ip address' so the following query should do the trick:
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.[IP Address], 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)'))))

Good luck,
John

0 votes
pragmmativco's picture
pragmmativco
17 weeks 6 days ago

vcomputer entry

Hi Jz81,

I tried that option and it did not work, this is what I tried so far, but still no luck.....

.....................................
IF %ResultType% = 1
SELECT DISTINCT TOP %N% vComputer.Guid AS '_ItemGuid', vComputer.Name, vComputer.Domain, vComputer.[IP Address], 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.[IP Address] LIKE '%IPAddress%') 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
............................................

I am not sure what else to try, thanks for your help in advance

0 votes
TheSmiz's picture
TheSmiz
17 weeks 5 days ago

Create New Report or Use Query Analyzer/SQL Svr Mgmt Studio

Solution

I would either simply create a new report and title it something like "Managed Windows Computers with Altiris Agent Installed" and copy/paste the following query in the parametrized query field.  You can also simply run this query from query analyzer or SQL server management studio.  You can add ORDER BY clauses as you see fit.

SELECT DISTINCT vc.Guid AS '_ItemGuid',
vc.Name,
vc.Domain,
vc.[IP Address],
vc.[OS Name],
vc.Guid AS [Machine Guid],
vc.Server
FROM Inv_AeX_AC_Client_Agent ca
INNER JOIN vComputer vc ON vc.Guid = ca._ResourceGuid
WHERE vc.[OS Name] LIKE '%Windows%'
AND vc.IsManaged = 1
AND (UPPER(ca.[Agent Name]) IN ('ALTIRIS EXPRESS NS CLIENT', 'ALTIRIS AGENT'))

Need anything further, let us know.

+1 (1 vote)
SOLUTION
pragmmativco's picture
pragmmativco
17 weeks 5 days ago

New report

Exactly what I needed
 
I created the new query with the above input and saved it successfully with the expected results...I was trying to guess input and variables the last couple of days...but with your help I getting there step by steps with sql queries...

Thanks you very much for your help "The Smiz" and "jz81"

 

0 votes