Ranger Audit Custom reports

[#1]

one of our customers would like to pull ranger audit information to external db source and use tableau to generate some reports.

What do we suggest to our customers when it comes to best practices in this area? do we have sample queries where we can pull the information we are showing on the Ranger UI? or we recommend them to use Rest APIs for audit data pull? If REST is preferable, do we have a list of REST API calls/samples for me to get started? please suggest

Thanks,

[#2]

@rbalam@hortonworks.com

If you are using DB option for Ranger audit then you should be able to run queries against audit tables.

Mysql:

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| hive |

| mysql |

| ranger |

| ranger_audit |

| test |

+--------------------+

6 rows in set (0.00 sec)

mysql> use ranger_audit

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+------------------------+

| Tables_in_ranger_audit |

+------------------------+

| xa_access_audit |

+------------------------+

1 row in set (0.00 sec)

mysql>

[#3 · Accepted solution]

It sounds like you want to pull out a lot of data, so I would definitely not use Ranger's RestAPI (not even sure Ranger allows audit export via RestAPI). If you are using DB Audits, which we do not recommend in production systems, you can connect Tableau directly to your DB (check the supported drivers => http://www.tableau.com/support/drivers)

Personally I'd go with one of the following solutions:

A) Enable HDFS Audit Logs (good idea in general), put a Hive table on top of the audit logs and use Tableau's Hive connector to retrieve and visualize/analyze the data. (Check this out => http://kb.tableau.com/articles/knowledgebase/hadoop-hive-connection)

B) If you are using the SolrCloud you could query the Solr index and export the relevant data into a data format that is supported by Tableau (this could be done by Nifi, etc.) . Unfortunately there is no Tableau Solr Driver yet as far as I know.

[#4]

Whenever I execute this query : sqoop list-databases --connect jdbc:mysql://localhost:3306 --username xxx -password xxx . Only the following tables are displayed information_schema, hive, mysql, ranger, ranger_audit,test but not the databases which I created inside mysql workbench. Your guidance will be appreciated.