Sguil Reports with BIRT HOWTO

From NSMWiki

You do not have permission to edit this page, for the following reason:

You can view and copy the source of this page:

= Introduction = For years now, one of Sguil's biggest weaknesses has been a lack of good integrated reporting tools. To be fair, Sguil does offer a few built-in reports, but they are fairly rudimentary and can only be run interactively by an analyst. There is no mechanism to provide good charts and graphs, nor is it easy to create and distribute them automatically. There has been some good work with alert-based reporting, notably the LAMP-based [http://squert.sourceforge.net Squert] application, but there is actually a lot more information in the Sguil database than just who attacked who. Over the last several months, I've been developing a set of Sguil reports that use both alert and network session information stored in the Sguil database. The session-based reports are especially helpful in identifying odd activity that does not trigger any specific alert. At first, these reports were implemented in a rather complex perl script, which was hard to maintain and difficult to customize & distribute to other sites. I have recently ported the functionality of the perl script into a dedicated reporting package, the open source [http://www.eclipse.org/birt BIRT] software. Although I don't want to go into detail here about what BIRT is or list all its features, I should say that it's a very flexible package for creating reports consisting of both table and chart elements, which is ideal for presenting Network Security Monitoring (NSM) data to both analysts and managers. When it is used to run reports (as opposed to creating or editing new report designs), BIRT runs as a Java applet under control of an application server such as Tomcat or JBoss. The report designs are simply XML files, which means that they are easy to distributed and modify. In this HOWTO, I'll show you how to get BIRT running on your RHEL 4/CentOS 4 system. I'll also share with you my standard set of Sguil reports, and it's my hope that some of you will take the next step to design and distribute your own BIRT-based reports for use by other Sguil users. By the way, if you're curious to see what the report looks like, you can view the [[Media:Sguil-sample-birt-report.pdf|Sample Report]]. = BIRT Server Setup = In this section, we'll talk about how to set up the BIRT report view application and verify that it's working. We'll also install some additional software that BIRT needs, including a PDF support library and the MySQL JDBC drivers. == OS Configuration == This HOWTO will assume that you are trying to install BIRT on a Red Hat Enterprise Linux 4 system, or CentOS 4 (a free clone). Other versions of Unix/Linux will work, too, but the mechanics of getting Tomcat installed will be a bit different. == Installing Tomcat == === RHEL 4 === Installing Tomcat is pretty easy. We want Tomcat version 5, which is convenient because that's what Red Hat provides. Installing Tomcat thus becomes a matter of subscribing the system to the "Red Hat Application Server v.2" channel. I'll assume you know how to do this. If not contact your local Red Hat system admin. Once the system is subscribed, you simply need to issue the following command: # up2date tomcat5-admin-webapp If prompted, accept the additional prerequisite packages (such as tomcat itself). === CentOS 4 === If you're installing on CentOS, you'll need to do a few more things since you don't have access to Red Hat's management tools. Check out [http://www.tummy.com/Community/Articles/tomcat-centos/ this guide to installing Tomcat on CentOS 4 or RHEL 4]. == Configuring Tomcat == Now that tomcat is installed, you'll need to make a few config changes to either increase the security of the default installation or to make it more BIRT-friendly. === Make Sure Tomcat Starts at Boot === This is pretty easy. As root, just issue the following command: # chkconfig tomcat5 on === Configure AWT Support === RHEL doesn't seem to provide enough AWT (graphics) support to allow BIRT to build graphs when it's running under an application server and not a GUI. In particular, the default configuration will crash whenever you try to run a report that contains a graph, which isn't very useful. Fortunately, you can easily fix this by adding a command-line switch to the Java runtime, letting it know that AWT is not running under the GUI. Edit the ''/etc/tomcat5/tomcat5.conf'' file and add the following to the '''JAVA_OPTS''' variable (uncommenting it if necessary): JAVA_OPTS="-Djava.awt.headless=true" === Create a BIRT User === Next, you'll need to create a Tomcat management id and a user to access the report applet. This consists of two easy steps. First, you create a user ''role'' (similar to a Unix group) called ''birt-users''. Then you'll create a user ''birt'' assigned to the ''birt-users'' role. You'll also create a separate management user ''admin'' assigned to the pre-defined ''manager'' role. You can do all this in one step by editing the ''/etc/tomcat5/tomcat-users.xml'' file. Change this: <?xml version='1.0' encoding='utf-8'?> <tomcat-users> <role rolename="tomcat"/> <role rolename="role1"/> <role rolename="manager"/> <user username="tomcat" password="tomcat" roles="tomcat"/> <user username="role1" password="tomcat" roles="role1"/> <user username="both" password="tomcat" roles="tomcat,role1"/> </tomcat-users> to this (note changes in bold): <?xml version='1.0' encoding='utf-8'?> <tomcat-users> '''<role rolename="birt-users"/>''' <role rolename="tomcat"/> <role rolename="role1"/> <role rolename="manager"/> <user username="tomcat" password="tomcat" roles="tomcat"/> <user username="role1" password="tomcat" roles="role1"/> <user username="both" password="tomcat" roles="tomcat,role1"/> '''<user username="birt" password="plaintext_password" roles="birt-users"/>''' '''<user username="admin" password="plaintext_password" roles="manager/>''' </tomcat-users> === Configure Authentication Parameters & SSL Security === Since we're configuring the system to require a login to access the reports, it's probably also a good idea if we set up some basic security to prevent someone from sniffing these credentials. We'll configure Tomcat to use HTTP Basic Authentication for logins, and to require SSL for all sessions. First, edit the ''/etc/tomcat5/web.xml'' file to require SSL. Add the following ''security-constraint'' stanza to the file (if there's already one there, delete it and replace it with this one): <!-- Try to force everything to use SSL! --> <security-constraint> <web-resource-collection> <web-resource-name>Protected Context</web-resource-name> <url-pattern>/*</url-pattern> </web-resource-collection> <!-- auth-constraint goes here if you requre authentication --> <user-data-constraint> <transport-guarantee>CONFIDENTIAL</transport-guarantee> </user-data-constraint> <auth-constraint> <role-name>birt-users</role-name> </auth-constraint> </security-constraint> While you're still in the ''web.xml'' file, add the following ''login-config'' section right after the ''security-constraint'': <login-config> <auth-method>BASIC</auth-method> <realm-name>Sguil Report Server</realm-name> </login-config> Now that you've configured Tomcat to require SSL, you'll also need to enable an SSL connector (in more plain English, turn on the SSL support). Edit ''/etc/tomcat5/server.xml'' and find the XML section that starts with: <Service name="Catalina"> Just under that line, add the following connector definition: <!-- Define a SSL HTTP/1.1 Connector on port 8443 --> <Connector port="8443" maxHttpHeaderSize="8192" maxThreads="150" minSpareThreads="25" maxSpareThreads="75" enableLookups="false" disableUploadTimeout="true" acceptCount="100" scheme="https" secure="true" clientAuth="false" sslProtocol="TLS" keystoreFile="/var/tmp/tomcat-ssl/server/server.ks" keystorePass="changeit" algorithm="IbmX509"/> Note the use of the ''algorithm="IbmX509"'' parameter. On RHEL and CentOS, this is necessary because they use the IBM version of Java. Tomcat needs X509 certificate support, and the default name it uses ("SunX509") only works for Sun's JVM. If you're using RHEL or CentOS, you need to specify the IBM algorithm, but if you're using some other platform you probably have the Sun JVM and can omit this option alltogether. === Install an SSL Certificate === The last step to configure Tomcat is to provide it with an SSL server certificate. I'm not going to go into the all the details here, since there are already some good [http://wiki.apache.org/tomcat/HowTo#head-dda58b28679259196562da84ad73d7b35b41c5c2 instructions] on the Tomcat Wiki. Depending on whether you use Verisign certs or your own OpenSSL-based CA, you'll find all you need here. Using those instructions, generate & install your server certificate, then come back to this HOWTO. === Changing the Default JVM (optional) === By default, Tomcat5 on RHEL4 uses JDK 1.4. However, the newest version of BIRT are compiled with Java 1.5 compilers, which makes the incompatible. Fortunately, it's a fairly simple matter to change the version of Java. Just uninstall the old version and install a 1.5 version. It's a bit more complicated with RHEL, though, as the Tomcat5 RPMs ''require'' 1.4. In that case, just install the 1.5 RPMs in addition to the 1.4 packages that are already present, then set the '''''JAVA_HOME''''' variable in ''/etc/sysconfig/tomcat5'' to point to the latest version, like so: JAVA_HOME="/usr/lib/jvm/jre-1.5.0" === Test Tomcat === Now let's see if this all works properly. Reboot the system. When it comes back up, give it a minute or so for Tomcat to start up, then try to access the following URL: https://your.tomcat.server:8443/ If all went well, you should see the Tomcat default page, which contains a message saying that everything's working so far. It's probably also a good idea to try to access the same page via normal HTTP, to make sure that the server properly redirects the browser to the SSL version of the same page: http://your.tomcat.server:8080/ == Installing the BIRT Web Viewer Applet == In this section, we'll walk through the steps necessary to get the standalone BIRT reporting engine running under Tomcat. Then we'll make sure it's working by running a very simple built-in report. === Downloading BIRT and it's Prerequisites === Before we get started, you'll need to download some software. First, there's the BIRT runtime, which can be found on the [BIRT downloads page http://download.eclipse.org/birt/downloads/]. Look for the button marked "Deployment". You don't need the report designer just to run reports that others have created for you. Next, you'll need the [http://www.lowagie.com/iText/ iText] PDF library v1.3, available [http://prdownloads.sourceforge.net/itext/itext-1.3.jar here]. BIRT also requires the Jakarta project's ''Common Logging'' library, which isn't installed by default on RHEL/CentOS. You can download version 1.1 from [http://jakarta.apache.org/site/downloads/downloads_commons-logging.cgi this site]. Finally, you'll need the MySQL JDBC drivers to make the connection to the Sguil database. You can get these from the [http://dev.mysql.com/downloads/connector/j/3.1.html MySQL download page]. === Installing the BIRT Applet === Installing the BIRT applet is very straightforward. Simply uncompress the downloaded archive, copy the applet files into your tomcat directory, and change to owner to match the user your tomcat server runs as: # unzip birt-runtime-2.1.2.zip # cp -r birt-runtime-2_1_2/WebViewerExample /var/lib/tomcat5/webapps/birt-viewer ''(Note: this /var/lib/tomcat5 directory might be different for you if you're installing Tomcat on a non-RHEL/CentOS system! If so, be sure to make the appropriate corrections while following the rest of this HOWTO.)'' === Installing iText === Simply copy the ''.jar'' file you downloaded earlier into the BIRT plugin directory: # cp itext-1.3.jar /var/lib/tomcat5/webapps/birt-viewer/WEB-INF/platform/plugins/com.lowagie.itext/lib === Installing the MySQL JDBC Drivers === Unpack the archive you downloaded earlier. This will create a subdirectory containing one file you need, and a whole lot you don't. BIRT already contains a driver directory especially for the JDBC ''.jar'' file, so all you have to do is to copy it into place: # cp mysql-connector-java-3.1.14/mysql-connector-java-3.1.14-bin.jar /var/lib/tomcat5/webapps/birt-viewer/WEB-INF/platform/plugins/org.eclipse.birt.report.data.oda.jdbc_2.1.1.v20070205-1728/drivers === Installing Commons Logging Support === If you don't already have the Jakarta project's Commons Logging (i.e., you're on RHEL or CentOS), simply unpack the distribution you downloaded earlier and copy the ''.jar'' file into the BIRT directory: # cp commons-logging-1.1/commons-logging-1.1.jar /var/lib/tomcat5/webapps/birt-viewer/WEB-INF/lib === Fix the Permissions === As a precaution, you should ensure that everything under the BIRT directory is owned by the same user that runs the Tomcat server. In RHEL/CentOS, this is user ''tomcat''. # chown -R tomcat.tomcat /var/lib/tomcat5/webapps/birt-viewer === Restart Tomcat === Since you've installed a new applet, the easiest thing to do is to restart Tomcat in order to load it in. # /etc/init.d/tomcat5 restart == Running a Sample Report == Now it's time to see if this is all working correctly. First, try to access the defauilt BIRT page by visiting this URL: https://your.tomcat.server:8443/birt-viewer If the applet runs, it will show you a very simple BIRT startup page with a link to a sample report. Click the ''View Example'' link to start the report viewer. You'll see an extremely simple report that just says, "Congratulations, it all works!" (or words to that effect). If you get that, the it means the basic report engine is installed properly. You should also be sure to test the PDF export function now. While viewing the sample report, look at the top of the viewer window and find the small printer icon. If you hover over that, it'll pop up a tooltip that says "Print report as PDF". Click it and, if all is well, it'll ask you to download a PDF file. Open it and make sure it contains the sample report. If this step fails, the BIRT applet will probably crash. This usually means that the iText ''.jar'' file isn't installed properly. Before continuing, be sure that your BIRT installation passes all the above tests. = Creating Sguil Reports = In this section, we'll finally get to install the Sguil report file and start generating some reports. If you haven't already seen it, you may wish to spend some time looking through the [[Media:Sguil-sample-birt-report.pdf|Sample Report]] just to get an idea of what we're trying to accomplish. == Obtaining the Report Design File == To generate a report, BIRT requires a ''report design'' file, which is nothing more than an XML file that contains all the information about how the report looks, what data sources to contact and which SQL queries to execute. BIRT then runs the report design against your Sguil database and produces the finished report. For this HOWTO, you can fetch the design file that generated the sample report [[Media:Sguil.rptdesign|here]]. == Preparing to use the Design File == In order to make the design file work in your environment, you'll need to create a database account for reporting and make a few edits to the file itself. === Creating a Database User for BIRT === The first thing you should do when getting ready to run reports against your database is to configure a '''read only''' user account that the reporting engine can use. This is important because it means that no matter how badly written the report designs are, they '''cannot modify the database'''. You can run a report with absolute confidence that all your data will be where you left it. As you may be exchanging report designs with other users, you cannot always guarantee that they are well-written. Using a read only account is an important safeguard. To create a new MySQL account, log in to your database as ''root'' and issue the following command. This will create a user ''sguil-ro'' with a password of ''pick_a_password_here'': mysql> GRANT SELECT on sguildb.* to sguil-ro@your.tomcat.servername IDENTIFIED BY PASSWORD("pick_a_password_here"); === Setting Database Parameters === Now that you've created the database user, you'll need to edit the design file to contain the proper MySQL connection info. Edit the design file you downloaded earlier and find the section that looks like this: <data-sources> <oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="SguilDB" id="16"> <property name="odaDriverClass">com.mysql.jdbc.Driver</property> <property name="odaURL">jdbc:mysql://your.mysql.host/sguildb</property> <property name="odaUser">sguil</property> <encrypted-property name="odaPassword"></encrypted-property> </oda-data-source> </data-sources> Once you've located the database section, make the following changes: * Change the ''odaURL'' parameter to point to your database server's hostname. If your Sguil database is '''not''' named ''sguildb'', change that too. * Change the ''odaUser'' to match the username you created above, probably ''sguil-ro''. * You'll need to edit the ''odaPassword'' field to included the '''base64 encoded''' version of your database password. If you have perl handly, you can calculate this with a short one liner, like so: perl -e 'use MIME::Base64; print encode_base64("your_db_password") . "\n";' === Configuring Your Network Info === Ok, this part is a little tricky. Several of the reports in the design file try to look for traffic that is specifically entering or leaving your network, and to do so they need to know the range of addresses on your network. For example, the scan detection reports use the following SQL query: select INET_NTOA(src_ip) as src, count(distinct dst_ip) as dsts, count(distinct dst_port) as ports from sancp where start_time between ? and ? and dst_bytes = 0 and ip_proto in (6,17) and ((src_ip between INET_ATON('192.168.1.0') and INET_ATON('192.168.1.255')) and (dst_ip between INET_ATON('192.168.1.0') and INET_ATON('192.168.1.255')) ) group by src_ip having ports > 0 </property> See those IP address ranges? The range from 192.168.1.0 to 192.168.1.255 corresponds to a class C, or 192.168.1.0/24 in CIDR notation. You'll need to know the first and last IP addresses in your range, so that you can edit these into the report SQL. The design file uses 192.168.1.0/24 as the local network in all the queries, but if yours is different, you can do a few search-and-replace operations to customize this for your own situation. ''Note: If you have several non-contiguous ranges on your network, you may have to play with the AND and OR statements to insert additional ranges, but for now this is outside the scope of the HOWTO. Post on the ''sguil-users'' list if you need help with this.'' == Installing the Report == Now that you've made a bunch of edits to the sample design file, copy it into a place where BIRT can access it. # mkdir /var/lib/tomcat5/webapps/birt-viewer/report # cp sguil.rptdesign /var/lib/tomcat5/webapps/birt-viewer/report # chown -R tomcat.tomcat /var/lib/tomcat5/webapps/birt-viewer/report == Running a Report == Finally! Let's try generating a report! === Ad Hoc Reports === You can generate a report at any time simply by visiting the following URL: https://your.tomcat.server:8443/birt-viewer/frameset?__report=report/sguil.rptdesign This will run the interactive report viewer applet, which will start by prompting you to enter the starting and ending dates for the report (use the format ''YYYY-MM-DD'' or ''YY-MM-DD HH:MM:SS''), as well the IDs of your perimeter sensor(s). You can find the sensor IDs in the sguil console by clicking on the ''Sensor Status'' tab. The integers running along the left side of the list are the sensor IDs. If you have only one perimeter sensor, just enter the number by itself. If you have more than one (e.g., a main link and a screened network) you can enter them as a comma-separated list. It's also worth noting that you can leave any or all of these blank to accept the default value. The start date defaults to midnight of the day before, and the end date defaults to midnight that morning, effectively giving you a report of activity that occurred during the previous day. The default sensor ID is 1, on the theory that most people install their perimeter sensor first. Don't worry, there's an easy way to automate all this when you're generating unattended reports. But for now, just enter some values or accept the defaults and click the ''OK'' button. The system should churn away for a while (it may be a long while if you have a large database). If you've edited the design file properly, it will eventually come back with a report. Without going into too much detail about how to use this interface, I'll just say that the report itself is broken up into pages, and the report viewer applet honors these. Notice the arrows in the upper right corner. You can use these to move back and forth by one page at a time. You can also click the ''Table of Contents'' icon in the upper left-hand corner to display the TOC, which allows you to navigate directly to the page you need. Finally, you can download the PDF version of the report at any time by clicking on the ''Print to PDF'' icon. If you're not such a big fan of the fancy page-based interface, you can generate an all-in-one HTML report and view it in a continuous scroll from beginning to end. Use this URL instead: https://your.tomcat.server:8443/birt-viewer/run?__report=report/sguil.rptdesign === Automatic Reports === Once you've got the ad-hoc reports working, it's time to automate the process. I run reports every night, so they are ready to view when I come in the next morning. You've already seen the URLs to use to generate reports, so the trick now is to do three things: * Avoid having the specify the report parameters interactively * Select an appropriate output format (HTML, PDF, CSV, whatever) * Load the report from the command line and redirect the output to a file instead of launching a viewer The first trick is quite simple. You can encode the parameters into the URL, like so: https://your.tomcat.server:8443/birt-viewer/run?__report=report/sguil.rptdesign&Start%20Date=2007-04-16&End%20Date=2007-04-17&Perimeter%20Sensors=1,2,3 You can also leave these blank (and take the default values), by specifying the URL like: https://your.tomcat.server:8443/birt-viewer/run?__report=report/sguil.rptdesign&Start%20Date=&End%20Date=&Perimeter%20Sensors= The solution to the second problem is equally straightforward. You can use the built-in ''__format'' parameter to specify your favorite output format. The default is a simple one-page HTML file, but this lacks pages and the TOC. I prefer the PDF format, which you can generate like so: https://your.tomcat.server:8443/birt-viewer/run?__report=report/sguil.rptdesign&Start%20Date=&End%20Date=&Perimeter%20Sensors=&__format=PDF Finally, you can automate this process via ''cron'', so that the reports will be run each night and the PDF files will be saved into a directory for later perusal. Edit root's crontab file and add the following entry: 0 1 * * * /usr/bin/wget --user birt --password your_tomcat_birt_password \ "https://your.tomcat.server:8443/birt-viewer/run?__report=report/sguil.rptdesign&Start%20Date=&End%20Date=&Perimeter%20Sensors=&__format=PDF" \ -O "/some/dir/birt-reports/report-`date +\%G-\%m-\%d`.pdf" This will generate a new report every night at 1:00AM and leave them in the ''/some/dir/birt-reports'' directory. The filenames will be datestamped with the current date (e.g. ''report-2007-04-15.pdf''). If you're very clever, you can install a normal webserver on the same system and redirect the reports to the web content directory. I use the following crontab entry to generate reports and email myself a note that contains a link to the newly-created report: 0 1 * * * /usr/bin/wget --user birt --password my_birt_pass \ "https://my.tomcat.server:8443/birt-viewer/run?__report=report/sguil.rptdesign&Start%20Date=&End%20Date=&Perimeter%20Sensors=&__format=PDF" \ -O "/var/www/html/birt-reports/report-`date +\%G-\%m-\%d`.pdf" && \ ( echo -e "A new nightly Sguil report has been created.\nPlease visit the URL \ below to download it.\n\n\thttps://my.tomcat.server/birt-reports/report-`date +\%G-\%m-\%d`.pdf" \ | Mail -s "Nightly Sguil Report `date +\%G-\%m-\%d`" user@domain.com ) One further note about SSL support. If you've opted for a self-signed OpenSSL certificate in your Tomcat server, the odds are that ''wget'' can't verify the legitimacy of your cert and will refuse to load the page. You can force it to ignore this check by adding the ''--no-check-certificate'' option to the ''wget'' command. = Creating Custom Reports = If you find that the sample reports don't quite give you the information that you're looking for, it's possible to create your own custom Sguil reports using the BIRT Report Designer plugin for Eclipse. You can find installation instructions and download packages for most platforms on [http://www.eclipse.org/birt the BIRT home page]. Although it's outside the scope of this HOWTO, you can load the sample sguil design into BIRT and change it to suit your needs, or you can even create entirely new reports. I hope that if you do create new reports, you'll consider contributing them back to the Sguil community for everyone's benefit. I plan to work out some sort of report clearing house in the future, but for now it's easiest just to [mailto:david@vorant.com contact me] and I'll work with you to post them somewhere where people can find them.

Return to Sguil Reports with BIRT HOWTO.