com.martealert
Class martealertSqlUtils

java.lang.Object
  com.martealert.martealertTable
      com.martealert.martealertSqlUtils

All Implemented Interfaces:

martealertConstants, martealertRealTimeInterface

public class martealertSqlUtils

extends martealertTable

implements martealertConstants

Field Summary

Fields inherited from class com.martealert.martealertTable

_, ALIASVAR, columnNames, data, DEFAULT, GROUPBY, IDENT, INDEX, INDEXSEARCH, NAMEDVAR, RELATION, SELECT, TX, UNNAMEDVAR, VAR

Fields inherited from interface com.martealert.martealertConstants

AMBIGUOUS_FIELDS, ANALYZER, CLASSIFICATION, COMPLETION, FTS, FTSTZ, HOUR_IN_MILLIS, INTERVAL, IPADDRS, LASTJOIN, LTS, LTSTZ, MAXMSGID, MAXSEVERITY, MFTS, MLTS, MSGID, MSOURCEPORT, NALERTS, NOTNULL_FIELDS, NULL_FIELDS, NXCLASSIFICATION, ORDER_BY_ALERTS, ORDER_BY_MAXTIME, ORDER_BY_TIME, PRELUDE_TABLES, PROTOCOL, SENSOR, SEVERITY, SEVERITY_COMPLETION, SOURCE, SOURCEIP, SOURCEPORT, SOURCEUID, TARGET, TARGETIP, TARGETPORT, TARGETPORT_PROTOCOL, TARGETUID, TERMINAL, TIME, TIMETZ, TLAST, TOP, TOTALTIME

Fields inherited from interface com.martealert.martealertRealTimeInterface

REAL_TIME_CLASSIFICATION, REAL_TIME_COMPLETION, REAL_TIME_MSGID, REAL_TIME_SEVERITY, REAL_TIME_SOURCEIP, REAL_TIME_TARGETIP, REAL_TIME_TIME

Constructor Summary

martealertSqlUtils()
Creates a new instance of martealertSqlUtils

Method Summary

static void	addColumn(java.sql.Connection con, java.lang.String tableName, java.lang.String newColumnName) Add a column to the table "tableName" of the database with a concrete name.
static void	closeSQLConnection(java.sql.Connection con) Close a generic SQL connection.
static int	getIdFromTime(java.sql.Connection con, java.lang.String time) Get message identifier from time.
static int	getIdFromTime(java.sql.Connection con, java.lang.String time, java.lang.String timetype) Get message identifier from time.
protected static int	getLastId(java.sql.Connection con) Obtain identifier from most recient row.
static java.lang.String	getLastJoinTable(martealertHashFilter hf) Calculate last join table for SQL command.
protected static java.lang.String	getSqlCommand(martealertHashFilter hf) Calculate sql command for tables which always contains _message_ident for all events in Prelude_Alert.
protected static java.lang.String	getSqlCommand(martealertHashFilter hf, java.lang.String limit) Calculate sql command for tables which always contains _message_ident for all events in Prelude_Alert.
protected static java.lang.String	getSqlCommand(martealertHashFilter hf, java.lang.String limit, boolean compareEqual) Calculate sql command for tables which always contains _message_ident for all events in Prelude_Alert.
protected static java.lang.String	getSqlJoinTable(java.lang.String field, martealertHashFilter hf) Calculate Table for making joins between tables in time range specified into hf.
static java.lang.String	getTopJoinTable(martealertHashFilter hf) Get top join table in a String for SQL command.
static java.lang.String	getTopJoinTable(java.lang.String msgid) Get top join table in a String for SQL command, passing a msgid.
static java.sql.Connection	openSQLConnection(java.lang.String usname, java.lang.String passwd, java.lang.String ipaddr) Open a database PostgreSQL connection.
static java.sql.Connection	openSQLConnection(java.lang.String usname, java.lang.String passwd, java.lang.String ipaddr, int port) Open a database PostgreSQL connection.
static java.lang.String	toString(java.sql.Timestamp time) Convert a Timestamp into String with ISO date format: "YYYY-MM-DD hh:mm:ss.mi"

Methods inherited from class com.martealert.martealertTable

clear, getColumn, getColumnNames, getColumnSize, getData, getFTSColumn, getIgnoredColumnsPdf, getIpColumns, getLTSColumn, getMultilineColumns, getPercentColumns, getPortColumns, getProtocolColumns, getRow, getRowSize, getSeverityColumn, getSqlRelation, getTableVector, getValue, getValue, getValue, printTable, reload, setColumnNames, setDataVector, setDataVector, setValue

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

martealertSqlUtils

public martealertSqlUtils()

Creates a new instance of martealertSqlUtils

Method Detail

openSQLConnection

public static java.sql.Connection openSQLConnection(java.lang.String usname,
                                                    java.lang.String passwd,
                                                    java.lang.String ipaddr)

Open a database PostgreSQL connection.

Parameters:

usname - User name of database.

passwd - Password of user name to access the database.

ipaddr - IP address where Postmaster is running.

Returns:

Connection variable to access the database.

openSQLConnection

public static java.sql.Connection openSQLConnection(java.lang.String usname,
                                                    java.lang.String passwd,
                                                    java.lang.String ipaddr,
                                                    int port)

Open a database PostgreSQL connection.

Parameters:

usname - User name of database.

passwd - Password of user name to access the database.

ipaddr - IP address where Postmaster is running.

port - IP port which Postmaster is listening queries.

Returns:

Connection variable to access the database.

closeSQLConnection

public static void closeSQLConnection(java.sql.Connection con)

Close a generic SQL connection.

Parameters:

con - Connection identifier to close.

addColumn

public static void addColumn(java.sql.Connection con,
                             java.lang.String tableName,
                             java.lang.String newColumnName)
                      throws java.sql.SQLException

Add a column to the table "tableName" of the database with a concrete name.

Parameters:

con - Connection variable to access Postgre database.

tableName - String with name of the table which you need to alter.

newColumnName - String with new column name.

Returns:

preludeTable object with altered table in preludeTable.data.

Throws:

java.sql.SQLException

toString

public static java.lang.String toString(java.sql.Timestamp time)

Convert a Timestamp into String with ISO date format: "YYYY-MM-DD hh:mm:ss.mi"

Parameters:

time - Timestamp to be converted.

Returns:

String "YYYY-MM-DD hh:mm:ss.mi"

getIdFromTime

public static int getIdFromTime(java.sql.Connection con,
                                java.lang.String time)
                         throws java.sql.SQLException

Get message identifier from time.

Parameters:

con - Connection to the database.

time - Time of alert which id is wanted to find.

Returns:

int with identifier of alert which time is passed as param.

Throws:

java.sql.SQLException - Sql error.

getIdFromTime

public static int getIdFromTime(java.sql.Connection con,
                                java.lang.String time,
                                java.lang.String timetype)
                         throws java.sql.SQLException

Get message identifier from time.

Parameters:

con - Connection to the database.

time - Time of alert which id is wanted to find.

timetype - It can be "FTS" constant or "LTS"

Returns:

int with identifier of alert which time is passed as param.

Throws:

java.sql.SQLException - Sql error.

getLastId

protected static int getLastId(java.sql.Connection con)
                        throws java.sql.SQLException

Obtain identifier from most recient row.

Parameters:

con - Connection to access the database

Returns:

Last message identifier

Throws:

java.sql.SQLException - SQL error.

getLastJoinTable

public static java.lang.String getLastJoinTable(martealertHashFilter hf)

Calculate last join table for SQL command.

Parameters:

hf - martealertHashFilter to obtain FTS and LTS.

Returns:

String with last join table operation to be added to SQL command.

getTopJoinTable

public static java.lang.String getTopJoinTable(martealertHashFilter hf)

Get top join table in a String for SQL command. DEPRECATED for ver < ale-sql-0.7.1

Parameters:

hf - martealertHashFilter to obtain FTS and LTS.

Returns:

String with top join table for SQL command.

getTopJoinTable

public static java.lang.String getTopJoinTable(java.lang.String msgid)

Get top join table in a String for SQL command, passing a msgid. It's going to be used in martealertDetailedReport DEPRECATED for ver < ale-sql-0.7.1

Parameters:

msgid - message identifier for obtain top join table for detailed report.

Returns:

String with top join table for SQL command.

getSqlCommand

protected static java.lang.String getSqlCommand(martealertHashFilter hf)

Calculate sql command for tables which always contains _message_ident for all events in Prelude_Alert.

Parameters:

hf - martealertHashFilter to get command filtered.

Returns:

Part of 7.2 ale-sql command with the next format: SELECT t0._message_ident,t3.address,t0.time AT TIME ZONE 'GMT' AS time
FROM Prelude_CreateTime AS t0,Prelude_Address AS t3
WHERE (t0.time >= '2006-03-21 12:50:06' AND t0.time <= '2006-03-21 13:30:52') AND t0._parent_type = 'A'
AND t3._index = 0 AND t3._parent_type = 'T' AND t3._message_ident = t0._message_ident
GROUP BY 1, 2, 3

getSqlCommand

protected static java.lang.String getSqlCommand(martealertHashFilter hf,
                                                java.lang.String limit)

Calculate sql command for tables which always contains _message_ident for all events in Prelude_Alert.

Parameters:

hf - martealertHashFilter to get command filtered.

limit - String to specify limit for number of rows.

Returns:

Part of 7.2 ale-sql command with the next format: SELECT t0._message_ident,t3.address,t0.time AT TIME ZONE 'GMT' AS time
FROM Prelude_CreateTime AS t0,Prelude_Address AS t3
WHERE (t0.time >= '2006-03-21 12:50:06' AND t0.time <= '2006-03-21 13:30:52') AND t0._parent_type = 'A'
AND t3._index = 0 AND t3._parent_type = 'T' AND t3._message_ident = t0._message_ident
GROUP BY 1, 2, 3

getSqlCommand

protected static java.lang.String getSqlCommand(martealertHashFilter hf,
                                                java.lang.String limit,
                                                boolean compareEqual)

Calculate sql command for tables which always contains _message_ident for all events in Prelude_Alert.

Parameters:

hf - martealertHashFilter to get command filtered.

limit - String to specify limit for number of rows.

compareEqual - if false operators are >= or <=. If true operators are strict > or <.

Returns:

Part of 7.2 ale-sql command with the next format: SELECT t0._message_ident,t3.address,t0.time AT TIME ZONE 'GMT' AS time
FROM Prelude_CreateTime AS t0,Prelude_Address AS t3
WHERE (t0.time >= '2006-03-21 12:50:06' AND t0.time <= '2006-03-21 13:30:52') AND t0._parent_type = 'A'
AND t3._index = 0 AND t3._parent_type = 'T' AND t3._message_ident = t0._message_ident
GROUP BY 1, 2, 3

getSqlJoinTable

protected static java.lang.String getSqlJoinTable(java.lang.String field,
                                                  martealertHashFilter hf)

Calculate Table for making joins between tables in time range specified into hf. Version ale-own-sql: 7.2

Parameters:

field - Field of top which invoke this method.

hf - martealertHashFilter of SQL Command

Returns:

Part of SQL tables for a field.