Smarts NCM: How to query the NCM database for the users created in the application

Products

• VMware Smart Assurance

Environment

VMware Smart Assurance - NCM

Resolution

This article explains how to query the Smarts NCM/Voyence DB for users created in the application



The following sections show the query syntax and examples for retrieving all information on all users created in the Smarts NCM application database, the number of users created, and the number of users excluding the four default users.

Retrieving all users
Use the following database query to retrieve the users created in Smarts NCM from the application database:

select * from cm_user;
 

Example:

voyencedb=# select * from cm_user;
user_id | user_name | user_profile_id | enabled | password
| login_attempts_remaining | last_login_attempt | authentication_method | favorites_folder_id | authentication_realm | version | security_profile
---------------------------------------------------------+---------------+------------------------------------------------------+---------+------------------
--------+--------------------------+-------------------------+-----------------------+---------------------------------------------------------+-------------
---------+---------+------------------
\012\037\211\215r\354\332?\016\030u1>\001\000\000 | system | \012\037\211\215t\002\327?\016\030u1>\001\000\000 | t | YXiu4l3wrVUVJ9+5C
fewMA== | 3 | 2013-04-22 16:45:57.581 | DBAuthentication | \012\037\211\215s\006\311?\016\030u1>\001\000\000 | | 1 |

\012\037\211\215u\007\332?\024\030u1>\001\000\000 | controlDaemon | \012\037\211\215w\263\344?\025\030u1>\001\000\000 | t | SJhnOncFvDFeLpNpr
CA8yg== | 3 | 2013-04-22 16:45:57.588 | DBAuthentication | \012\037\211\215vg\346?\024\030u1>\001\000\000 | | 1 |

\012\037\211\215x\215\344?\037\030u1>\001\000\000 | int_mod_user | \012\037\211\215z\343\273? \030u1>\001\000\000 | t | 0P7g3IhJR130EAWH1
gnm+g== | 3 | 2013-04-22 16:45:57.599 | DBAuthentication | \012\037\211\215y\304\342?\037\030u1>\001\000\000 | | 1 |

\012\037\211\215|9\326?2\366zZ>\001\000\000 | test123 | \012\037\211\215~%\350?3\366zZ>\001\000\000 | t | rwMt1Fv19DdIZmtA7
6BfKw== | 3 | | TACACSAuthentication | \012\037\211\215}\212\331?3\366zZ>\001\000\000 | | 1 |

\012\037\211\215\251\355\353?\353\006\245.@\001\000\000 | testing | \012\037\211\215\253F\306?\353\006\245.@\001\000\000 | t | risfylFZSeXVT7Irj
tlVdQ== | 3 | 2013-07-30 15:44:36.505 | DBAuthentication | \012\037\211\215\252\272\335?\353\006\245.@\001\000\000 | | 13 |

\012\037\211\215op\336?\327\027u1>\001\000\000 | sysadmin | \012\037\211\215q]\345?\332\027u1>\001\000\000 | t | SKNltM4eMipVrpAX8
9rwwA== | 3 | 2013-08-02 18:22:45.032 | DBAuthentication | \012\037\211\215p\025\327?\331\027u1>\001\000\000 | | 47 |

(6 rows)
voyencedb=#

Total number of users
To retrieve the total number of users in the Smarts NCM database, run the following query:

select count(*) from cm_user;
 

Example
voyencedb=# select count(*) from cm_user;
count
-------
6
(1 row)

Total number of users excluding default users
To retrieve the number of users created in the Smarts NCM database excluding the default 4 users (system, controlDaemon, int_mod_user and sysadmin), run the following query:

select (select count(*) from cm_user)-4;
 

Example
voyencedb=# select (select count(*) from cm_user)-4;
?column?
----------
2
(1 row)