int cgiMain() {
#ifdef MYSQL_DB
static MYSQL *dbh; /* database connect handle */
static MYSQL_RES *result; /* database query results */
static MYSQL_ROW values; /* query data returned */
unsigned int colcount =0; /* number of returned columns */
int server_version; /* returned server version */
#endif
#ifdef ORACLE_DB
sqlo_db_handle_t dbh; /* database handle */
sqlo_stmt_handle_t sth1; /* statement handle 1 */
char server_version[1024]=""; /* string for returned server version */
int stat =0; /* status of sqlo calls */
int handle =0; /* handle of the interrupt handler */
const char ** values; /* values */
#endif
char sqlquery_str[1024] =""; /* SQL query string */
int allrows =0; /* number of returned rows */
int rowcount =0; /* row iteration counter */
div_t oddline_calc; /* calculates even/odd row color */
char router[41] =""; /* selected router IP */
char start_date[11] =""; /* selected start date */
char start_time[6] =""; /* selected start time */
char end_date[11] =""; /* selected end date */
char end_time[6] =""; /* selected end time */
char select_by[11] =""; /* select by start_date (def) | stop_date */
char order_by[13] =""; /* sort list by column */
char sort_order[5] =""; /* ascending or descending */
char **form_data; /* string array for query data */
char title[256] = ""; /* cgi title string */
struct tm *tm_ptr; /* containing time structure */
time_t now, old; /* containing timestamp */
char err_str[2048] = ""; /* use for combined error string */
char dataunit[255] = "0 Bytes"; /* holds the calculated KB/MB */
unsigned long long sum_bin = 0;
unsigned long long sum_bout = 0;
unsigned long long sum_ball = 0;
char sum_buf[255] = "0";
_abort_flag = 0;
#ifdef ORACLE_DB
/* ------------------------------------------------------------------- *
* ORACLE_HOME is needed for OCI8 to find tnsnames.ora *
* ------------------------------------------------------------------- */
putenv(WEB_ORACLE_ENV);
/* initialize the connection */
if (SQLO_SUCCESS != sqlo_init(SQLO_OFF, 1, 100))
cgi_error("Error: Failed to init libsqlora8.");
/* register the interrupt handler */
sqlo_register_int_handler(&handle, sigint_handler);
/* login to the database */
if (SQLO_SUCCESS != sqlo_connect(&dbh, WEB_TNS_STRING))
cgi_error("Error: Cannot connect to database.");
RETURN_ON_ABORT; /* finish if SIGINT was catched */
if (SQLO_SUCCESS != sqlo_server_version(dbh, server_version,
sizeof(server_version)))
cgi_error(sqlo_geterror(dbh));
RETURN_ON_ABORT; /* finish if SIGINT was catched */
/* enable autocommit, each statement is commited as a single transaction */
stat = sqlo_set_autocommit(dbh, 1);
/* we load the cgi form values into form_data */
if (cgiFormEntries(&form_data) != cgiFormSuccess)
cgi_error("Error: Could not retrieve form data.");
/* ------------------------------------------------------------------- *
* If we are not called with arguments, we display the query selector *
* with a query to list the available routers from edacs_router. *
* ------------------------------------------------------------------- */
if(form_data[0] == NULL) {
/* define the SQL query */
snprintf(sqlquery_str, sizeof(sqlquery_str), "SELECT ROUTER FROM %s.EDACS_ROUTER", EDACSADMIN);
/* initialize the statement handle */
sth1 = SQLO_STH_INIT;
/* opens a cursor for the query statement */
if ( 0 > (sqlo_open2(&sth1, dbh, sqlquery_str, 0, NULL)))
cgi_error(sqlo_geterror(dbh));
RETURN_ON_ABORT; /* finish if SIGINT was catched */
/* get the output column names */
//if (SQLO_SUCCESS != sqlo_ocol_names2(sth1, &colcount, &colnames))
// cgi_error("Error getting the DB columns with sqlo_ocol_names2()");
//RETURN_ON_ABORT; /* finish if SIGINT was catched */
#endif
#ifdef MYSQL_DB
/* initialize the connection */
dbh = mysql_init(NULL);
if(dbh == NULL) cgi_error("Error: Failed to init MySQL DB.");
/* login to the database */
if (mysql_real_connect(dbh, MYSQLIP, EDACSADMIN, ADMIN_PASS, DB_NAME, DB_PORT, NULL, 0) == 0)
cgi_error("Error: Cannot connect to database.");
/* Get the database version */
server_version = mysql_get_server_version(dbh);
/* we load the cgi form values into form_data */
if (cgiFormEntries(&form_data) != cgiFormSuccess)
cgi_error("Error: Could not retrieve form data.");
/* ------------------------------------------------------------------- *
* If we are not called with arguments, we display the query selector *
* with a query to list the available routers from edacs_router. *
* ------------------------------------------------------------------- */
if(form_data[0] == NULL) {
/* create the SQL query string */
snprintf(sqlquery_str, sizeof(sqlquery_str), "SELECT router FROM edacs_router");
/* Prepare and execute the SQL statement */
if(mysql_query(dbh, sqlquery_str) != 0) {
if(DEBUG == 0) cgi_error(mysql_error(dbh));
else snprintf(err_str, sizeof(err_str), "DB error %s\n\nQuery string %s",
mysql_error(dbh), sqlquery_str);
cgi_error(err_str);
}
/* get query results set */
result = mysql_store_result(dbh);
if (result == NULL) {
snprintf(err_str, sizeof(err_str), "No results for query: %s\n", sqlquery_str);
cgi_error( err_str);
}
colcount = mysql_num_fields(result);
#endif
/* ------------------------------------------------------------------- *
* The timestamps are used for range pre-selection or show query time *
* ------------------------------------------------------------------- */
now = time(NULL);
tm_ptr = localtime(&now);
strftime(end_date, sizeof(end_date), "%d.%m.%Y", (tm_ptr));
strftime(end_time, sizeof(end_time), "%H:%M", tm_ptr);
old = time(NULL) - 7200;
tm_ptr = localtime(&old);
strftime(start_date, sizeof(start_date), "%d.%m.%Y", tm_ptr);
strftime(start_time, sizeof(start_time), "%H:%M", tm_ptr);
/* ------------------------------------------------------------------- *
* Start the HTML output *
* ------------------------------------------------------------------- */
/* define the CGI title */
snprintf(title, sizeof(title), "Router Session Activity by Time");
pagehead(title);
fprintf(cgiOut, "<div id=\"content\">\n");
fprintf(cgiOut, "<form action=\"router-acttime.cgi\" method=\"get\">\n");
fprintf(cgiOut, "<table class=\"inner\" width=100%%>\n");
/* 1st row, display headers */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<th class=\"inner\">Router</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Time Frame</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Order By</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Sort Order</th>\n");
/* 2nd row */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<td class=\"inner\"></td>\n");
fprintf(cgiOut, "<td class=\"inner-ctr\">From:</td>\n");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "</tr>\n");
/* 3rd row, request values */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<td class=\"inner-ctr\">(choose one)</td>");
fprintf(cgiOut, "<td class=\"inner-ctr\">");
fprintf(cgiOut, "<input type=text size=10 name=start_date value=\"%s\">",start_date);
fprintf(cgiOut, "<input type=text size=5 name=start_time value=\"%s\">",start_time);
fprintf(cgiOut, "</td>");
fprintf(cgiOut, "<td class=\"inner-ctr\">(choose one)</td>");
fprintf(cgiOut, "<td class=\"inner\">");
fprintf(cgiOut, "<input type=radio value=\"asc\" checked name=\"sort_order\">");
fprintf(cgiOut, "Ascending</td>");
fprintf(cgiOut, "</tr>\n");
/* 4th row, request values */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<td class=\"inner-ctr\">");
fprintf(cgiOut, "<select name=\"router\" size=\"1\">");
/* fetch the data */
#ifdef ORACLE_DB
while ( SQLO_SUCCESS == (stat = (sqlo_fetch(sth1, 1)))) {
/* get one record */
values = sqlo_values(sth1, NULL, 1);
#endif
#ifdef MYSQL_DB
while((values = mysql_fetch_row(result)) != NULL) {
#endif
fprintf(cgiOut, "<option value=\"%s\">%s</option>",values[0],values[0]);
}
fprintf(cgiOut, "</select></td>");
fprintf(cgiOut, "<td class=\"inner-ctr\">To:</td>");
fprintf(cgiOut, "<td class=\"inner-ctr\">");
fprintf(cgiOut, "<select name=\"order_by\" size=\"1\">");
fprintf(cgiOut, "<option value=\"username\">User Name</option>");
fprintf(cgiOut, "<option value=\"service\">Service</option>");
fprintf(cgiOut, "<option value=\"ip_or_phone\">IP or Phone</option>");
fprintf(cgiOut, "<option selected value=\"start_date\">Start Date</option>");
fprintf(cgiOut, "<option value=\"stop_date\">Stop Date</option>");
fprintf(cgiOut, "<option value=\"elapsed_mins\">Elapsed Time</option>");
fprintf(cgiOut, "<option value=\"tty\">TTY</option>");
fprintf(cgiOut, "<option value=\"bytes_in\">Bytes In</option>");
fprintf(cgiOut, "<option value=\"bytes_out\">Bytes Out</option>");
fprintf(cgiOut, "<option value=\"packets_in\">Packets In</option>");
fprintf(cgiOut, "<option value=\"packets_out\">Packets Out</option>");
fprintf(cgiOut, "<option value=\"throughput\">Throughput</option>");
fprintf(cgiOut, "</select></td>");
fprintf(cgiOut, "<td class=\"inner\">");
fprintf(cgiOut, "<input type=radio name=\"sort_order\" value=\"desc\">Descending</td>");
fprintf(cgiOut, "</tr>\n");
/* 5th row */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "<td class=\"inner-ctr\">");
fprintf(cgiOut, "<input type=text size=10 name=\"end_date\" value=\"%s\">", end_date);
fprintf(cgiOut, "<input type=text size=5 name=\"end_time\" value=\"%s\"><br> </td>", end_time);
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "</tr>\n");
/* 6th and last row, close the frame */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<th class=\"inner\" colspan=4><input type=submit value=\"Run Query\"></th>");
fprintf(cgiOut, "</tr>\n");
fprintf(cgiOut, "</table>\n");
fprintf(cgiOut, "</form>\n");
fprintf(cgiOut, "<h3>Additional Information</h3>\n");
fprintf(cgiOut, "<hr>\n");
fprintf(cgiOut, "<p>");
fprintf(cgiOut, "This query returns the list of user sessions on the selected router for a given time period.");
fprintf(cgiOut, "<ul>");
fprintf(cgiOut, "<li>Select the router from the drop down list. If unsure, see <a href=\"router-list.cgi\">List of Routers</a> for more information.");
fprintf(cgiOut, "<li>The time frame can be adjusted by typing directly into it, using the DD.MM.YYYY HH:MM format.");
fprintf(cgiOut, "<li>Choosing a large time frame can result in a long query and a very large result set (thousands of rows).");
fprintf(cgiOut, "<li>The results list can be ordered using criteria from the \"Order By\" drop down list.");
fprintf(cgiOut, "</ul>\n");
fprintf(cgiOut, "</p>\n");
pageside();
} /* end if for displaying the query request */
else {
/* ------------------------------------------------------------------- *
* check if we got all information to make the SQL query *
* --------------------------------------------------------------------*/
if ( cgiFormString("router", router, sizeof(router)) != cgiFormSuccess )
int cgiMain() {
#ifdef MYSQL_DB
static MYSQL *dbh; /* database connect handle */
static MYSQL_RES *result; /* database query results */
static MYSQL_ROW values; /* query data returned */
unsigned int colcount =0; /* number of returned columns */
int server_version; /* returned server version */
#endif
#ifdef ORACLE_DB
sqlo_db_handle_t dbh; /* database handle */
sqlo_stmt_handle_t sth1; /* statement handle 1 */
char server_version[1024]=""; /* string for returned server version */
int stat =0; /* status of sqlo calls */
int handle =0; /* handle of the interrupt handler */
//const char ** colnames; /* column names */
const char ** values; /* values */
#endif
char sqlquery_str[1024] =""; /* SQL query string */
int allrows =0; /* number of returned rows */
int rowcount =0; /* row iteration counter */
div_t oddline_calc; /* calculates even/odd row color */
int top_count =0; /* how many top ip to display */
char start_date[11] =""; /* selected start date */
char start_time[6] =""; /* selected start time */
char end_date[11] =""; /* selected end date */
char end_time[6] =""; /* selected end time */
char order_by[13] =""; /* sort list by column */
char sort_order[5] =""; /* ascending or descending */
char **form_data; /* string array for query data */
char title[256] = ""; /* cgi title string */
struct tm *tm_ptr; /* containing time structure */
time_t now, old; /* containing timestamp */
char err_str[2048] = ""; /* use for combined error string */
int period = 0; /* the period to display */
char dataunit[255] = "0 Bytes"; /* holds the calculated KB/MB */
_abort_flag = 0;
#ifdef ORACLE_DB
/* ------------------------------------------------------------------- *
* ORACLE_HOME is needed for OCI8 to find tnsnames.ora *
* ------------------------------------------------------------------- */
putenv(WEB_ORACLE_ENV);
/* initialize the connection */
if (SQLO_SUCCESS != sqlo_init(SQLO_OFF, 1, 100))
cgi_error("Error: Failed to init libsqlora8.");
/* register the interrupt handler */
sqlo_register_int_handler(&handle, sigint_handler);
/* login to the database */
if (SQLO_SUCCESS != sqlo_connect(&dbh, WEB_TNS_STRING))
cgi_error("Error: Cannot connect to database.");
RETURN_ON_ABORT; /* finish if SIGINT was catched */
if (SQLO_SUCCESS != sqlo_server_version(dbh, server_version,
sizeof(server_version)))
cgi_error(sqlo_geterror(dbh));
RETURN_ON_ABORT; /* finish if SIGINT was catched */
/* enable autocommit, each statement is commited as a single transaction */
stat = sqlo_set_autocommit(dbh, 1);
#endif
#ifdef MYSQL_DB
/* initialize the connection */
dbh = mysql_init(NULL);
if(dbh == NULL) cgi_error("Error: Failed to init MySQL DB.");
/* login to the database */
if (mysql_real_connect(dbh, MYSQLIP, EDACSADMIN, ADMIN_PASS, DB_NAME, DB_PORT, NULL, 0) == 0)
cgi_error("Error: Cannot connect to database.");
/* Get the database version */
server_version = mysql_get_server_version(dbh);
#endif
/* we load the cgi form values into form_data */
if (cgiFormEntries(&form_data) != cgiFormSuccess)
cgi_error("Error: Could not retrieve form data.");
if(form_data[0] == NULL) {
/* ------------------------------------------------------------------- *
* Start the HTML output to display the query selection *
* ------------------------------------------------------------------- */
/* define the CGI title */
snprintf(title, sizeof(title), "Top IP Address Session Activity");
pagehead(title);
fprintf(cgiOut, "<div id=\"content\">\n");
fprintf(cgiOut, "<form action=\"ip-toplast.cgi\" method=\"get\">\n");
fprintf(cgiOut, "<table class=\"inner\" width=100%%>\n");
/* 1st row, display headers */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<th class=\"inner\">Number of IP</th>");
fprintf(cgiOut, "<th class=\"inner\">Time Frame</th>");
fprintf(cgiOut, "<th class=\"inner\">Top by</th>");
fprintf(cgiOut, "<th class=\"inner\">Sort Order</th>");
fprintf(cgiOut, "</tr>\n");
/* 2nd row */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "<td class=\"inner\"><input type=radio value=\"24\" checked name=\"start\">Last Day</td>");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "</tr>\n");
/* 3rd row, request values */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<td class=\"inner-ctr\">(choose one)</td>");
fprintf(cgiOut, "<td class=\"inner\"><input type=radio value=\"168\" name=\"start\">Last Week</td>");
fprintf(cgiOut, "<td class=\"inner-ctr\">(choose one)</td>");
fprintf(cgiOut, "<td class=\"inner\"><input type=radio value=\"desc\" checked name=\"sort_order\">");
fprintf(cgiOut, "Top</td>");
fprintf(cgiOut, "</tr>\n");
/* 4th row, request values */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<td class=\"inner-ctr\"><select name=\"top_count\" size=\"1\">");
fprintf(cgiOut, "<option value=\"5\">Top 5 IP</option>");
fprintf(cgiOut, "<option selected value=\"10\">Top 10 IP</option>");
fprintf(cgiOut, "<option value=\"20\">Top 20 IP</option>");
fprintf(cgiOut, "<option value=\"50\">Top 50 IP</option>");
fprintf(cgiOut, "</select></td>");
fprintf(cgiOut, "<td class=\"inner\"><input type=radio value=\"720\" name=\"start\">Last Month</td>");
fprintf(cgiOut, "<td class=\"inner-ctr\"><select name=\"order_by\" size=\"1\">");
fprintf(cgiOut, "<option value=\"elapsed_mins\">Elapsed Time</option>");
fprintf(cgiOut, "<option value=\"bytes_in\">Bytes In</option>");
fprintf(cgiOut, "<option selected value=\"bytes_out\">Bytes Out</option>");
fprintf(cgiOut, "<option value=\"packets_in\">Packets In</option>");
fprintf(cgiOut, "<option value=\"packets_out\">Packets Out</option>");
fprintf(cgiOut, "<option value=\"sessions\">Session Count</option>");
fprintf(cgiOut, "</select></td>");
fprintf(cgiOut, "<td class=\"inner\"><input type=radio name=\"sort_order\" value=\"asc\">Bottom</td>");
fprintf(cgiOut, "</tr>\n");
/* 5th row */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "<td class=\"inner\">");
fprintf(cgiOut, "<input type=radio value=\"2160\" name=\"start\">Last 3 Months</td>");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "</tr>\n");
/* 6th and last row, close the frame */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<th class=\"inner\" colspan=4><input type=submit value=\"Run Query\"></td>");
fprintf(cgiOut, "</tr>\n");
fprintf(cgiOut, "</table>\n");
fprintf(cgiOut, "<h3>Additional Information</h3>\n");
fprintf(cgiOut, "<hr>\n");
fprintf(cgiOut, "<p>\n");
fprintf(cgiOut, "This query returns a list of top IP addresses of the \"Order By\" selection for the last time period choosen.");
fprintf(cgiOut, " It will give you a quick view who is possibly missusing the service, i.e. transferring large amounts of data in or out.");
fprintf(cgiOut, "<ul>");
fprintf(cgiOut, "<li>Select the number of top IP to display (5, 10, 20, 50) from the drop down list.");
fprintf(cgiOut, "<li>The time frame can be selected from the radio menu, time is counting back from now.");
fprintf(cgiOut, "<li>The results list is grouped by the \"Order By\" list, and sorted \"Top\" down or \"Bottom\" up.");
fprintf(cgiOut, "</ul>");
fprintf(cgiOut, "</p>\n");
} /* end if for displaying the query request */
else {
/* ------------------------------------------------------------------- *
* check if we got all information to make the SQL query *
* --------------------------------------------------------------------*/
if ( cgiFormIntegerBounded("top_count", &top_count, 1, 50, 10)
!= cgiFormSuccess )
cgi_error("Error retrieving IP top count.");
if ( cgiFormIntegerBounded("start", &period, 1, 2160, 24)
!= cgiFormSuccess )
cgi_error("Error retrieving start period information.");
if ( cgiFormString("order_by", order_by, sizeof(order_by))
!= cgiFormSuccess )
cgi_error("Error retrieving order_by information.");
if ( cgiFormString("sort_order", sort_order, sizeof(sort_order))
!= cgiFormSuccess )
cgi_error("Error retrieving sort_order information.");
/* ------------------------------------------------------------------- *
* The calculate query start and end time from given period in hours *
* ------------------------------------------------------------------- */
now = time(NULL);
tm_ptr = localtime(&now);
strftime(end_date, sizeof(end_date), "%d.%m.%Y", (tm_ptr));
strftime(end_time, sizeof(end_time), "%H:%M", tm_ptr);
old = time(NULL) - (period * 3600);
tm_ptr = localtime(&old);
strftime(start_date, sizeof(start_date), "%d.%m.%Y", tm_ptr);
strftime(start_time, sizeof(start_time), "%H:%M", tm_ptr);
/* ------------------------------------------------------------------- *
* check we got all parts and can start doing the SQL query below *
* --------------------------------------------------------------------*/
#ifdef ORACLE_DB
snprintf(sqlquery_str, sizeof(sqlquery_str), "SELECT IP_ADDR, TO_CHAR(SUM(BYTES_IN), '999,999,999,999') BYTES_IN, TO_CHAR(SUM(BYTES_OUT), '999,999,999,999') BYTES_OUT, TO_CHAR(SUM(PACKETS_IN), '999,999,999,999') PACKETS_IN, TO_CHAR(SUM(PACKETS_OUT), '999,999,999,999') PACKETS_OUT, TO_CHAR(SUM(ELAPSED_MINS), '99,999.99') ELAPSED_MINS, COUNT (IP_ADDR) AS SESSIONS FROM %s.V_EDACS WHERE BYTES_IN IS NOT NULL AND START_DATE BETWEEN TO_DATE('%s %s', 'dd.mm.yyyy hh24:mi') and TO_DATE ('%s %s', 'dd.mm.yyyy hh24:mi') GROUP BY IP_ADDR ORDER BY %s %s",
EDACSADMIN, start_date, start_time, end_date,
end_time, order_by, sort_order);
/* initialize the statement handle */
sth1 = SQLO_STH_INIT;
/* opens a cursor for the query statement */
if ( 0 > (sqlo_open2(&sth1, dbh, sqlquery_str, 0, NULL))) {
if(DEBUG == 0) cgi_error(sqlo_geterror(dbh));
else snprintf(err_str, sizeof(err_str), "DB error %s\n\nQuery string %s",
sqlo_geterror(dbh), sqlquery_str);
cgi_error(err_str);
}
RETURN_ON_ABORT; /* finish if SIGINT was catched */
/* get the output column names */
//if (SQLO_SUCCESS != sqlo_ocol_names2(sth1, &colcount, &colnames))
// cgi_error("Error getting the DB columns with sqlo_ocol_names2()");
// RETURN_ON_ABORT; /* finish if SIGINT was catched */
#endif
#ifdef MYSQL_DB
snprintf(sqlquery_str, sizeof(sqlquery_str), "SELECT ip_addr, SUM(bytes_in) bytes_in, SUM(BYTES_OUT) bytes_out, TRUNCATE((bytes_in+bytes_out)/SUM(TIME_TO_SEC(elapsed_mins)),2) throughput, FORMAT(SUM(packets_in), 0) packets_in, FORMAT(SUM(packets_out), 0) packet_out, SEC_TO_TIME(SUM(TIME_TO_SEC(elapsed_mins))) elapsed_mins, COUNT(ip_addr) AS SESSIONS FROM v_edacs WHERE bytes_in IS NOT NULL AND start_date BETWEEN STR_TO_DATE('%s %s', '%s') and STR_TO_DATE('%s %s', '%s') GROUP BY ip_addr ORDER BY %s %s",
start_date, start_time, "%d.%m.%Y %H:%i",
end_date, end_time, "%d.%m.%Y %H:%i",
order_by, sort_order);
/* Prepare and execute the SQL statement */
if(mysql_query(dbh, sqlquery_str) != 0) {
if(DEBUG == 0) cgi_error(mysql_error(dbh));
else snprintf(err_str, sizeof(err_str), "DB error %s\n\nQuery string %s",
mysql_error(dbh), sqlquery_str);
cgi_error(err_str);
}
/* get query results set */
result = mysql_store_result(dbh);
if (result == NULL) {
snprintf(err_str, sizeof(err_str), "No results for query: %s\n", sqlquery_str);
cgi_error( err_str);
}
allrows = mysql_num_rows(result);
colcount = mysql_num_fields(result);
#endif
/* ------------------------------------------------------------------------ *
* start the html output *
* -------------------------------------------------------------------------*/
snprintf(title, sizeof(title), "Top %d IP Address Activity by %s", top_count, order_by);
pagehead(title);
fprintf(cgiOut, "<div id=\"content\">\n");
fprintf(cgiOut, "<p>\n");
fprintf(cgiOut, "<b>Top:</b> %d <b>Selection:</b> %s <b>Timeperiod:</b> %s %s - %s %s <b>Data Records:</b> %d",
top_count, order_by, start_date, start_time, end_date, end_time, allrows);
fprintf(cgiOut, "</p>\n");
fprintf(cgiOut, "<table class=\"inner\" width=100%%>\n");
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<th class=\"inner\">#</th>\n");
fprintf(cgiOut, "<th class=\"inner\">IP Address</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Data In</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Data Out</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Throughput</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Packets In</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Packets Out</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Elapsed Time</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Sessions</th>\n");
fprintf(cgiOut, "</tr>\n");
/* fetch the data */
#ifdef ORACLE_DB
while ( SQLO_SUCCESS == (stat = (sqlo_fetch(sth1, 1)))) {
/* get one record */
values = sqlo_values(sth1, NULL, 1);
#endif
#ifdef MYSQL_DB
while((values = mysql_fetch_row(result)) != NULL) {
#endif
rowcount++;
/* check for even/odd rows */
oddline_calc = div(rowcount, 2);
if(oddline_calc.rem) fprintf(cgiOut, "<tr class=\"odd\">\n");
else fprintf(cgiOut, "<tr class=\"even\">\n");
fprintf(cgiOut, "<td>%d</td>\n", rowcount);
fprintf(cgiOut, "<td>");
fprintf(cgiOut, "<a href=ip-actlast.cgi?start=%d&sort_order=%s&ipaddr=%s&order_by=start_date>",
period, sort_order, values[0]);
fprintf(cgiOut, "%s</a></td>", values[0]);
fprintf(cgiOut, "<td class=\"right\">%s</td>", calc_units(values[1], dataunit));
fprintf(cgiOut, "<td class=\"right\">%s</td>", calc_units(values[2], dataunit));
fprintf(cgiOut, "<td class=\"right\">%s/s</td>", calc_units(values[3], dataunit));
fprintf(cgiOut, "<td class=\"right\">%s</td>", values[4]);
fprintf(cgiOut, "<td class=\"right\">%s</td>", values[5]);
fprintf(cgiOut, "<td class=\"right\">%s</td>", values[6]);
fprintf(cgiOut, "<td class=\"right\">%s</td>", values[7]);
fprintf(cgiOut, "</tr>\n");
if ( rowcount == top_count) break;
} /* end while row */
#ifdef ORACLE_DB
if (SQLO_SUCCESS != sqlo_close(sth1))
cgi_error("Error Closing the SQL statment handle.");
RETURN_ON_ABORT; /* finish if SIGINT was catched */
#endif
#ifdef MYSQL_DB
mysql_close(dbh);
#endif
/* ----------------------------------------------------------------- *
* IF there was no data for the selection, display a notification *
* ----------------------------------------------------------------- */
if(rowcount == 0) {
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<td colspan=9>");
fprintf(cgiOut, "No data found for top %d IP by %s between %s %s and %s %s.",
top_count, order_by, start_date, start_time, end_date, end_time);
fprintf(cgiOut, "</td>\n");
fprintf(cgiOut, "</tr>\n");
} /* end if rowcount is zero */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<th class=\"inner\" colspan=9>");
fprintf(cgiOut, " ");
fprintf(cgiOut, "</th>\n");
fprintf(cgiOut, "</tr>\n");
fprintf(cgiOut, "</table>\n");
} /* end else we were called with form data */
pageside();
pagefoot();
return(0);
}
int open(struct libdb *dbp)
{
if(NULL != dbp)
{
int dbType = dbp->db_type;
int result = 0;
switch(dbType)
{
case DB_ORACLE://oracle database connect
{
if(is_open(dbp))
{
result = 1;
break;//The connection is built up, you do not need to repeat the connection
}
if(dbp->db_orap != NULL)
{
int ret, len;
struct oracle *orap = dbp->db_orap;
ret = sqlo_init(orap->ora_mode, orap->ora_num, orap->ora_cur);
if(SQLO_SUCCESS != ret)
{
result = 0;
set_error_d(dbp, "Failed to initialize the libsqlora8 Library:");
break;
}
char *connp = NULL;
analyse(dbp, connp);
if((len=strlen(connp))<=0)
break;
connp[len-1]='\0';
sqlo_db_handle_t *dbhp = &dbp->db_orap->ora_dbhp;
ret = sqlo_connect(dbhp, connp);
if(SQLO_SUCCESS != ret)
{
const char *error = sql_geterror();
result = 0;
dbp->db_errorp = "Failed to initialize the oracle Library:%s";
int alen = len+strlen(error);
dbp->db_errorp.resize(alen+1);
snprintf(&dbp->db_errorp[0], alen, "Failed to initialize the oracle Library:%s", error);
break;
}
sqlo_set_autocommit(dbp->db_orap->ora_dbhp, SQLO_OFF);
result = 1;
}
}
break;
case DB_MYSQL://MySql database connect
{
if(is_open(dbp))
{
result = 1;
break;//The connection is built up, you do not need to repeat the connection
}
if(dbp->db_sqlp != NULL)
{
MYSQL *retp = NULL ;
retp = mysql_init(dbp->db_sqlp->mys_sqlp);
if(NULL == retp)
{
result = 0;
set_error_d(dbp, "mysql_init");
break;
}
char *connp = NULL;
analyse(dbp, connp);
struct mysql *sqlp = dbp->db_sqlp;
retp = mysql_real_connect(
sqlp->mys_sqlp, //pointer to MySql
sqlp->mys_hostp, //host name
sqlp->mys_userp, //user name
sqlp->mys_pwdp, //user password
sqlp->mys_dbp, //database name
sqlp->mys_port, //the port
sqlp->mys_socketp, //the socket
sqlp->mys_flags //the flags of client
);
if(NULL == retp)
{
result = 0;
set_error_d(dbp, "mysql_real_connect");
break;
}
int ret = -1;
ret = mysql_autocommit(dbp->db_sqlp->mys_sqlp, 0);
if(0 != ret)
{
result = 0;
set_error_d(dbp, "mysql_autocommit");
break;
}
result = 1;
}
}
break;
default:
{
result = 0;
set_error_d(dbp, "The system is not support the database.");
}
break;
}
return result;
}
return 0;
}
/*
* Connect to the database.
* If fails, program terminates.
*/
int zbx_db_connect(char *host, char *user, char *password, char *dbname, char *dbsocket, int port)
{
int ret = ZBX_DB_OK;
#ifdef HAVE_SQLITE3
char *p, *path;
#endif /* HAVE_SQLITE3 */
#ifdef HAVE_MYSQL
/* For MySQL >3.22.00 */
/* if( ! mysql_connect( conn, NULL, dbuser, dbpassword ) )*/
conn = mysql_init(NULL);
if (!mysql_real_connect(conn, host, user, password, dbname, port, dbsocket, CLIENT_MULTI_STATEMENTS))
{
zabbix_errlog(ERR_Z3001, dbname, mysql_errno(conn), mysql_error(conn));
ret = ZBX_DB_FAIL;
}
if (ZBX_DB_OK == ret)
{
if (0 != mysql_select_db(conn, dbname))
{
zabbix_errlog(ERR_Z3001, dbname, mysql_errno(conn), mysql_error(conn));
ret = ZBX_DB_FAIL;
}
}
if (ZBX_DB_OK == ret)
DBexecute("SET CHARACTER SET utf8");
if (ZBX_DB_FAIL == ret)
{
switch (mysql_errno(conn)) {
case CR_CONN_HOST_ERROR:
case CR_SERVER_GONE_ERROR:
case CR_CONNECTION_ERROR:
case CR_SERVER_LOST:
case ER_SERVER_SHUTDOWN:
case ER_UNKNOWN_ERROR:
ret = ZBX_DB_DOWN;
break;
default:
break;
}
}
return ret;
#endif
#ifdef HAVE_POSTGRESQL
char *cport = NULL;
DB_RESULT result;
DB_ROW row;
int sversion;
if( port ) cport = zbx_dsprintf(cport, "%i", port);
conn = PQsetdbLogin(host, cport, NULL, NULL, dbname, user, password );
zbx_free(cport);
/* check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
zabbix_errlog(ERR_Z3001, dbname, 0, PQerrorMessage(conn));
ret = ZBX_DB_FAIL;
}
result = DBselect("select oid from pg_type where typname = 'bytea'");
row = DBfetch(result);
if(row)
{
ZBX_PG_BYTEAOID = atoi(row[0]);
}
DBfree_result(result);
#ifdef HAVE_FUNCTION_PQSERVERVERSION
sversion = PQserverVersion(conn);
zabbix_log(LOG_LEVEL_DEBUG, "PostgreSQL Server version: %d", sversion);
#else
sversion = 0;
#endif /* HAVE_FUNCTION_PQSERVERVERSION */
if (sversion >= 80100)
{
/* disable "nonstandard use of \' in a string literal" warning */
DBexecute("set escape_string_warning to off");
}
return ret;
#endif
#ifdef HAVE_ORACLE
char connect[MAX_STRING_LEN];
zbx_strlcpy(connect, user, sizeof(connect));
if (password && *password) {
zbx_strlcat(connect, "/", sizeof(connect));
zbx_strlcat(connect, password, sizeof(connect));
if (dbname && *dbname) {
zbx_strlcat(connect, "@", sizeof(connect));
zbx_strlcat(connect, dbname, sizeof(connect));
}
}
if (SQLO_SUCCESS != sqlo_init(SQLO_OFF, 1, 100)) {
zabbix_errlog(ERR_Z3001, connect, 0, "Failed to init libsqlora8");
ret = ZBX_DB_FAIL;
}
if (ZBX_DB_OK == ret) {
/* login */ /* TODO: how to use port??? */
if (SQLO_SUCCESS != sqlo_connect(&oracle, connect)) {
zabbix_errlog(ERR_Z3001, connect, 0, "sqlo_connect");
ret = ZBX_DB_FAIL;
}
}
if (ZBX_DB_OK == ret)
sqlo_autocommit_off(oracle);
return ret;
#endif
#ifdef HAVE_SQLITE3
/* check to see that the backend connection was successfully made */
if (SQLITE_OK != (ret = sqlite3_open(dbname, &conn))) {
zabbix_errlog(ERR_Z3001, dbname, 0, sqlite3_errmsg(conn));
exit(FAIL);
}
/* Do not return SQLITE_BUSY immediately, wait for N ms */
sqlite3_busy_timeout(conn, 60*1000);
sqlite_transaction_started = 0;
path = strdup(dbname);
if (NULL != (p = strrchr(path, '/')))
*++p = '\0';
else
*path = '\0';
DBexecute("PRAGMA synchronous = 0"); /* OFF */
DBexecute("PRAGMA temp_store = 2"); /* MEMORY */
DBexecute("PRAGMA temp_store_directory = '%s'", path);
zbx_free(path);
return ret;
#endif
}
int cgiMain() {
#ifdef MYSQL_DB
static MYSQL *dbh; /* database connect handle */
static MYSQL_RES *result; /* database query results */
static MYSQL_ROW values; /* query data returned */
unsigned int colcount =0; /* number of returned columns */
int server_version; /* returned server version */
#endif
#ifdef ORACLE_DB
sqlo_db_handle_t dbh; /* database handle */
sqlo_stmt_handle_t sth1; /* statement handle 1 */
char server_version[1024]=""; /* string for returned server version */
int stat =0; /* status of sqlo calls */
int handle =0; /* handle of the interrupt handler */
//const char ** colnames; /* column names */
const char ** values; /* values */
#endif
char sqlquery_str[1024] =""; /* SQL query string */
char **form_data; /* string array for query data */
char username[49] =""; /* selected username */
char first_start_date[11]=""; /* first connect start date */
char first_start_time[6] =""; /* first connect start time */
char first_end_date[11] =""; /* first connect end date */
char first_end_time[6] =""; /* first connect end time */
char last_start_date[11] =""; /* last connect start date */
char last_start_time[6] =""; /* last connect start time */
char last_end_date[11] =""; /* last connect end date */
char last_end_time[6] =""; /* last connect end time */
char title[256] = ""; /* cgi title string */
int allrows =0; /* number of returned rows */
int rowcount =0; /* row iteration counter */
div_t oddline_calc; /* calculates even/odd row color */
char err_str[2048] =""; /* use for combined error string */
_abort_flag = 0;
/* we load the cgi form values into form_data */
if (cgiFormEntries(&form_data) != cgiFormSuccess)
/* ------------------------------------------------------------------- *
* If we are not called with arguments, we display a error message. *
* ------------------------------------------------------------------- */
cgi_error("Error: Could not retrieve form data.");
/* ------------------------------------------------------------------- *
* check if we got all information to make the SQL query *
* --------------------------------------------------------------------*/
if ( cgiFormString("username", username, sizeof(username))
!= cgiFormSuccess )
cgi_error("Error retrieving the username.");
#ifdef ORACLE_DB
/* ------------------------------------------------------------------- *
* ORACLE_HOME is needed for OCI8 to find tnsnames.ora *
* ------------------------------------------------------------------- */
putenv(WEB_ORACLE_ENV);
/* initialize the connection */
if (SQLO_SUCCESS != sqlo_init(SQLO_OFF, 1, 100))
cgi_error("Error: Failed to init libsqlora8.");
/* register the interrupt handler */
sqlo_register_int_handler(&handle, sigint_handler);
/* login to the database */
if (SQLO_SUCCESS != sqlo_connect(&dbh, WEB_TNS_STRING))
cgi_error("Error: Cannot connect to database.");
RETURN_ON_ABORT; /* finish if SIGINT was catched */
if (SQLO_SUCCESS != sqlo_server_version(dbh, server_version,
sizeof(server_version)))
cgi_error(sqlo_geterror(dbh));
RETURN_ON_ABORT; /* finish if SIGINT was catched */
/* enable autocommit, each statement is commited as a single transaction */
stat = sqlo_set_autocommit(dbh, 1);
/* define the SQL query */
snprintf(sqlquery_str, sizeof(sqlquery_str), "SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s FROM %s.EDACS_REMOTE WHERE USERNAME='******' ORDER BY IP_OR_PHONE",
"IP_OR_PHONE", /* 00 */
"TO_CHAR(FIRST_CONNECT, 'dd-mm-yyyy')", /* 01 */
"TO_CHAR(FIRST_CONNECT, 'hh24:mi:ss')", /* 02 */
"TO_CHAR(FIRST_CONNECT+INTERVAL '1' MINUTE, 'dd-mm-yyyy')", /* 03 */
"TO_CHAR(FIRST_CONNECT+INTERVAL '1' MINUTE, 'hh24:mi')", /* 04 */
"TO_CHAR(LAST_CONNECT, 'dd-mm-yyyy')", /* 05 */
"TO_CHAR(LAST_CONNECT, 'hh24:mi:ss')", /* 06 */
"TO_CHAR(LAST_CONNECT+INTERVAL '1' MINUTE, 'dd-mm-yyyy')", /* 07 */
"TO_CHAR(LAST_CONNECT+INTERVAL '1' MINUTE, 'hh24:mi')", /* 08 */
EDACSADMIN, username);
// cgi_error(sqlquery_str); /* DEBUG output of the SQL string */
/* initialize the statement handle */
sth1 = SQLO_STH_INIT;
/* opens a cursor for the query statement */
if ( 0 > (sqlo_open2(&sth1, dbh, sqlquery_str, 0, NULL)))
cgi_error(sqlo_geterror(dbh));
RETURN_ON_ABORT; /* finish if SIGINT was catched */
/* get the output column names */
//if (SQLO_SUCCESS != sqlo_ocol_names2(sth1, &colcount, &colnames))
// cgi_error("Error getting the DB columns with sqlo_ocol_names2()");
//RETURN_ON_ABORT; /* finish if SIGINT was catched */
#endif
#ifdef MYSQL_DB
/* initialize the connection */
dbh = mysql_init(NULL);
if(dbh == NULL) cgi_error("Error: Failed to init MySQL DB.");
/* login to the database */
if (mysql_real_connect(dbh, MYSQLIP, EDACSADMIN, ADMIN_PASS, DB_NAME, DB_PORT, NULL, 0) == 0)
cgi_error("Error: Cannot connect to database.");
/* Get the database version */
server_version = mysql_get_server_version(dbh);
/* create the SQL query string */
snprintf(sqlquery_str, sizeof(sqlquery_str), "SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s FROM edacs_remote WHERE username='******' ORDER BY ip_or_phone",
"ip_or_phone", /* 00 */
"DATE_FORMAT(first_connect, '%d-%m-%Y')", /* 01 */
"DATE_FORMAT(first_connect, '%H:%i:%s')", /* 02 */
"DATE_FORMAT(first_connect+INTERVAL '1' MINUTE, '%d-%m-%Y')", /* 03 */
"DATE_FORMAT(first_connect+INTERVAL '1' MINUTE, '%H:%i')", /* 04 */
"DATE_FORMAT(last_connect, '%d-%m-%Y')", /* 05 */
"DATE_FORMAT(last_connect, '%H:%i:%s')", /* 06 */
"DATE_FORMAT(last_connect+INTERVAL '1' MINUTE, '%d-%m-%Y')", /* 07 */
"DATE_FORMAT(last_connect+INTERVAL '1' MINUTE, '%H:%i')", /* 08 */
username);
// cgi_error(sqlquery_str); /* DEBUG output of the SQL string */
/* Prepare and execute the SQL statement */
if(mysql_query(dbh, sqlquery_str) != 0) {
if(DEBUG == 0) cgi_error(mysql_error(dbh));
else snprintf(err_str, sizeof(err_str), "DB error %s\n\nQuery string %s",
mysql_error(dbh), sqlquery_str);
cgi_error(err_str);
}
/* get query results set */
result = mysql_store_result(dbh);
if (result == NULL) {
snprintf(err_str, sizeof(err_str), "No results for query: %s\n", sqlquery_str);
cgi_error( err_str);
}
allrows = mysql_num_rows(result);
colcount = mysql_num_fields(result);
#endif
/* -------------------------------------------------------------------------- *
* start the html output *
* ---------------------------------------------------------------------------*/
/* define the CGI title */
snprintf(title, sizeof(title), "User Information for '%s'", username);
pagehead(title);
fprintf(cgiOut, "<div id=\"content\">\n");
fprintf(cgiOut, "<table class=\"inner\" width=100%%>\n");
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<th class=\"inner\">#</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Remote IP / Phone</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Active Since</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Last Update</th>\n");
fprintf(cgiOut, "</tr>\n");
/* fetch the data */
#ifdef ORACLE_DB
while ( SQLO_SUCCESS == (stat = (sqlo_fetch(sth1, 1)))) {
/* get one record */
values = sqlo_values(sth1, NULL, 1);
#endif
#ifdef MYSQL_DB
while((values = mysql_fetch_row(result)) != NULL) {
#endif
rowcount++;
/* check for even/odd rows */
oddline_calc = div(rowcount, 2);
if(oddline_calc.rem) fprintf(cgiOut, "<tr class=\"odd\">\n");
else fprintf(cgiOut, "<tr class=\"even\">\n");
/* calculate start and end times for link to session query */
strncpy(first_start_date, values[1], sizeof(first_start_date)-1);
first_start_date[2] = '.';
first_start_date[5] = '.';
first_start_date[10] = '\0'; /* strncpy does not terminate the string, therefore we have to */
strncpy(first_start_time, values[2], sizeof(first_start_time)-1);
first_start_time[5] = '\0'; /* strncpy does not terminate the string, therefore we have to */
strncpy(first_end_date, values[3], sizeof(first_end_date)-1);
first_end_date[2] = '.';
first_end_date[5] = '.';
first_end_date[10] = '\0'; /* strncpy does not terminate the string, therefore we have to */
strncpy(first_end_time, values[4], sizeof(first_end_time)-1);
first_end_time[5] = '\0'; /* strncpy does not terminate the string, therefore we have to */
strncpy(last_start_date, values[5], sizeof(last_start_date)-1);
last_start_date[2] = '.';
last_start_date[5] = '.';
last_start_date[10] = '\0'; /* strncpy does not terminate the string, therefore we have to */
strncpy(last_start_time, values[6], sizeof(last_start_time)-1);
last_start_time[5] = '\0'; /* strncpy does not terminate the string, therefore we have to */
strncpy(last_end_date, values[7], sizeof(last_end_date)-1);
last_end_date[2] = '.';
last_end_date[5] = '.';
last_end_date[10] = '\0'; /* strncpy does not terminate the string, therefore we have to */
strncpy(last_end_time, values[8], sizeof(last_end_time)-1);
last_end_time[5] = '\0'; /* strncpy does not terminate the string, therefore we have to */
fprintf(cgiOut, "<td>%d</td>\n", rowcount);
fprintf(cgiOut, "<td>%s</td>", values[0]);
fprintf(cgiOut, "<td>");
fprintf(cgiOut, "<a href=user-acttime.cgi?start_date=%s&start_time=%s&end_date=%s&end_time=%s&sort_order=asc&username=%s&order_by=start_date>", first_start_date, first_start_time, first_end_date, first_end_time, username);
fprintf(cgiOut, "%s %s</a></td>", values[1], values[2]);
fprintf(cgiOut, "<td>");
fprintf(cgiOut, "<a href=user-acttime.cgi?start_date=%s&start_time=%s&end_date=%s&end_time=%s&sort_order=asc&username=%s&order_by=start_date&select_by=stop_date>", last_start_date, last_start_time, last_end_date, last_end_time, username);
fprintf(cgiOut, "%s %s</a></td>", values[5], values[6]);
fprintf(cgiOut, "</tr>\n");
}
#ifdef ORACLE_DB
if (SQLO_SUCCESS != sqlo_close(sth1))
cgi_error("Error Closing the SQL statment handle.");
RETURN_ON_ABORT; /* finish if SIGINT was catched */
#endif
#ifdef MYSQL_DB
mysql_close(dbh);
#endif
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<th class=\"inner\" colspan=4>");
fprintf(cgiOut, " ");
fprintf(cgiOut, "</th>\n");
fprintf(cgiOut, "</tr>\n");
fprintf(cgiOut, "</table>\n");
fprintf(cgiOut, "<h3>Additional Information</h3>\n");
fprintf(cgiOut, "<hr>\n");
fprintf(cgiOut, "<p>");
fprintf(cgiOut, "This list represents all remote IP addresses or telephone numbers this user connected from.");
fprintf(cgiOut, "<ul>");
fprintf(cgiOut, "<li>The \"Remote IP / Phone\" is the remote ISP IP address of a user in case of a VPN connection, or his telephone number reported when connecting via dial-up. If the value is unknown, the connection came from a line that has caller-ID supression or is a plain old analog modem line.");
fprintf(cgiOut, "<li>The \"Active Since\" is the first time session information was received. The time links to the first recorded session for this user, coming from this particular remote IP or phone.");
fprintf(cgiOut, "<li>The \"Last Update\" time shows when the latest session record was received. The link tries to find the latest session. Sometimes the session is still in progress and incomplete or no data is returned.");
fprintf(cgiOut, "</ul>");
fprintf(cgiOut, "</p>\n");
pageside();
pagefoot();
return(0);
}
int cgiMain() {
#ifdef MYSQL_DB
static MYSQL *dbh; /* database connect handle */
static MYSQL_RES *result; /* database query results */
static MYSQL_ROW values; /* query data returned */
unsigned int colcount =0; /* number of returned columns */
int server_version; /* returned server version */
#endif
#ifdef ORACLE_DB
sqlo_db_handle_t dbh; /* database handle */
sqlo_stmt_handle_t sth1; /* statement handle 1 */
char server_version[1024]=""; /* string for returned server version */
int stat =0; /* status of sqlo calls */
int handle =0; /* handle of the interrupt handler */
const char ** values; /* values */
#endif
char sqlquery_str[1024] =""; /* SQL query string */
int allrows =0; /* number of returned rows */
int rowcount =0; /* row iteration counter */
div_t oddline_calc; /* calculates even/odd row color */
char ipaddr[16] =""; /* selected IP address */
char start_date[11] =""; /* selected start date */
char start_time[6] =""; /* selected start time */
char end_date[11] =""; /* selected end date */
char end_time[6] =""; /* selected end time */
char order_by[13] =""; /* sort list by column */
char sort_order[5] =""; /* ascending or descending */
char **form_data; /* string array for query data */
char title[256] =""; /* cgi title string */
struct tm *tm_ptr; /* containing time structure */
time_t now, old; /* containing timestamp */
char err_str[2048] =""; /* use for combined error string */
int period = 0; /* the period to display */
char dataunit[255] = "0 Bytes"; /* holds the calculated KB/MB */
unsigned long long sum_bin = 0; /* summary of all bytes in */
unsigned long long sum_bout = 0; /* summary of all bytes out */
unsigned long long sum_ball = 0; /* summary of all bytes total */
char sum_buf[255] = "0"; /* summary string buffer */
_abort_flag = 0;
#ifdef ORACLE_DB
/* ------------------------------------------------------------------- *
* ORACLE_HOME is needed for OCI8 to find tnsnames.ora *
* ------------------------------------------------------------------- */
putenv(WEB_ORACLE_ENV);
/* initialize the connection */
if (SQLO_SUCCESS != sqlo_init(SQLO_OFF, 1, 100))
cgi_error("Error: Failed to init libsqlora8.");
/* register the interrupt handler */
sqlo_register_int_handler(&handle, sigint_handler);
/* login to the database */
if (SQLO_SUCCESS != sqlo_connect(&dbh, WEB_TNS_STRING))
cgi_error("Error: Cannot connect to database.");
RETURN_ON_ABORT; /* finish if SIGINT was catched */
if (SQLO_SUCCESS != sqlo_server_version(dbh, server_version,
sizeof(server_version)))
cgi_error(sqlo_geterror(dbh));
RETURN_ON_ABORT; /* finish if SIGINT was catched */
/* enable autocommit, each statement is commited as a single transaction */
stat = sqlo_set_autocommit(dbh, 1);
#endif
#ifdef MYSQL_DB
/* initialize the connection */
dbh = mysql_init(NULL);
if(dbh == NULL) cgi_error("Error: Failed to init MySQL DB.");
/* login to the database */
if (mysql_real_connect(dbh, MYSQLIP, EDACSADMIN, ADMIN_PASS, DB_NAME, DB_PORT, NULL, 0) == 0)
cgi_error("Error: Cannot connect to database.");
/* Get the database version */
server_version = mysql_get_server_version(dbh);
#endif
/* we load the cgi form values into form_data */
if (cgiFormEntries(&form_data) != cgiFormSuccess)
cgi_error("Error: Could not retrieve form data.");
if(form_data[0] == NULL) {
/* ------------------------------------------------------------------- *
* Start the HTML output to display the query selection *
* ------------------------------------------------------------------- */
/* define the CGI title */
snprintf(title, sizeof(title), "Latest IP Address Session Activity");
pagehead(title);
fprintf(cgiOut, "<div id=\"content\">\n");
fprintf(cgiOut, "<form action=\"ip-actlast.cgi\" method=\"get\">\n");
fprintf(cgiOut, "<table class=\"inner\">\n");
/* 1st row, display headers */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<th class=\"inner\" width=150>");
fprintf(cgiOut, "IP Address</th>");
fprintf(cgiOut, "<th class=\"inner\" width=150>");
fprintf(cgiOut, "Time Frame</th>");
fprintf(cgiOut, "<th class=\"inner\" width=150>");
fprintf(cgiOut, "Order By</th>");
fprintf(cgiOut, "<th class=\"inner\" width=150>");
fprintf(cgiOut, "Sort Order</th>");
fprintf(cgiOut, "</tr>\n");
/* 2nd row */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "<td class=\"inner\">");
fprintf(cgiOut, "<input type=radio value=\"6\" checked name=\"start\"> Last 6 Hours</td>");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "</tr>\n");
/* 3rd row */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<td class=\"inner-ctr\">");
fprintf(cgiOut, "(192.168.111.222)</td>");
fprintf(cgiOut, "<td class=\"inner\">");
fprintf(cgiOut, "<input type=radio value=\"12\" name=\"start\"> Last 12 Hours</td>");
fprintf(cgiOut, "<td class=\"inner-ctr\"> (choose one)</td>");
fprintf(cgiOut, "<td class=\"inner\">");
fprintf(cgiOut, "<input type=radio value=\"asc\" checked name=\"sort_order\">");
fprintf(cgiOut, " Ascending</td>");
fprintf(cgiOut, "</tr>\n");
/* 4th row, request values */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<td class=\"inner-ctr\">");
fprintf(cgiOut, "<input type=text name=\"ipaddr\" size=\"15\"></td>");
fprintf(cgiOut, "<td class=\"inner\">");
fprintf(cgiOut, "<input type=radio value=\"24\" name=\"start\"> Last 24 Hours</td>");
fprintf(cgiOut, "<td class=\"inner-ctr\"><select name=\"order_by\" size=\"1\">");
fprintf(cgiOut, "<option value=\"router\">Router</option>");
fprintf(cgiOut, "<option value=\"service\">Service</option>");
fprintf(cgiOut, "<option value=\"ip_or_phone\">IP or Phone</option>");
fprintf(cgiOut, "<option selected value=\"start_date\">Start Date</option>");
fprintf(cgiOut, "<option value=\"stop_date\">Stop Date</option>");
fprintf(cgiOut, "<option value=\"elapsed_mins\">Elapsed Time</option>");
fprintf(cgiOut, "<option value=\"bytes_in\">Bytes In</option>");
fprintf(cgiOut, "<option value=\"bytes_out\">Bytes Out</option>");
fprintf(cgiOut, "<option value=\"throughput\">Throughput</option>");
fprintf(cgiOut, "</select></td>");
fprintf(cgiOut, "<td class=\"inner\">");
fprintf(cgiOut, "<input type=radio name=\"sort_order\" value=\"desc\"> Descending</td>");
fprintf(cgiOut, "</tr>\n");
/* 5th row */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "<td class=\"inner\">");
fprintf(cgiOut, "<input type=radio value=\"168\" name=\"start\"> Last Week</td>");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "<td class=\"inner\"></td>");
fprintf(cgiOut, "</tr>\n");
/* 6th and last row, close the frame */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<th class=\"inner\" colspan=4>");
fprintf(cgiOut, "<input type=submit value=\"Run Query\"></th>");
fprintf(cgiOut, "</tr>\n");
fprintf(cgiOut, "</table>\n");
fprintf(cgiOut, "</form>\n");
fprintf(cgiOut, "<h3>Additional Information</h3>\n");
fprintf(cgiOut, "<hr>\n");
fprintf(cgiOut, "<p>\n");
fprintf(cgiOut, "This query returns the list of user sessions for this IP address during the last time period.");
fprintf(cgiOut, "<ul>");
fprintf(cgiOut, "<li>Type the IP address into the text field. If unsure, query the last sessions to see which IP's are given out.");
fprintf(cgiOut, "<li>The time frame can be selected from the radio menu, time is counting back from now.");
fprintf(cgiOut, "<li>Choosing a large time frame can result in a long query and a very large result set (thousands of rows).");
fprintf(cgiOut, "<li>The results list can be ordered using criteria from the \"Order By\" drop down list.");
fprintf(cgiOut, "</ul></font>");
fprintf(cgiOut, "</p>\n");
pageside();
} /* end if for displaying the query request */
else {
/* ------------------------------------------------------------------- *
* check if we got all information to make the SQL query *
* --------------------------------------------------------------------*/
if ( cgiFormString("ipaddr", ipaddr, sizeof(ipaddr))
!= cgiFormSuccess )
cgi_error("Error retrieving the IP address.");
if ( cgiFormIntegerBounded( "start", &period, 1, 2160, 6)
!= cgiFormSuccess )
cgi_error("Error retrieving start period information.");
if ( cgiFormString("order_by", order_by, sizeof(order_by))
!= cgiFormSuccess )
cgi_error("Error retrieving order_by information.");
if ( cgiFormString("sort_order", sort_order, sizeof(sort_order))
!= cgiFormSuccess )
cgi_error("Error retrieving sort_order information.");
/* ------------------------------------------------------------------- *
* The calculate query start and end time from given period in hours *
* ------------------------------------------------------------------- */
now = time(NULL);
tm_ptr = localtime(&now);
strftime(end_date, sizeof(end_date), "%d.%m.%Y", (tm_ptr));
strftime(end_time, sizeof(end_time), "%H:%M", tm_ptr);
old = time(NULL) - (period * 3600);
tm_ptr = localtime(&old);
strftime(start_date, sizeof(start_date), "%d.%m.%Y", tm_ptr);
strftime(start_time, sizeof(start_time), "%H:%M", tm_ptr);
/* ------------------------------------------------------------------- *
* check we got all parts and can start doing the SQL query below *
* --------------------------------------------------------------------*/
#ifdef ORACLE_DB
snprintf(sqlquery_str, sizeof(sqlquery_str), "SELECT USERNAME, ROUTER, SERVICE, IP_OR_PHONE, IP_ADDR, %s, %s, ELAPSED_MINS_STR, TTY, BYTES_IN_STR, BYTES_OUT_STR, PACKETS_IN_STR, PACKETS_OUT_STR, KBS_STR FROM %s.V_EDACS WHERE IP_ADDR = '%s' AND START_DATE BETWEEN TO_DATE('%s %s', 'dd.mm.yyyy hh24:mi') and TO_DATE ('%s %s', 'dd.mm.yyyy hh24:mi') ORDER BY %s %s",
"TO_CHAR(START_DATE, 'dd-mm-yyyy hh24:mi:ss')",
"TO_CHAR(STOP_DATE, 'dd-mm-yyyy hh24:mi:ss')",
EDACSADMIN, ipaddr, start_date, start_time, end_date,
end_time, order_by, sort_order);
/* initialize the statement handle */
sth1 = SQLO_STH_INIT;
/* opens a cursor for the query statement */
if ( 0 > (sqlo_open2(&sth1, dbh, sqlquery_str, 0, NULL))) {
if(DEBUG == 0) cgi_error(sqlo_geterror(dbh));
else snprintf(err_str, sizeof(err_str), "DB error %s\n\nQuery string %s",
sqlo_geterror(dbh), sqlquery_str);
cgi_error(err_str);
}
RETURN_ON_ABORT; /* finish if SIGINT was catched */
/* get the output column names */
//if (SQLO_SUCCESS != sqlo_ocol_names2(sth1, &colcount, &colnames))
// cgi_error("Error getting the DB columns with sqlo_ocol_names2()");
//RETURN_ON_ABORT; /* finish if SIGINT was catched */
#endif
#ifdef MYSQL_DB
snprintf(sqlquery_str, sizeof(sqlquery_str), "SELECT username, router, service, ip_or_phone, ip_addr, %s, %s, elapsed_mins_str, bytes_in, bytes_out, throughput FROM v_edacs WHERE ip_addr = '%s' AND start_date BETWEEN STR_TO_DATE('%s %s', '%s') and STR_TO_DATE('%s %s', '%s') ORDER BY %s %s",
"DATE_FORMAT(start_date, '%d-%m-%Y %H:%i:%s')",
"DATE_FORMAT(stop_date, '%d-%m-%Y %H:%i:%s')",
ipaddr,
start_date, start_time, "%d.%m.%Y %H:%i",
end_date, end_time, "%d.%m.%Y %H:%i",
order_by, sort_order);
/* Prepare and execute the SQL statement */
if(mysql_query(dbh, sqlquery_str) != 0) {
if(DEBUG == 0) cgi_error(mysql_error(dbh));
else snprintf(err_str, sizeof(err_str), "DB error %s\n\nQuery string %s",
mysql_error(dbh), sqlquery_str);
cgi_error(err_str);
}
/* get query results set */
result = mysql_store_result(dbh);
if (result == NULL) {
snprintf(err_str, sizeof(err_str), "No results for query: %s\n", sqlquery_str);
cgi_error( err_str);
}
allrows = mysql_num_rows(result);
colcount = mysql_num_fields(result);
#endif
/* ------------------------------------------------------------------------ *
* start the html output *
* -------------------------------------------------------------------------*/
snprintf(title, sizeof(title), "Latest Session Activity for IP Address %s", ipaddr);
pagehead(title);
fprintf(cgiOut, "<div id=\"content-wide\">\n");
fprintf(cgiOut, "<p>\n");
fprintf(cgiOut, "<b>IP Address:</b> %s <b>Timeperiod:</b> %s %s - %s %s <b>Data Records:</b> %d",
ipaddr, start_date, start_time, end_date, end_time, allrows);
fprintf(cgiOut, "</p>\n");
fprintf(cgiOut, "<table class=\"inner\" width=100%%>\n");
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<th class=\"inner\">#</th>\n");
fprintf(cgiOut, "<th class=\"inner\">User</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Router</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Service</th>\n");
fprintf(cgiOut, "<th class=\"inner\">IP / Phone</th>\n");
fprintf(cgiOut, "<th class=\"inner\">IP Address</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Session Start</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Session End</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Duration</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Data In</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Data Out</th>\n");
fprintf(cgiOut, "<th class=\"inner\">Throughput</th>\n");
fprintf(cgiOut, "</tr>\n");
/* fetch the data */
#ifdef ORACLE_DB
while ( SQLO_SUCCESS == (stat = (sqlo_fetch(sth1, 1)))) {
/* get one record */
values = sqlo_values(sth1, NULL, 1);
#endif
#ifdef MYSQL_DB
while((values = mysql_fetch_row(result)) != NULL) {
#endif
rowcount++;
/* check for even/odd rows */
oddline_calc = div(rowcount, 2);
if(oddline_calc.rem) fprintf(cgiOut, "<tr class=\"odd\">\n");
else fprintf(cgiOut, "<tr class=\"even\">\n");
/* calculate transer data sums */
if (values[8]) sum_bin = sum_bin + atoll(values[8]);
if (values[9]) sum_bout = sum_bout + atoll(values[9]);
fprintf(cgiOut, "<td>%d</td>\n", rowcount);
fprintf(cgiOut, "<td>");
if (values[0]) fprintf(cgiOut, "%s</td>\n", values[0]); else fprintf(cgiOut, " </td>");
fprintf(cgiOut, "<td>");
if (values[1]) fprintf(cgiOut, "%s</td>\n", values[1]); else fprintf(cgiOut, " </td>");
fprintf(cgiOut, "<td>");
if (values[2]) fprintf(cgiOut, "%s</td>\n", values[2]); else fprintf(cgiOut, " </td>");
fprintf(cgiOut, "<td>");
if (values[3]) fprintf(cgiOut, "%s</td>\n", values[3]); else fprintf(cgiOut, " </td>");
fprintf(cgiOut, "<td>");
if (values[4]) fprintf(cgiOut, "%s</td>\n", values[4]); else fprintf(cgiOut, "none</td>");
fprintf(cgiOut, "<td>");
if (values[5]) fprintf(cgiOut, "%s</td>\n", values[5]); else fprintf(cgiOut, " </td>");
fprintf(cgiOut, "<td>");
if (values[6]) fprintf(cgiOut, "%s</td>\n", values[6]); else fprintf(cgiOut, "in progress</td>");
fprintf(cgiOut, "<td>");
if (values[7]) fprintf(cgiOut, "%s</td>\n", values[7]); else fprintf(cgiOut, " </td>");
fprintf(cgiOut, "<td>");
if (values[8]) fprintf(cgiOut, "%s</td>\n", calc_units(values[8], dataunit)); else fprintf(cgiOut, " </td>");
fprintf(cgiOut, "<td>");
if (values[9]) fprintf(cgiOut, "%s</td>\n", calc_units(values[9], dataunit)); else fprintf(cgiOut, " </td>");
fprintf(cgiOut, "<td>");
if (values[10]) fprintf(cgiOut, "%s/s</td>\n", calc_units(values[10], dataunit)); else fprintf(cgiOut, " </td>");
fprintf(cgiOut, "</tr>\n");
} /* end while fetch row data */
#ifdef ORACLE_DB
if (SQLO_SUCCESS != sqlo_close(sth1))
cgi_error("Error Closing the SQL statment handle.");
RETURN_ON_ABORT; /* finish if SIGINT was catched */
#endif
#ifdef MYSQL_DB
mysql_close(dbh);
#endif
/* ----------------------------------------------------------------- *
* IF there was no data for the selection, display a notification *
* ----------------------------------------------------------------- */
if(rowcount == 0) {
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<td colspan=12>");
fprintf(cgiOut, "No data found for IP address %s between %s %s and %s %s.",
ipaddr, start_date, start_time, end_date, end_time);
fprintf(cgiOut, "</td>\n");
fprintf(cgiOut, "</tr>\n");
} /* end if rowcount is zero */
fprintf(cgiOut, "<tr>\n");
fprintf(cgiOut, "<th class=\"inner\" colspan=12>Inbound Data Total:\n");
sprintf(sum_buf, "%llu", sum_bin);
fprintf(cgiOut, " %s Outbound Data Total:", calc_units(sum_buf, dataunit));
sprintf(sum_buf, "%llu", sum_bout);
fprintf(cgiOut, " %s Transfered Data Total:", calc_units(sum_buf, dataunit));
sum_ball = sum_ball + sum_bin + sum_bout;
sprintf(sum_buf, "%llu", sum_ball);
fprintf(cgiOut, " %s</th>\n", calc_units(sum_buf, dataunit));
fprintf(cgiOut, "</tr>\n");
fprintf(cgiOut, "</table>\n");
} /* end else we were called with form data */
pagefoot();
return(0);
}
int cgiMain() {
sqlo_db_handle_t dbh; /* database handle */
int stat; /* status of sqlo calls */
sqlo_stmt_handle_t sth; /* statement handle */
char server_version[1024]; /* string for returned server version */
char sqlquery_str[1024]; /* SQL query string */
int handle; /* handle of the interrupt handler */
const char ** v; /* values */
const char ** n; /* column names */
CONST int *nl; /* column name lengths */
CONST unsigned short *vl; /* value lengths */
int nc; /* number of columns */
unsigned int i, j; /* loop variable, why is it always i? */
/***********************************************************************/
/* ORACLE_HOME is needed for OCI8 to find tnsnames.ora and other stuff */
/***********************************************************************/
putenv(MY_ORACLEHOME_ENV);
/* initialize the connection */
if (SQLO_SUCCESS != sqlo_init(SQLO_OFF, 1, 100)) {
printf("Failed to init libsqlora8\n");
return EXIT_FAILURE;
}
/* register the interrupt handler */
sqlo_register_int_handler(&handle, sigint_handler);
/* login to the database */
if (SQLO_SUCCESS != sqlo_connect(&dbh, MY_TNS_STRING)) {
printf("Cannot login with %s\n", MY_TNS_STRING);
return EXIT_FAILURE;
}
RETURN_ON_ABORT; /* finish if SIGINT was catched */
if (SQLO_SUCCESS != sqlo_server_version(dbh, server_version,
sizeof(server_version))) {
printf("Failed to get the server version: %s\n", sqlo_geterror(dbh));
return EXIT_FAILURE;
}
RETURN_ON_ABORT; /* finish if SIGINT was catched */
printf("Connected to:\n%s\n\n", server_version);
/****************************************************************/
/* Normally we check if the table exists before we query. Since */
/* we test against DUAL, this is not a user but a system table */
/* and the test for DUAL existing in USER_TABLES would fail. */
/* */
/* Prepare and execute the SQL command here: */
/****************************************************************/
snprintf(sqlquery_str, sizeof(sqlquery_str), "SELECT * FROM %s", MY_TESTTABLE);
sth = SQLO_STH_INIT;
if ( 0 > (sqlo_open2(&sth, dbh, sqlquery_str, 0, NULL)))
error_exit(dbh, "sqlo_open");
/* get the output column names */
n = sqlo_ocol_names(sth, &nc);
/* get the output column name lengths */
nl = sqlo_ocol_name_lens(sth, NULL);
printf("number of output columns: %d \n\n", nc);
/* print the table column header(s) */
for (i = 0; i < nc; ++i) printf("%-*s ", nl[i], n[i]);
printf("\n");
for (i = 0; i < nc; ++i) {
for (j = 0; j < nl[i]; ++j) putchar('-');
putchar('+');
}
putchar('\n');
/* fetch the data */
while ( SQLO_SUCCESS == (stat = (sqlo_fetch(sth, 1)))) {
/* get one record */
v = sqlo_values(sth, NULL, 1);
/* get the length of the data items */
vl = sqlo_value_lens(sth, NULL);
/* print the column values */
for (i = 0; i < nc; ++i)
printf("%-*s ", (vl[i] > nl[i] ? vl[i] : nl[i]), v[i]);
printf("\n");
}
if (0 > stat)
error_exit(dbh, "sqlo_fetch");
if ( SQLO_SUCCESS != sqlo_close(sth))
error_exit(dbh, "sqlo_close");
return 1;
}