The Edamame Demo
The Edamame demo is designed to show two things:
•How to build an application with SQLstream
•How to approach a specific class of problem (the so-called "needle in a haystack")
To do this, we've created a very simplified model of the data flows in a simulated financial services institution, involving customers transferring money into and out of their accounts in different ways. We then monitor the system using SQLstream, showing how to filter large volumes of data looking for specific patterns. Finally, when we detect that pattern, we use the SQLstream s-Server to generate an e-mail message.
In the process, the demo introduces a number of features and concepts of the SQLstream system, including
•The Log File Adapter (for tailing log files into streams)
•The Mail Adapter (for sending e-mail messages)
•Basic streaming operations, such as UNION and JOIN
•Use of views to build application logic
The Edamame scenario represents several components of a simulated financial-services institution's online transaction system. It has a central transaction server, which customers access either through a web application or by IVR (telephone). So these three input components of the system communicate with the SQLstream s-Server in different ways:
Link to SQLstream
Web app logins
The two login systems each trigger actions in the transaction system: a successful login produces either a deposit or a withdrawal of a random amount. Each of the login systems has two tunable parameters: the interval between events (the default is 1000ms) and the percentage of failed logins (the default is 0).
Once you install the demo application into its application server, you can bring up the web page that depicts the components of the system and their interrelationships. The screen shows each of the input systems with updated counts of transactions and rates, and the output (e-mail) system with a count of alerts.
The demo screen also includes "tooltip" text on the black arrows to explain some of the linkages between the subsystems. Clicking the arrows pops up code showing the interfaces to the s-Server. Clicking the icons in the subsystem boxes (the phone, mainframe, etc.) shows the most recent item processed by that subsystem.
The bulk of the Edamame demo objects are a series of SQL views that define the business logic behind our simplified fraud-detection scheme. Based on the two kinds of input to the system (user login data and transaction data), we can look for patterns we define as potentially fraudulent.
1.The first step is to look for multiple failed login attempts within a relatively short period of time. In this example, we'll look for more than three failed login attempts within a one-minute window.
2.The next step is to combine the suspicious login information with transaction data to determine whether any of the suspicious activity was followed closely by monetary withdrawals.
3.Finally, once we find such suspicious withdrawals, we can generate e-mail messages to alert security personnel.
Obviously, the definition of fraud in this example is vastly simpler than it would be in the real world. The point here is to see how one can encode business logic into an SQL application. The process would be similar for a more complex system with more inputs and more involved rules.
The Edamame demo is delivered with SQLstream as a bundled J2EE web application in the file $SQLSTREAM_HOME/demo/edamame/edamame.war.
Note: $SQLSTREAM_HOME refers to the installation directory for s-Server, such as /opt/sqlstream/4.0.XXX.
Before you can run the demo, you need an application server to run the web application. We have developed and tested using Apache Tomcat 5.0 and 5.5, because it's easy, small, and free. The web application should run under other application servers, but we haven't tried them and don't support them.
The demo as written requires that the application be running on port 8080 on the same system as the SQLstream s-Server. All the embedded URLs are hard coded to use localhost:8080, which is the default configuration of Tomcat. However, if you are running the Tomcat package on Ubuntu, you need to change all embedded URLs to specify 8180 instead, because the Ubuntu version of Tomcat uses 8180 as its default port.
The e-mail alerts function requires that the server also have an SMTP program running, such as sendmail or postfix. Most Linux distributions have one installed by default. Note that you can run the demo without sending email. If you choose that option, you do not need an SMTP server.
To run the Edamame demo:
1.Install and start the Tomcat application server : Many Linux distributions provide Apache Tomcat as an installable package. You can also download binaries from the Apache Software Foundation and install it yourself. See the Notes on Installing and Configuring Tomcat at the end of this document on installing and configuring Tomcat.
2.Set the environment variable SQLSTREAM_HOME for all relevant users, including Tomcat as well as the user the SQLstream s-Server runs as. Set it for root, as well, if you run Tomcat as root, such as when running it as a system service.
3.Deploy the Edamame web application. Use the Tomcat web application manager at http://localhost:8080/manager/html. Under the Deploy section, find the "WAR file to deploy" box, and browse to $SQLSTREAM_HOME/demo/edamame/edamame.war. Click Deploy. Edamame will appear in the list of deployed web applications.
4.If you want to test the email notification portion of the demo, make sure an SMTP server such as sendmail or postfix is installed.
You can run through the whole demo scenario or you can just use the various inputs to test server features. For example, you can start just the Web App to test generation of log file data, and inspect the associated stream "EDAMAME"."RawWebAppLoginEvents" or the parsed view of that, which is "EDAMAME"."WebAppLoginEvents".
1.If the SQLstream s-Server is not already running, start it.
2.If you intend to run the email portion of the demo, make sure your SMTP server is running.
3.Open the Edamame web application in a web browser. The URL is .
The Edamame application works for both Mozilla Firefox and Microsoft Internet Explorer. Note that you can also access the application from a remote machine, substituting the actual host name for localhost, assuming your routing and firewall are set up properly. This can reduce the load on the server box, as the web application makes and processes AJAX requests frequently.
4.Start SQLstream s-Studio by double-clicking the SQLstream s-Studio desktop icon.
5.SQLstream s-Studio enables you to see the objects that make up the Edamame application and also to monitor the flow of data through the application's streams and views. Since both the Edamame web application and SQLstream s-Studio take a fair amount of space, it is often helpful to run them on separate desktop workspaces if your environment provides them.
6.Connect to the First SQLstream s-Server in SQLstream s-Studio by right-clicking "First SQLstream s-Server" in the Development pane and choosing "Connect."
7.If you have previously run the Edamame demo and encountered difficulties, or if you want to start over with clean, original data, you should reset the demo by performing the steps in Resetting the Demo before running it again.
8.Start the data flows by clicking the > icons for transaction server, web and phone logins. Data will now be flowing in streams.
9.Do the following:
oWith the web application running, switch to SQLstream s-Studio and open the EDAMAME schema, and then right-click some of the objects to see the data flowing. By default, you will initially see data flowing only in the Transactions and LoginEvents streams.
oOpen and inspect the following:
▪Transactions view, which shows the data parsed from the transaction server's log file. You can trace the data back through the RawTransactions stream and the LogStream plugin, which uses the Log File Adapter. See the topic LogFile Adapter in the Enterprise Integration Guide for more details.
▪LoginEvents view, which shows a UNION of the streams IvrLoginEvents and WebAppLoginEvents).
Note that this UNION brings together disparate sources of data (one parsed from a log file, the other INSERTed directly into a native STREAM).
Bringing all login data into a single stream greatly simplifies the logic later on that filters and analyzes the logins.
▪SuspectLoginFailures view, which achieves several results: it filters for multiple, failed login attempts; it defines the one-minute window; and it partitions the data by account.
▪SuspectDebits view, which JOINs the data from SuspectLoginFailures with the transaction data for the same account, looking for debit transactions.
▪EmailAlertContent view, which formats the data from the SuspectDebits stream into a form that can be inserted into the Alerts FOREIGN STREAM in order to send e-mails.
▪emailSuspectTransactions PUMP, which is the active agent that connects the generated EmailAlertContent items and the Alerts e-mail stream.
11.Launch an attack by pressing the rocket button (on web and/or phone) to launch a single attack. You can also try increasing the login attack rates on the Edamame web application page.
Start the Edamame web application. Either click the name "edamame" on the Tomcat Web Application Manager page or open a browser window directly on http://localhost:8080/edamame.
By applying business rules successively through the views SuspectLoginFailures, SuspectDebits, and EmailAlertContent, the application works from raw data streams to filter down to the kinds of data that are "interesting" for this demonstration. In essence, the key to finding the needle in the haystack is to define smaller and smaller "haystacks" of data until only the "needles" remain. Once the desired set of data rows has been identified (SuspectDebits), the application can transform the data into an output format and place the data into an output stream (Alerts) for further action.