AppD Archive

Extract data from Data Base on the Controller.

CommunityUser
Splunk Employee
Splunk Employee

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.

0 Karma

Arun_Dasetty
Super Champion

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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...