How to report all installed agents and versions per managed computer

Products

• Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Question

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
      go

declare @@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 _ReportTempAgents

open 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 NULL

      open 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
      end

      close rmcursor2
      deallocate rmcursor2
      fetch next from rmcursor into @@cname
end

close rmcursor
deallocate rmcursor

select * from _ReportTempAgents

drop table _ReportTempAgents

--END SQL