How do I determine all installed agents and versions for managed computers on the Notification Server?
Answer
Import the attached report (All Installed Agents And Versions.xml) XML to your Notification Server console. The attached report includes a collection picker.
Note: NULL or Blank return indicates agent not installed.
Caution: The report is process intensive and may take an extended period of time to return results.
Alternately, create a report or view using the SQL included below.
-- Begin SQL
IF EXISTS(SELECT name FROM dbo.sysobjects WHERE name = N'_ReportTempAgents' AND xtype='U')
DROP TABLE _ReportTempAgents
create table _ReportTempAgents ([Computer Name] varchar(64) PRIMARY KEY ([Computer Name]))
declare @@column varchar(50)declare rmcursor1 cursor for
SELECT DISTINCT [Agent Name]
FROM Inv_AeX_AC_Client_Agent
ORDER BY [Agent Name]open rmcursor1
fetch next from rmcursor1 into @@column
while (@@fetch_status = 0) begin
-- Adding in extra columns for the agent names
execute('ALTER TABLE _ReportTempAgents ADD [' + @@column + '] varchar(32)')
fetch next from rmcursor1 into @@column
end
close rmcursor1
deallocate rmcursor1-- Inserting key computer name
insert _ReportTempAgents([Computer Name])
SELECT DISTINCT vcomputer.[Name]
from vcomputer where vcomputer.ismanaged = 1
godeclare @@agent nvarchar(50)
declare @@agentname nvarchar(100)
declare @@aversion nvarchar(32)
declare @@cname nvarchar(50)
declare @@command nvarchar(400)
declare @@ninst nvarchar(13)declare rmcursor cursor for
select distinct [Computer Name]
from _ReportTempAgentsopen rmcursor
fetch next from rmcursor into @@cname
while (@@fetch_status = 0) begin
declare rmcursor2 cursor for
select distinct ca.[Agent Name]
from Inv_AeX_AC_Client_Agent ca
join vcomputer vc on ca.[_resourceguid] = vc.[GUID]
where vc.[name] = @@cname
and ca.[Product Version] IS NOT NULLopen rmcursor2
fetch next from rmcursor2 into @@agent
while (@@fetch_status = 0) begin
set @@agentname = @@agent
set @@aversion = (
select top 1 [Product Version]
from vcomputer vc
join Inv_AeX_AC_Client_Agent on vc.[GUID] = Inv_AeX_AC_Client_Agent.[_ResourceGUID]
where vc.[name] like @@cname and [Agent Name] like @@agent)
set @@command = 'Update dbo._ReportTempAgents SET [' + @@agentname + '] = ' +
'''' + @@aversion + '''' + ' where [computer name] like ''' + @@cname + ''''
-- Update the version to its agant name using the computer name as a key
execute (@@command)
fetch next from rmcursor2 into @@agent
endclose rmcursor2
deallocate rmcursor2
fetch next from rmcursor into @@cname
endclose rmcursor
deallocate rmcursorselect * from _ReportTempAgents
drop table _ReportTempAgents
--END SQL