Extract data from Data Base on the Controller.

[CommunityUser]

Hi all,

i´m consulting in the mysql database on the controller, and i want to know the actions executed by the users, and the application where the users made some action.

For example:

select user_name,FROM_UNIXTIME(ts_ms/1000, '%Y %D %M %h:%i:%s') as login_time,action from controller_audit where user_name='Name_of_user';

The result of this is:

| Name_of_user | 2015 20th May 04:17:43 | APP_EMAIL_DIGESTS |
|Name_of_user | 2015 20th May 04:18:31 | OBJECT_DELETED |
| Name_of_user| 2015 20th May 04:18:34 | APP_EMAIL_DIGESTS |
| Name_of_user | 2015 20th May 05:00:22 | LOGIN |
| Name_of_user | 2015 20th May 05:00:49 | APP_CONFIGURATION |
| Name_of_user | 2015 20th May 05:01:00 | OBJECT_UPDATED |
| Name_of_user | 2015 20th May 05:01:06 | OBJECT_UPDATED |
| Name_of_user| 2015 20th May 05:09:55 | APP_EUM_DASHBOARD |
| Name_of_user | 2015 20th May 05:13:27 | APP_CONFIGURATION |
+-----------+--------------------------+---------------------------+

As you can see, this shows the list of actions of one particular user (with the information of controller_audit table), but don't shows the application name where the action was made.

Anybody knows how can i get this data?

Thank and regards!

PJ.

[Arun_Dasetty]

Hi ,

We do "not" see direct way here as the object name say POLICY, RULE, APPLICATION not always represents a table name and nor the table audit have constraint references for object id, we might need try as below for pattern of objects :

Say the object name modified is of type (object type) policy you can run below command:

mysql> select application_id from controller.policy where id=(select object_id from controller.controller_audit where object_name like '%POLICY% and action like '%OBJECT_DELETED%);

Please change the column in refernce to actually column name on audit and policy table in above example. Check if that information helps.

Regards,

Arun