Smarts NCM : How to fetch the list of users associated with a specific network by accessing the database?

Products

• VMware Smart Assurance

Environment

VMware Smart Assurance - NCM

Resolution

To fetch the list of users associated with a specific network, perform the following steps-

1. Login to the NCM DB (Regardless of combination or distributed setup)-

Navigate to $VOYENCE_HOME/db/controldb. Login to database with su - pgdba privilege.

su - pgdba
-bash-3.2$ psql voyencedb voyence
Password for user voyence: (Enter the password of database set at the time of installation)
voyencedb=#

2. Run the below sql query to fetch the list of users-

SELECT user_name from cm_user where user_id IN (SELECT user_id FROM cm_acl_entry WHERE network_id = (SELECT network_id FROM cm_network where network_name='Network Name'));

Additional Information

NOTE: Result of the query should be something like-

voyencedb=# SELECT user_name from cm_user where user_id IN (SELECT user_id FROM cm_acl_entry WHERE network_id = (SELECT network_id FROM cm_network where network_name='Mimic'));
user_name
-----------
sysadmin
Test1
(2 rows)
voyencedb=#

Here, users sysadmin and Test1 are the only two users associated with the network "Mimic".