Querying Live Guardium Data with Cognos (Without the CSV Exports)

by John Haldeman, Security Practice Lead

This post is all about how to configure Cognos to query Guardium DAM data directly on the appliance. That is without exporting the data to CSV first and then loading it into a database that Cognos can access. How it works is by using a web service that accesses the Guardium REST API and then exposes the resulting Guardium data in an XML format that Cognos accepts. Cognos queries the web service and displays the data.

Architecture for Querying Guardium Data Directly from Cognos

At the following link you will find code to a servlet that can be used to query a Guardium appliance (normally a central manager or standalone collector) and run any report on that appliance:
          https://github.com/johnhaldeman/guardiumReportWrapperForCognos

Using the Web Service

Before we go into details on how to deploy and configure the web service, let's look at how to use it. Once deployed, the web service is designed to query the Guardium appliance and then expose the resulting JSON report data in an XML format acceptable to Cognos. When you access the web service you just specify the report name and the required parameters as HTTP GET or POST parameters. For example, the built in "Sessions List" report returns the list of database sessions that Guardium has captured and requires four parameters to run:

• QUERY_FROM_DATE (set to NOW -1 WEEK for example)
  
• QUERY_TO_DATE (set to NOW for example)
  
• SHOW_ALIASES (set to TRUE for example)
• REMOTE_SOURCE (set to % for example to run locally)
  

Being careful to escape the HTTP parameters, you would call the web service like this:
http://<service_ip>:<service_port>/GuardiumJSONtoXML/xmlReport?reportName=Sessions+List&QUERY_FROM_DATE=NOW+-1+WEEK&QUERY_TO_DATE=NOW&SHOW_ALIASES=TRUE&REMOTE_SOURCE=%25

Note that this is a general implementation. You can run any report and if that report takes a different set of parameters, you just specify those as additional HTTP GET or POST parameters. The web service should be able to run any report in Guardium if you feed it the right parameter set. After execution, the servlet then returns the data in the general Cognos XML format described in the developerWorks article here.

Example execution and results of the "Sessions List" report using the web service

Using the same developerWorks article, you then configure a Cognos XML datasource and perform some minor data modeling using the Cognos Framework Manager (starting at step 10 here). You can then make Cognos aware of the same report parameters we talked about earlier (ie: QUERY_FROM_DATE and the like) using the instructions at the support document here.

The end result is Cognos requesting the required parameters on report execution, executing the report, and then displaying the data in Cognos without having to perform and data export from Guardium.

Cognos Requesting Guardium Report Parameters

Sessions List report running in Cognos - Retrieving live Guardium data

At this point you can use Cognos' other capabilities to full advantage like developing better report parameter pages that assist users in entering appropriate data for Guardium parameters, and creating graphs and other visualizations.

Configuring the Web Service

Once compiled, you can deploy the servlet to your favorite application server. A natural place might be the Tomcat or WebSphere instance that your Cognos instance runs on, but it could be anywhere. Then, before you can use the web service, you need to modify the servlet's deployment descriptor (ie: the web.xml file) where you specify some REST API parameters that you will need in order to run the REST calls on the Guardium appliance. This includes the REST API client secret, a GUI username, and password. For details on how to register a REST client and create a client secret, take a look at the developerWorks article on the REST API. You can modify the servlet context parameters already included in the sample web.xml file in the github repository.

The web.xml file with the context parameters filled in for our lab environment

Some Performance and Security Considerations

I will end this post with some notes and caveats. By far the most powerful and flexible method for using Guardium data outside of the Guardium enviornment is to export the data to CSV and then loading it into a database. Many customers do this and it works quite well. The discussion above should bare in mind the implications to the flexibility of the data you are querying, particularly when it comes to writing custom queries in Cognos on top of these data sources. The best way to use this method as is a alternative view on reports that are already built in Guardium, rather than a new datasource that you should build joins an complex queries on top of. For best results, do the report building in Guardium and just the viewing in Cognos. This helps push the computation down to where the data is.

The other consideration that must be made is around security. The servlet described in this post allows anyone with access to that servlet on the network to query Guardium data - with no authentication. There are various ways to restrict access which I might explore in other posts. If you have any specific questions or a need to implement this in a production environment, please feel free to contact me at john.haldeman[AT]infoinsightsllc.com.