Deepak,
I think, in that case, the system overview should provide you that data in the optimal way.
While the information if a Agent is active could (!!) be found within the
HOST Table (HOST_ACTIVE),
the status of CP and WP Processes can not be determined that easily via the database, since these processes do report into the MQ Tables and they are shared from all active WP and CP Processes (except MQCP Tables but i really dont recommend to touch them without order from technical support to do so regarding to an active incident)
if you really want to determine if an agent is active or not via DB, you can ping the HOST Table, but the recommended and more comfortable way would be to do that via the system overview or with the regarding script command (
SYS_HOST_ALIVE)