Data Queries
Modifying the Report Query
This section assumes that you have already created a new report, or are editing an existing report. If you haven't yet created the report you would like to edit, review the steps in the Overview for more information.
To modify the report query:
- Expand your report category, then name, then platform. You should see a report definition with a version number, like 2011.1.
- Right-click on that version number and select Edit Report. This will invoke iReport.
- If prompted, you may need to point Eclipse to your installation of iReport - browse to the directory where you unpacked iReport.
- You can optionally create a connection to a live Sentinel system if you want to preview your reports in iReport
- In the Report Inspector pane at left, right click on the top-level TemplateReport element and select Edit Query.
- Note that many reports are implemented as a bunch of subreports. You may need to right-click each subreport in turn (indicated by grey-shaded boxes in the Designer pane), select Open Subreport, and then when the subreport is loaded into the Report Inspector edit its query.
- Edit the query as desired.
You may find it useful to use tools like SQL Developer and the Sentinel Search UI to test out your queries before you attempt to author them in iReport. If you have an active Connection to a Sentinel system set up, you can use the Read Fields button in the query editor (for SQL only, today) to test the query, but more extensive testing is available in third-party tools.
It's also very helpful to set up report previewing in iReport so you do not have to build/deploy/run each report edit to see what you've changed.
SQL Queries
Sentinel's backend database for Sentinel RD 6.1, Sentinel Log Manager, and Sentinel 7 is based on PostgreSQL. For Sentinel RD, the database contains configuration and event data; for the other platforms only configuration data.
In general, all SQL queries that you will put into reports will be of the form:
- SELECT fields FROM tables WHERE filter criteria [ ORDER BY ] fields
- fields
- The list of fields that can be selected can be found in the Database Schema documentation. It is also quite common to use CASE statements and other server-side manipulation of this data as part of the query.
- tables
- The available tables is also defined in the Database Schema documentation, but note that in Sentinel 7 a Report Data Definition (RDD) can also create its own tables. More on this below.
- filter criteria
- This is where you'd specify, say, to filter for events where a field contains some specific piece of data, like a particular taxonomic class.
For Sentinel 7 only, a Report Data Definition will create a new database table and optionally one or more views, and will populate those tables/views with event data. Since the table/view names are automatically generated, you won't know what they are in advance. Instead, you will use a special parameter that will resolve at runtime into the correct table name. The way to set this up is as follows:
- Identify an existing or create a new RDD that will populate the data you need.
- Within that RDD, there is an XML file that describes the configuration. Open that XML and examine its contents.
- There should be an entry like /EventDataSyncInfo/SyncLuceneToTable/ReferenceName. The ReferenceName is what you'll need to refer to the table.
- Similarly, there should be one or more entries for /EventDataSyncInfo/SyncTableToView/ReferenceName, again you'll need the ReferenceName to address the view.
- When you want to query the table/view created by the RDD, add a parameter reference into the SELECT ... FROM clause to refer to that table as $P!{ReferenceName}
- For example, an RDD view named AuthDataSyncView would be queried like this: SELECT iun FROM $P!{AuthDataSyncView}
Lucene Queries
Sentinel Log Manager and Sentinel 7 store event data in indexed files that can be searched using Lucene-style queries. This is the same query language used directly in the Sentinel UI, and documented for Sentinel 7. You will need to be familiar with the basic Lucene query syntax to construct the correct report queries, but note that you can literally copy and paste queries from the Sentinel UI into the report.
The search query is not all, however. Unlike the Sentinel UI, which shows all details of all events, the report only shows some fields, and can
perform some summary operations. To make this work, we've defined a SQL-like query syntax for Lucene, which has this basic structure:
SELECT field list WHERE filter [ GROUP BY grouping fields ] [ OVER starttime, endtime ] [ MAXCOUNT=number ]
So here are the key things to remember when constructing Lucene-based report queries:
- You can use any standard field from our Event Schema as long as it is persisted in Lucene (not all fields are).
- When referencing fields in the query, use the short field name such as 'msg' or 'evt', not the long label like 'InitiatorUserName'.
- You can if you wish alias each field to something more readable, like this:
SELECT msg AS message, evt AS eventname, ...
Whatever you alias them to is how they will be referred to in the report, e.g. as $F{message}. - Unfortunately at this time the Lucene driver can not fetch a list of fields from the source like the SQL driver can; this means you have to manually construct iReport field variables for the fields you will get back from your query.
Using Fields on Your Report
In iReport, each field returned by the query will become an iReport variable referenced as $F{fieldname}, where fieldname is the alias created by the Lucene or SQL query (or the original field name, if aliases are not used). You can simply lay out an unformatted bunch of $F{fieldname} fields on your report layout to test what you'll actually get back from the query.
Report Development Guide