Query to fetch records of agents mapped in Application/s

Products

• CA Release Automation - Release Operations Center (Nolio)

Issue/Introduction

We need a query to detect all agents mapped to the applications along with there status.

Environment

Release : 6.7

Component : CA RELEASE AUTOMATION DATABASE

Resolution

Query to get mapped agents in an application.

Note: The query below will not list applications which doesn't have any server(agents) mapped

• Query with application filter (replace the filter %A% with respective application name filters)

select
  A.ID APPID,
  A.APP_NAME APPNAME,
  E.ID ENVID,
  E.NAME ENVNAME,
  STI.MAPPED_SERVER AGENTID,
  S.SERVER_NAME AGENTNAME,
  S.AGENT_VERSION,
  S.SERVER_REACHABLE
from ENVIRONMENTS E
  full outer join APPLICATIONS A
    on E.APPLICATIONID = A.ID
  full outer join SERVER_TYPE_INSTANCE STI
    on E.ID = STI.ENVIRONMENT_ID
  join SERVERS S
    on STI.MAPPED_SERVER = S.ID
where A.APP_NAME like '%A%'

• Query without application filter

select
  A.ID APPID,
  A.APP_NAME APPNAME,
  E.ID ENVID,
  E.NAME ENVNAME,
  STI.MAPPED_SERVER AGENTID,
  S.SERVER_NAME AGENTNAME,
  S.AGENT_VERSION,
  S.SERVER_REACHABLE
from ENVIRONMENTS E
  full outer join APPLICATIONS A
    on E.APPLICATIONID = A.ID
  full outer join SERVER_TYPE_INSTANCE STI
    on E.ID = STI.ENVIRONMENT_ID
  join SERVERS S
    on STI.MAPPED_SERVER = S.ID