Community Articles

Find and share helpful community-sourced technical articles.

This is a short how-to leveraging Zeppelin and Solr's native SQL capabilities to query the Ranger audit logs in real time. The capability to query Ranger audits has been in existence for quite a while and there are multiple articles available demonstrating how to apply a Hive external table on top of Ranger audits stored in HDFS. This article demonstrates how to leverage Zeppelin and Solr SQL to query Solr in real time without additional step of creating an external table on top of the HDFS audit.

First thing you need is access to your Solr instance. I'm using the default instance packaged with Ambari infra. The Solr admin UI is available at the following address: http://{ambari-infra-ip}:8886. In the UI, you can issue arbitrary queries using standard Solr syntax. I am new to Solr and found the query syntax cumbersome. Instead, I decided to leverage Solr SQL, available as of version 6. HDP 3.0 ships with Solr 7.3.

The next step is to set up Zeppelin interpreter for Solr via JDBC. Steps for doing that are available on the Solr website and I'm going to summarize the minimum required configuration for HDP 3. Feel free to copy and modify the properties below:

default.driver :
default.url : jdbc:solr://{ambari-infra-ip}:2181/infra-solr?collection=ranger_audits
default.user: solr

In the artifacts section, add the following entry


Be mindful of the port for Zookeeper quorum for the Ranger Solr collection. I found the information browsing the ZK CLI shell.

[zk: localhost:2181(CONNECTED) 4] ls /infra-solr/collections/ranger_audits

So once you enter that information into Zeppelin interpreter, you can now use the %solr command to browse Ranger audits with SQL, just add a new note with Solr interpreter selected.


Notice I am using all of the standard fields in Ranger audit, you can find an older version of the schema at the following link. I say older because in HDP 3, Ranger supports multiple clusters and additional fields identifying separate clusters are available, by I digress. The query above will show all current events where result, i.e. access is denied. This is really convenient because you don't need to apply schema and data is available in real time. You can build powerful reporting capabilities on top of what is available in Ranger Admin UI, (in case your question was why even doing that if that info was available already via Ranger).

Finally, once you press execute, the data will be shown below:


You can now add more plotting libraries and built-in Zeppelin charting capabilities to make very powerful dashboards!

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.
Version history
Last update:
‎08-17-2019 06:23 AM
Updated by:
Top Kudoed Authors