Thursday, September 29, 2005

Sguil Event reporting with BIRT

Sguil (pronounced “sgweel”) is a great platform for IDS operations. However, one of the features that I have found lacking is the reporting capabilities. As an IT manager, I need to know essential information about my infrastructure, and canned reports fail to deliver. As far as reporting solutions go, I have found Actuate Corporation’s offerings to be the best of breed. So I was very excited when I found out that Actuate is offering an Open Source reporting solution called BIRT (Business Intelligence and Reporting Tools). What we will look at is how to create a BIRT report to give us a simple summary page of Sguil events.

Image hosted by

I first heard of BIRT at the Actuate Users conference in August of 2004. Paul Clenahan, one of Actuate’s VPs, described how Actuate is embracing the Open Source development model. He was of course talking about BIRT. Interested, I approached him afterward and we discussed some aspects of how BIRT could benefit the Sguil project. At the time, Actuate was offering BIRT as a module for Eclipse in very early development. I signed up for the pre-release versions and evaluated over the course of the year. At the 2005 Actuate Conference, they announced that they had released a standalone version, which did the full installation of Eclipse with the BIRT workspace preconfigured and ready to roll. More information on both the Eclipse plug-in and standalone versions of BIRT can be found at and

I prefer the approach of separating the reporting system from Sguil for two key reasons: 1) singularity of purpose, and 2) additional flexibility. To my first point, separating out the reporting system coincides with the Unix philosophy that a tool should do one thing and do it well. By separating the reporting functionality from the IDS system, you avoid adding an additional layer of complexity. Sguil’s main focus should be as a console for network security analysts, and I feel that reporting is outside of that scope. Second, you gain much more flexibility with BIRT as a reporting platform; you can develop as many reports as you need with customized look, feel, and report criteria with very little development. With Sguil’s built-in reports, you are limited to what the project developers have time to provide. Report modifications require someone to modify the TCL source code. BIRT has the potential to take NSM operations to the next level in terms of reporting capabilities by tailoring the data to meet decision-making managers’ needs.

Lets take a look at BIRT. There are two type of BIRT packages: standalone packages and Eclipse plug-ins. In the standalone category, there are type types of installation packages, BIRT Report Designer (BRD) and BIRT Report Designer Professional. I will only take a look at BIRT Report Designer in this article. (Perhaps Professional will be a topic for a future article.) The install packages are roughly 100 MB for BRD Pro and 75 MB apiece BRD. The installers for the Standalone version seem to be in the Windows® variety only, while the Eclipse plug-ins are platform-independent Java files.

Installation of BRD is fairly simple: just click on the executable and go. This brings you into the familiar Windows® Install Shield program. The first few screens are just a title screen and the EULA. The next screen asks if you want to do a typical install or a custom install. I always choose custom, and I always keep the install directory default. The custom options only give you the BIRT core so there is not a lot of variety or options to set. It seems to me that they could easily remove the typical/custom choice.

You will also need to get JDBC drivers for MySQL to write reports for Sguil. I download the JDBC Drivers from I used the 3.0 drivers because the 3.1 drivers gave me some issues. After downloading, extract the files to C:\MySQL.

After installation, I start BIRT from the appropriate Start Menu location, located under Programs, and Actuate Birt 1.0.1. This seems to be an all-inclusive install package since the environment looks exactly like Eclipse with the BIRT perspective open.
Image hosted by

On the left hand side is our Palette, Outline, and Data Explorer. Since this is a tabbed interface, you can drag tabs into any floating palette that you choose. For this example I will keep things as is, but I usually like to drag my Data Explorer with my outline to keep my report design elements separate from my design components, but that is a personal preference. On the bottom of the page is the Properties and Property Editor. The Property Editor is a custom layout for whatever component you are working with, while the Properties tab will show you a tree view of all properties that belong to that component.

In this example, I want to create a basic report with BIRT using the Sguil database. This is a simple report Summary page that will display a count of all the incidents in the Sguil database grouped by category. I will break out the report into two columns, with the left side displaying the Category title, and the right displaying the count of events to date.

First, go up to the “File” and “New Report”. On the report title page, I just entered “Sguil Report”. The next screen gives you report templates to use. Since this will be a very simple report, I chose blank report and clicked Finish.
Image hosted by

Image hosted by

Next thing I do is go over to the Data Explorer tab. I can see that I do not have a data source, without which I cannot pull any data for my report. So I need to define a new Data Source. I right mouse click on Data Source and choose “New Data Source”.

Image hosted by
I will need to add the MySQL JDBC drivers in order to continue. Next, I choose Manage Drivers. Then I click on Add and point to the directories where the JAR files reside under the C:\MySQL directory.

Image hosted by

Back on the Add Datasource screen, I add the information like in the picture below. Note the Database URL syntax. In this case I used jdbc:mysql://, where is the IP address of my MySQL database and sguildb is the name of the database for Sguil.

Image hosted by

Returning to the Data Explorer, I right mouse click on the newly created Data Source and choose rename, so I can rename the component SguilDataSource. Now I need to create a Data Set (known as a Recordset in other environments). I right mouse click on Data Sets. The first page in the properties dialog is the SQL screen. I enter the following SQL statement:


To speed up future reports, I create the following index for the Sguil database:

create index

With my Data Source and Data Set created, I am ready to create my report body. First, I move back to my Palette, which is located on the right hand side of the workspace. I select a “Table” object from the drop down list and drag it over to my report body.

Image hosted by

When the Setup Table dialog appears, I select 2 Columns and 1 row. Even so, what I end up with is 3 actual rows: one header, one detail, and one footer.

I need to add data to the table. I go back to the DataExplorer and expand the Data Set branch. I drag “Status” over to the first column, and “count(status)” to the second column. Now if I preview my report, I will see that status is going to display the numbers of the Status ID’s, not the status it actually stands for. So I need to do a replace of these values. To correct this, I need to add a bit of code to interpret what these values mean, and I will do so in the Value Expression for the status cell. I do this by double clicking on the status cell to open up the Expression Builder. Alternatively you can right mouse click on the status cell and choose “Edit Value/Expression” as pictured below.

Image hosted by

To replace the values I put in the following code:

if (row["status"] == "0")
row["status"] = "Active";

if (row["status"] == "1")
row["status"] = "N/A";

if (row["status"] == "11")
row["status"] = "Category 1";

if (row["status"] == "12")
row["status"] = "Category 2";

if (row["status"] == "13")
row["status"] = "Category 3";

if (row["status"] == "14")
row["status"] = "Category 4";

if (row["status"] == "15")
row["status"] = "Category 5";

if (row["status"] == "16")
row["status"] = "Category 6";

For some odd reason, using a switch statement did not work here. Now, I click OK and go back to the report designer. I change the headers for each column by clicking on the header cells and entering the desired text in the “Text” property. My table is shown below.

Image hosted by

To me this report is very plain, so lets add a few more elements. I need to add a report title to this page, and I want to change the color of the header row. To add the report title is easy. I go over to the component palette, and drag a label over. I change the text in it to “Sguil Report Type Summary”. Using the standard text controls at the bottom, it’s easy to bold the title, make it large, and center it. Changing the color of the header row is accomplished in the Outline tab, by selecting the table, header, and row. Then I go into the Property window to set the background color to blue, and set the font weight to bold.

The last thing is to have the colors alternate in the detail rows to make the report easier to read. First, I create a parameter to store the row number. The next step is to go over to the data explorer, right mouse click on Report Parameters and choose New Report Parameter.

Image hosted by
I enter in the name of the parameter and check the boxes for Allow Null Values, Hidden, and Do Not Echo Input. Next I set the type to float, and change the format to fixed. Then it’s back to the report outline, where I right mouse click on the Sguil Report parent object and select Edit Code.

Image hosted by

It’s time to change the function to Initialize and set the row number parameter to its initializing value.

Image hosted by

Now I go back to the Outline, select the table element, right mouse click and choose Edit Code. At the OnRow function, the parameter is incremented by 1 using the ++ operator.

Image hosted by

Now, here comes the magic: in the Outline, I select Row under the Table object and go into the Property Editor for the Row. Then I select the Highlights tab and click on the Add button. Input the formula as shown below, I select a background color.

Image hosted by

Below is a screenshot of the final product.
Image hosted by
Now the report is complete. Although I did not cover using parameters, we could easily modify this report to include parameters for a specific date range, or only a certain set of categories. Using BRD, the report can be outputted using the Preview as HTML or Preview as PDF options under the file menu. If you would like to automate the report process, you can publish the final report design file to an Apache Tomcat server and schedule it to run at regular intervals.

BIRT reporting capabilities can help NSM operations by filling in the deficiencies in Sguil’s reporting capabilities. By being able to provide ad-hoc reports, NSM operations can effectively communicate incidents and policy violations in a manner that will allow stakeholders to make informed decisions about their security policies. I hope NSM operations find this a useful tool for working with their customers.


Anonymous said...

Great article.
On the row status you could also have used the map feature to display status ID. Just select the control and on the Property Editor, select the Map tab. Add a map for each condition.

John Ward said...


Thanks for the tip. I was looking for something like that. That makes it much easier.


Anonymous said...

Thanks for your great article.I have got very much helped by that.I also want to know something from u.i.e., I want to
get a value by executing one query for example let me give u I have start date variable to get by executing a query and that query should be used in a query in the data set which generates my report.
For example:select * from XXX where startdate = XXXX;
I need to get this XXXX from another table.
How can it be possible.It will be very help ful for me if u answer this or can give me a link where i can get solution to this stuff


John Ward said...


This is an Oracle based example:
select 'X' from table where to_char(test_Date, 'dd-mon-yyyy') = (select to_char(sysdate, 'dd-mon-yyyy') from dual)

Different databases will handle date types differently. I will address this more throughly in an article.

Anonymous said...

Hi john
im luiz from brazil and id like i think you could help me
im working in a oracle pl/sql project and
in some of these menus of the system i need to send to report informations about 2 fields (example: name(list of values - lov and birth date-list of values too)
in the first time the report bring the data but in the second time dont update the informations of the fields
how i can proceed to generate reports with the users choice in the fields(name and birth date)? how i can create parameters for it? do you have a
article to send to me , please
hope for a answer as soon as you can
sorry for my english but i dont speak as well
tks so much

Anonymous said...

Looks good, but when I tried to change the Value Expression to what you had and ran the report, the header was the only thing that showed up. Any idea on how to get this to work with BIRT 2.1.2?

John Ward said...

With BIRT 2.1.2, you can actually just drag over the data set itself into the report design section. Some of the parameter binding is actually much easier now than in the version demonstrated here (I believe at the time it was 1.0). Now you can actually bind and create a report parameter directly from the data set dialog to make things easier.

What I would do, check if you can preview using the data set preview tab, and if so, then just drag the data set from the Data Explorer into the report designer. This will create your table with data and headers automatically.

Anonymous said...

Thanks for the reply John. I can create the table and have it work without anything fancy, the problem is when I try to change the Value Expression to:
if (row["status"] == "0")
row["status"] = "Active";

Then the only thing that shows up when I generate the report is the column header. AFAIK I'm following the instructions correctly. I think something must of changed in the newer versions of BIRT.

John Ward said...


Actually, it looks like you are using the letter "o" instead of the number "0" (zero). At the time, BIRT handled data types a little differently (I believe), but now, you can just use if (row["status"] == 0) row["status"] = Active;

To actually make things a little easier, you can use what they call a Map. Instead of changing the value expression for status using an expression, you can actually now just select the element, under the property editor go to the Map tab, and enter in the rules there. So you would create rules like:

(current data row, I believe the Map editor uses row["data item"]) is equal to range of values (in Squil, its 0, 1, 10, 11, 12, 13, 14, 15, 16 IIRC), and replace them with the correct strings. This is a lot easier than working with the value expression.

If you need some help with that, shoot me an email with the report design you are using, and I'll take a look at it.


Anonymous said...

Thanks John, I went with the Map editor and it works great. I appriciate your help!

Anonymous said...

Hi john

I don't have the "Edit Code" command in the report's contextual menù! :-(

My BIRT version is 1.0.1...

Anonymous said...

thank for article