Query on DB status in controller

[Jubin_Patel]

hi team 

we have active/passive configuration of the db agent for the db collectors in the controller. is there any query where we can find which is active/passive host by running in the controller database and not checking from the controller gui.

below is the ref snap from the database agent setting screen where one is active host and other passive host. 

[Jubin_Patel]

Hello @Xiangning.Mao ,Thanks for the details. It worked as expected. 

[Xiangning_Mao · Accepted solution]

Hello Jubin.Patel,

Thanks for posting question on the community.

Controller determines status of db agents with the same name based on latest start time. 

Hence we could fetch last agent start time from controller db and then judge which one is active / passive.

Please access controller and use the query command below: (You need to use real account name in this SQL):

select acn.name as nodeName, ag.type as agentType, ag.agent_version as agentVersion, from_unixtime(ag.last_agent_start_timestamp/1000) as lastAgentStartTime from application app inner join application_component ac on ac.application_id = app.id inner join application_component_node acn on acn.application_component_id = ac.id inner join application_component_node_agent_mapping acnm on acnm.application_component_node_id=acn.id inner join agent ag on ag.id=acnm.agent_id inner join account a on a.id = app.account_id where a.name='<account name>' and ag.type = 'DB_AGENT';

(E.g.)

mysql> select acn.name as nodeName, ag.type as agentType, ag.agent_version as agentVersion, from_unixtime(ag.last_agent_start_timestamp/1000) as lastAgentStartTime from application app inner join application_component ac on ac.application_id = app.id inner join application_component_node acn on acn.application_component_id = ac.id inner join application_component_node_agent_mapping acnm on acnm.application_component_node_id=acn.id inner join agent ag on ag.id=acnm.agent_id inner join account a on a.id = app.account_id where a.name='xxxxxxxx' and ag.type = 'DB_AGENT';
+---------------------------------------------+-----------+-------------------------------------------------------------------------------+--------------------------+
| nodeName                                    | agentType | agentVersion                                                                  | lastAgentStartTime       |
+---------------------------------------------+-----------+-------------------------------------------------------------------------------+--------------------------+
| cDBAgent_Mao|host:cDBAgent-Secondary-Mao    | DB_AGENT  | Database Agent v24.5.0.4126 GA compatible with 4.5.2.0 Build Date  2024-05-15 | 2024-07-16 05:48:48.8930 |
| cDBAgent_Mao|host:cDBAgent-Primary-Mao      | DB_AGENT  | Database Agent v24.5.0.4126 GA compatible with 4.5.2.0 Build Date  2024-05-15 | 2024-07-16 05:48:48.8890 |
+---------------------------------------------+-----------+-------------------------------------------------------------------------------+--------------------------+
2 rows in set (0.00 sec)

 

Hope this helps.

Best regards,

Xiangning