Query to identify the agent groups an agent belongs to

Products

• CA Release Automation - Release Operations Center (Nolio)

Issue/Introduction

Deleting decommissioned servers from ROC UI - Agent Management tab failed with Unexpected error on server 
[Unable to perform beforeTransactionCompletion].

The log shows error to insert into server_category_aud.

nolio_dm_all.log:
==============
...
[https-jsse-nio-8443-exec-25] ERROR (org.hibernate.engine.jdbc.batch.internal.BatchingBatch:134) 
- HHH000315: Exception executing batch [java.sql.BatchUpdateException: Cannot insert the value NULL into column 'id', 
table 'NOLIO.dbo.server_category_aud'; column does not allow nulls. INSERT fails.], SQL: /* insert server_category_aud */ 
insert into server_category_aud (REVTYPE, REVEND, REV, server_id, category_id) values (?, ?, ?, ?, ?)
[https-jsse-nio-8443-exec-25] WARN  (org.hibernate.engine.jdbc.spi.SqlExceptionHelper:137) - SQL Error: 515, SQLState: 23000
...

Environment

CA Release Automation - 6.8

Cause

The agent in question currently belonging to an Agent Group(s) is the most likely cause of this error.

Resolution

The fix for this issue is included in the current available cumulative patch 6.8.4:

Nolio Release Automation 6.8 > Release Notes > Patch Release > Release Notes for 6.8.4 > Fixed Issues

33674753 Unable to remove agent with auditing enabled

In case the cumulative patch 6.8.4 still not applied, the following steps will allow remove for the majority 
of clients that encounter this issue.

1. Login to ROC.
2. Identify the agent group(s) that the agent belongs to.
  - Via Administration -> Agent Groups
  - Go through each group (on the left) and search for the server in question. 
  - Select the next Group "Type" from the group "Type" drop down that has "Default" selected by default.
  - Go through each group (on the left) and search for the server in question. 
3. Remove the agent from the Agent Group(s).

Unable to Delete Agent - You cannot delete a server that is being used by Release Center artifacts.

 

As searching the GUI manually may take a substantial amount of time, rather than individually check inside 
each of all Agent Groups in the GUI for each of the agents, try a query in order to identify the Agent Groups 
an agent belongs to.

There are 3 tables useful in this particular case:

1. servers - list of all the agents with their properties.
2. categories - list of agent categories.
3. server_category - table to link above 2 entities.
 
Example query:

select * 
 from servers s
 inner join server_category sc on sc.server_id = s.id
 inner join categories c on c.id = sc.category_id

This query gives all the agents, assigned to groups with these groups details.