int OraDBRequest::getConditionerRequests(std::vector<request_t>* requests, const char* brand, int cluster_node, int status, int limit)
{
snprintf(_var_brand, sizeof(_var_brand), "%s", brand);
_var_cluster_node = cluster_node;
_var_status = status;
_var_limit = limit;
if (ora_force_execute(&_sth_select_conditioner, 0) < 0) {
LOG_CRITICAL("%s: Failed to EXECUTE SELECT_CONDITIONER_REQUEST."
" STATEMENT: \"%s\", LIBSQLORA ERROR: \"%s\"",
__func__, sqlo_command(_sth_select_conditioner), sqlo_geterror(_dbh));
//-- try to re-bind...
selectConditionerBind();
return -1;
}
if(_result == 0) {
return 0;
}
int n_fetched = 0;
n_fetched++;
requests->push_back(_request);
LOG_DEBUG("%s: request \"%d\" fetched from db.", __func__, _request.id);
while(n_fetched < limit && (ora_force_fetch(&_sth_select_conditioner) == 1)) {
requests->push_back(_request);
}
return requests->size();
}
/* print error and exit with failure */
void do_error_exit(sqlo_db_handle_t dbh, const char * file,
int line, const char * msg) {
printf("%s (line: %d):\n%s: %s\n", file, line, msg, sqlo_geterror(dbh));
sqlo_rollback(dbh);
sqlo_finish(dbh);
exit(EXIT_FAILURE);
}
int OraDBRequest::usurfActivation(request_t* request)
{
_var_retr = DB_RETR_INIT;
snprintf(_var_msisdn, sizeof(_var_msisdn), "%s", request->a_no);
snprintf(_var_country, sizeof(_var_country), "%s", request->country);
_var_duration = request->duration;
_var_status = request->result_code;
int ora_status = ora_force_execute(&_sth_ua, 0);
request->db_retr = _var_retr;
if (ora_status < 0) {
LOG_CRITICAL("%s: Failed to EXECUTE SP_USURF_ACTIVATION."
" STATEMENT: \"%s\", LIBSQLORA ERROR: \"%s\"",
__func__, sqlo_command(_sth_ua), sqlo_geterror(_dbh));
//-- try to re-bind...
usurfActivationBind();
return -1;
}
snprintf(request->partner, sizeof(request->partner), "%s", _var_extra_o_1);
snprintf(request->exptime, sizeof(request->exptime), "%s", _var_extra_o_2);
snprintf(request->expdate, sizeof(request->expdate), "%s", _var_extra_o_3);
LOG_DEBUG("%s: retr: %d, msisdn: %s, partner: %s, exptime: %s, expdate: %s", __func__
, request->db_retr, request->a_no, request->partner, request->exptime, request->expdate);
return 0;
}
int OraDBRequest::updateRequest(const request_t* request)
{
memcpy(&_request, request, sizeof(request_t));
if (ora_force_execute(&_sth_update, 0, 1) < 0) {
LOG_CRITICAL("%s: Failed to EXECUTE UPDATE_REQUEST."
" STATEMENT: \"%s\", LIBSQLORA ERROR: \"%s\"",
__func__, sqlo_command(_sth_update), sqlo_geterror(_dbh));
LOG_DEBUG("%s: request id: %d"
", step_no: %d"
", last_step_no: %d"
", status: %d"
", tran_type: %d"
", customer_type: %s"
", min_bal: %d"
", error_code: %d"
", imsi: %s"
", activation_date: %s"
", deactivation_date: %s"
", duration: %d"
", gsm_num: %s"
", result_code: %d"
", silent: %d"
, __func__ , request->id
, request->step_no
, request->last_step_no
, request->status
, request->tran_type
, request->customer_type
, request->min_bal
, request->error_code
, request->imsi
, request->activation_date
, request->deactivation_date
, request->duration
, request->gsm_num
, request->result_code
, request->silent
);
//-- try to re-bind...
updateBind();
return -1;
}
LOG_DEBUG("%s: request %d updated imsi: %s, step_no: %d, last_step_no: %d, status: %d, result_code: %d", __func__
, request->id, request->imsi, request->step_no, request->last_step_no, request->status, request->result_code);
return 0;
}
int OraDBRequest::updateManualRequest(const request_t* request)
{
memcpy(&_request, request, sizeof(request_t));
if (ora_force_execute(&_sth_update2, 0, 1) < 0) {
LOG_CRITICAL("%s: Failed to EXECUTE UPDATE_MANUAL_REQUEST."
" STATEMENT: \"%s\", LIBSQLORA ERROR: \"%s\"",
__func__, sqlo_command(_sth_update2), sqlo_geterror(_dbh));
//-- try to re-bind...
updateManualBind();
return -1;
}
LOG_DEBUG("%s: request %d updated status: %d",
__func__, request->id, request->status);
return 0;
}
int OraDBRequest::insertKeywordRequest(const request_t* request)
{
memcpy(&_request, request, sizeof(request_t));
if (ora_force_execute(&_sth_insert_kw, 0, 1) < 0) {
LOG_CRITICAL("%s: Failed to EXECUTE INSERT_REQUEST."
" STATEMENT: \"%s\", LIBSQLORA ERROR: \"%s\"",
__func__, sqlo_command(_sth_insert_kw), sqlo_geterror(_dbh));
//-- try to re-bind...
insertBind();
return -1;
}
LOG_DEBUG("%s: request inserted enrollment_type: %s, msisdn: %s, imsi: %s, ref_id: %d",
__func__, request->msg, request->a_no, request->imsi, request->ref_id);
return 0;
}
int OraDBRequest::insertRequest(const request_t* request)
{
memcpy(&_request, request, sizeof(request_t));
if (ora_force_execute(&_sth_insert, 0, 1) < 0) {
LOG_CRITICAL("%s: Failed to EXECUTE INSERT_REQUEST."
" STATEMENT: \"%s\", LIBSQLORA ERROR: \"%s\"",
__func__, sqlo_command(_sth_insert), sqlo_geterror(_dbh));
//-- try to re-bind...
insertBind();
return -1;
}
LOG_DEBUG("%s: request inserted msg: %s, a_no: %s, b_no: %s",
__func__, request->msg, request->a_no, request->b_no);
return 0;
}
int OraDBRequest::processShampoo(request_t* request)
{
_var_retr = DB_RETR_INIT;
memcpy(&_request, request, sizeof(request_t));
int ora_status = ora_force_execute(&_sth_process_shampoo, 0);
request->db_retr = _var_retr;
if (ora_status < 0) {
LOG_CRITICAL("%s: Failed to EXECUTE SP_PROCESS_SHAMPOO."
" STATEMENT: \"%s\", LIBSQLORA ERROR: \"%s\"",
__func__, sqlo_command(_sth_process_shampoo), sqlo_geterror(_dbh));
//-- try to re-bind...
processShampooBind();
return -1;
}
LOG_DEBUG("%s: retr: %d, msisdn: %s, type: %s, plan: %s, start: %s, end: %s", __func__
, request->db_retr, request->svc_msisdn, request->svc_type, request->svc_plan
, request->svc_start, request->svc_end);
return 0;
}
int OraDBRequest::processMlp(request_t* request)
{
_var_retr = DB_RETR_INIT;
memcpy(&_request, request, sizeof(request_t));
int ora_status = ora_force_execute(&_sth_process_mlp, 0);
request->db_retr = _var_retr;
if (ora_status < 0) {
LOG_CRITICAL("%s: Failed to EXECUTE SP_PROCESS_MLP."
" STATEMENT: \"%s\", LIBSQLORA ERROR: \"%s\"",
__func__, sqlo_command(_sth_process_mlp), sqlo_geterror(_dbh));
//-- try to re-bind...
processMlpBind();
return -1;
}
LOG_DEBUG("%s: retr: %d, msisdn: %s, transaction_code: %s, transaction_id: %s, bill_cycle: %s, type: %s, soc: %s, effdate: %s", __func__
, request->db_retr, request->svc_msisdn, request->svc_txcode, request->svc_txid, request->svc_bill_cycle
, request->svc_type, request->svc_soc, request->svc_eff_date);
return 0;
}
int OraDBRequest::usurfDeactivation(request_t* request)
{
_var_retr = DB_RETR_INIT;
snprintf(_var_msisdn, sizeof(_var_msisdn), "%s", request->a_no);
snprintf(_var_service_id, sizeof(_var_service_id), "%s", request->service_id);
int ora_status = ora_force_execute(&_sth_ud, 0);
request->db_retr = _var_retr;
if (ora_status < 0) {
LOG_CRITICAL("%s: Failed to EXECUTE SP_USURF_DEACTIVATION."
" STATEMENT: \"%s\", LIBSQLORA ERROR: \"%s\"",
__func__, sqlo_command(_sth_ud), sqlo_geterror(_dbh));
//-- try to re-bind...
usurfDeactivationBind();
return -1;
}
LOG_DEBUG("%s: retr: %d, msisdn: %s, service_id: %s", __func__, request->db_retr, request->a_no, request->service_id);
return 0;
}
int OraDBRequest::processTran(request_t* request)
{
_var_retr = DB_RETR_INIT;
_var_trantype = request->tran_type;
_var_req_id = request->id;
_var_ref_id = request->ref_id;
snprintf(_var_msisdn, sizeof(_var_msisdn), "%s", request->a_no);
memset(_var_extra_i_1, 0, sizeof(_var_extra_i_1));
memset(_var_extra_i_2, 0, sizeof(_var_extra_i_2));
memset(_var_extra_i_3, 0, sizeof(_var_extra_i_3));
memset(_var_extra_o_1, 0, sizeof(_var_extra_o_1));
memset(_var_extra_o_2, 0, sizeof(_var_extra_o_2));
memset(_var_extra_o_3, 0, sizeof(_var_extra_o_3));
switch (request->tran_type) {
case TRAN_TYPE_GROAM_ON:
snprintf(_var_extra_i_1, sizeof(_var_extra_i_1), "%s", request->activation_date);
snprintf(_var_extra_i_2, sizeof(_var_extra_i_2), "%d", request->duration);
snprintf(_var_extra_i_3, sizeof(_var_extra_i_3), "%s", request->imsi);
break;
case TRAN_TYPE_GROAM_EXTEND:
snprintf(_var_extra_i_1, sizeof(_var_extra_i_1), "%d", request->duration);
break;
case TRAN_TYPE_GLIST:
snprintf(_var_extra_i_1, sizeof(_var_extra_i_1), "%s", request->country);
break;
case TRAN_TYPE_GLIST_ZONE:
snprintf(_var_extra_i_1, sizeof(_var_extra_i_1), "%d", request->zone);
break;
case TRAN_TYPE_ROAM_USURF_ON:
snprintf(_var_extra_i_1, sizeof(_var_extra_i_1), "%s", request->country);
snprintf(_var_extra_i_2, sizeof(_var_extra_i_2), "%d", request->duration);
break;
default:
snprintf(_var_extra_i_1, sizeof(_var_extra_i_1), "%s", request->gsm_num);
}
int ora_status = ora_force_execute(&_sth_pt, 0);
request->db_retr = _var_retr;
if (ora_status < 0) {
LOG_CRITICAL("%s: Failed to EXECUTE SP_PROCESS_TRAN."
" STATEMENT: \"%s\", LIBSQLORA ERROR: \"%s\"",
__func__, sqlo_command(_sth_pt), sqlo_geterror(_dbh));
//-- try to re-bind...
processTranBind();
return -1;
}
switch (request->tran_type) {
case TRAN_TYPE_GROAM_ON:
snprintf(request->activation_date, sizeof(request->activation_date), "%s", _var_extra_o_1);
snprintf(request->promo_name, sizeof(request->promo_name), "%s", _var_extra_o_2);
LOG_DEBUG("%s: retr: %d, trantype: %d, msisdn: %s, req_id: %d, ref_id: %d"
", activation_date: %s, deactivation_date: %s, duration: %d, promo_name: %s", __func__
, request->db_retr, request->tran_type, request->a_no, request->id, request->ref_id
, request->activation_date, request->deactivation_date, request->duration, request->promo_name);
break;
case TRAN_TYPE_GROAM_OFF:
snprintf(request->service_id, sizeof(request->service_id), "%s", _var_extra_o_1);
break;
case TRAN_TYPE_GLIST:
request->zone = strtol(_var_extra_o_1, NULL, 10);
snprintf(request->rate, sizeof(request->rate), "%s", _var_extra_o_2);
LOG_DEBUG("%s: retr: %d, trantype: %d, msisdn: %s, req_id: %d, ref_id: %d: zone: %d, rate: %s", __func__,
request->db_retr, request->tran_type, request->a_no, request->id, request->ref_id,
request->zone, request->rate);
break;
case TRAN_TYPE_GLIST_ZONE:
snprintf(request->country, sizeof(request->country), "%s", _var_extra_o_1);
LOG_DEBUG("%s: retr: %d, trantype: %d, msisdn: %s, req_id: %d, ref_id: %d: country: %s", __func__,
request->db_retr, request->tran_type, request->a_no, request->id, request->ref_id, request->country);
break;
case TRAN_TYPE_CHECKLINK:
request->local_sim = strtol(_var_extra_o_1, NULL, 10);
LOG_DEBUG("%s: retr: %d, trantype: %d, msisdn: %s, req_id: %d, ref_id: %d: local_sim: %d", __func__,
request->db_retr, request->tran_type, request->a_no, request->id, request->ref_id, request->local_sim);
break;
case TRAN_TYPE_YES:
snprintf(request->gsm_num, sizeof(request->gsm_num), "%s", _var_extra_o_1);
LOG_DEBUG("%s: retr: %d, trantype: %d, msisdn: %s, req_id: %d, ref_id: %d: gsm_num: %s", __func__,
request->db_retr, request->tran_type, request->a_no, request->id, request->ref_id, request->gsm_num);
break;
case TRAN_TYPE_ROAM_USURF_ON:
snprintf(request->country, sizeof(request->country), "%s", _var_extra_o_1);
request->duration = strtol(_var_extra_o_2, NULL, 10);
snprintf(request->service_id, sizeof(request->service_id), "%s", _var_extra_o_3);
break;
default:
snprintf(request->activation_date, sizeof(request->activation_date), "%s", _var_extra_o_1);
snprintf(request->deactivation_date, sizeof(request->deactivation_date), "%s", _var_extra_o_2);
request->duration = strtol(_var_extra_o_3, NULL, 10);
LOG_DEBUG("%s: retr: %d, trantype: %d, msisdn: %s, req_id: %d, ref_id: %d, link_to: %s, "
"activation_date: %s, deactivation_date: %s, duration: %d, country: %s, service_id: %s", __func__,
request->db_retr, request->tran_type, request->a_no, request->id, request->ref_id, request->gsm_num,
request->activation_date, request->deactivation_date, request->duration, request->country, request->service_id);
}
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;
}
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 )
/*
* Execute SQL statement. For select statements only.
* If fails, program terminates.
*/
DB_RESULT zbx_db_vselect(const char *fmt, va_list args)
{
char *sql = NULL;
DB_RESULT result;
/* double sec;*/
#ifdef HAVE_ORACLE
sqlo_stmt_handle_t sth;
#endif
#ifdef HAVE_SQLITE3
int ret = FAIL;
char *error=NULL;
#endif
#ifdef HAVE_POSTGRESQL
char *error = NULL;
#endif
/* sec = zbx_time();*/
sql = zbx_dvsprintf(sql, fmt, args);
zabbix_log( LOG_LEVEL_DEBUG, "Query [%s]", sql);
#ifdef HAVE_MYSQL
if(!conn)
{
zabbix_errlog(ERR_Z3003);
result = NULL;
}
else
{
if(mysql_query(conn,sql) != 0)
{
zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
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:
result = (DB_RESULT)ZBX_DB_DOWN;
break;
default:
result = NULL;
break;
}
}
else
{
result = mysql_store_result(conn);
}
}
#endif
#ifdef HAVE_POSTGRESQL
result = zbx_malloc(NULL, sizeof(ZBX_PG_DB_RESULT));
result->pg_result = PQexec(conn, sql);
result->values = NULL;
result->cursor = 0;
result->row_num = 0;
if (NULL == result->pg_result)
{
zabbix_errlog(ERR_Z3005, 0, "Result is NULL", sql);
}
if (PGRES_TUPLES_OK != PQresultStatus(result->pg_result))
{
error = zbx_dsprintf(error, "%s:%s",
PQresStatus(PQresultStatus(result->pg_result)),
PQresultErrorMessage(result->pg_result));
zabbix_errlog(ERR_Z3005, 0, error, sql);
zbx_free(error);
}
else /* init rownum */
result->row_num = PQntuples(result->pg_result);
#endif
#ifdef HAVE_ORACLE
if(0 > (sth = (sqlo_open(oracle, sql,0,NULL))))
{
zabbix_errlog(ERR_Z3005, 0, sqlo_geterror(oracle), sql);
exit(FAIL);
}
result = sth;
#endif
#ifdef HAVE_SQLITE3
if(!sqlite_transaction_started)
{
php_sem_acquire(&sqlite_access);
}
result = zbx_malloc(NULL, sizeof(ZBX_SQ_DB_RESULT));
result->curow = 0;
lbl_get_table:
if(SQLITE_OK != (ret = sqlite3_get_table(conn,sql,&result->data,&result->nrow, &result->ncolumn, &error)))
{
if(ret == SQLITE_BUSY) goto lbl_get_table; /* attention deadlock!!! */
zabbix_errlog(ERR_Z3005, 0, error, sql);
sqlite3_free(error);
if(!sqlite_transaction_started)
{
php_sem_release(&sqlite_access);
}
exit(FAIL);
}
if(!sqlite_transaction_started)
{
php_sem_release(&sqlite_access);
}
#endif
/* sec = zbx_time() - sec;
if(sec > 0.1)
zabbix_log( LOG_LEVEL_WARNING, "Long query: " ZBX_FS_DBL " sec, \"%s\"", sec, sql);*/
zbx_free(sql);
return result;
}
DB_ROW zbx_db_fetch(DB_RESULT result)
{
#ifdef HAVE_MYSQL
if(!result) return NULL;
return mysql_fetch_row(result);
#endif
#ifdef HAVE_POSTGRESQL
int i;
/* EOF */
if(!result) return NULL;
/* free old data */
if(result->values)
{
zbx_free(result->values);
result->values = NULL;
}
/* EOF */
if(result->cursor == result->row_num) return NULL;
/* init result */
result->fld_num = PQnfields(result->pg_result);
if(result->fld_num > 0)
{
result->values = zbx_malloc(result->values, sizeof(char*) * result->fld_num);
for(i = 0; i < result->fld_num; i++)
{
if(PQgetisnull(result->pg_result, result->cursor, i))
{
result->values[i] = NULL;
}
else
{
result->values[i] = PQgetvalue(result->pg_result, result->cursor, i);
if(PQftype(result->pg_result,i) == ZBX_PG_BYTEAOID) /* binary data type BYTEAOID */
zbx_pg_unescape_bytea((u_char *)result->values[i]);
}
}
}
result->cursor++;
return result->values;
#endif
#ifdef HAVE_ORACLE
int res;
res = sqlo_fetch(result, 1);
if(SQLO_SUCCESS == res)
{
return (DB_ROW)sqlo_values(result, NULL, 1);
}
else if(SQLO_NO_DATA == res)
{
return NULL;
}
zabbix_errlog(ERR_Z3006, 0, sqlo_geterror(oracle));
exit(FAIL);
return NULL;
#endif
#ifdef HAVE_SQLITE3
/* EOF */
if(!result) return NULL;
/* EOF */
if(result->curow >= result->nrow) return NULL;
if(!result->data) return NULL;
result->curow++; /* NOTE: First row == header row */
return &(result->data[result->curow * result->ncolumn]);
#endif
return NULL;
}
/*
* Execute SQL statement. For non-select statements only.
* If fails, program terminates.
*/
int zbx_db_vexecute(const char *fmt, va_list args)
{
char *sql = NULL;
int ret = ZBX_DB_OK;
/* double sec;*/
#ifdef HAVE_POSTGRESQL
PGresult *result;
char *error = NULL;
#endif
#ifdef HAVE_SQLITE3
char *error=0;
#endif
#ifdef HAVE_MYSQL
int status;
#endif
/* sec = zbx_time();*/
sql = zbx_dvsprintf(sql, fmt, args);
zabbix_log( LOG_LEVEL_DEBUG, "Query [%s]", sql);
#ifdef HAVE_MYSQL
if(!conn)
{
zabbix_errlog(ERR_Z3003);
ret = ZBX_DB_FAIL;
}
else
{
if (0 != (status = mysql_query(conn,sql)))
{
zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
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:
ret = ZBX_DB_FAIL;
break;
}
}
else
{
do {
if (mysql_field_count(conn) == 0)
{
/* zabbix_log(LOG_LEVEL_DEBUG, ZBX_FS_UI64 " rows affected",
(zbx_uint64_t)mysql_affected_rows(conn));*/
ret += (int)mysql_affected_rows(conn);
}
else /* some error occurred */
{
zabbix_log(LOG_LEVEL_DEBUG, "Could not retrieve result set");
break;
}
/* more results? -1 = no, >0 = error, 0 = yes (keep looping) */
if ((status = mysql_next_result(conn)) > 0)
zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
} while (status == 0);
}
}
#endif
#ifdef HAVE_POSTGRESQL
result = PQexec(conn,sql);
if( result==NULL)
{
zabbix_errlog(ERR_Z3005, 0, "Result is NULL", sql);
ret = ZBX_DB_FAIL;
}
else if( PQresultStatus(result) != PGRES_COMMAND_OK)
{
error = zbx_dsprintf(error, "%s:%s",
PQresStatus(PQresultStatus(result)),
PQresultErrorMessage(result));
zabbix_errlog(ERR_Z3005, 0, error, sql);
zbx_free(error);
ret = ZBX_DB_FAIL;
}
if(ret == ZBX_DB_OK)
{
ret = PQntuples(result);
}
PQclear(result);
#endif
#ifdef HAVE_ORACLE
if ((ret = sqlo_exec(oracle, sql))<0)
{
zabbix_errlog(ERR_Z3005, 0, sqlo_geterror(oracle), sql);
ret = ZBX_DB_FAIL;
}
#endif
#ifdef HAVE_SQLITE3
if (!sqlite_transaction_started)
{
php_sem_acquire(&sqlite_access);
}
lbl_exec:
if (SQLITE_OK != (ret = sqlite3_exec(conn, sql, NULL, 0, &error)))
{
if (ret == SQLITE_BUSY)
goto lbl_exec; /* attention deadlock!!! */
zabbix_errlog(ERR_Z3005, 0, error, sql);
sqlite3_free(error);
ret = ZBX_DB_FAIL;
}
if (ret == ZBX_DB_OK)
{
ret = sqlite3_changes(conn);
}
if (!sqlite_transaction_started)
{
php_sem_release(&sqlite_access);
}
#endif
/* sec = zbx_time() - sec;
if(sec > 0.1)
zabbix_log( LOG_LEVEL_WARNING, "Long query: " ZBX_FS_DBL " sec, \"%s\"", sec, sql);*/
zbx_free(sql);
return ret;
}
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() {
#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 OraDBRequest::initTran(request_t* request)
{
_var_retr = DB_RETR_INIT;
_var_trantype = request->tran_type;
_var_req_id = request->id;
_var_ref_id = request->ref_id;
snprintf(_var_msisdn, sizeof(_var_msisdn), "%s", request->a_no);
memset(_var_extra_i_1, 0, sizeof(_var_extra_i_1));
memset(_var_extra_i_2, 0, sizeof(_var_extra_i_2));
memset(_var_extra_i_3, 0, sizeof(_var_extra_i_3));
memset(_var_extra_o_1, 0, sizeof(_var_extra_o_1));
memset(_var_extra_o_2, 0, sizeof(_var_extra_o_2));
memset(_var_extra_o_3, 0, sizeof(_var_extra_o_3));
switch (request->tran_type) {
case TRAN_TYPE_ROAM_USURF_ON:
case TRAN_TYPE_ROAM_USURF_OFF:
case TRAN_TYPE_ROAM_USURF_STATUS:
snprintf(_var_extra_i_1, sizeof(_var_extra_i_1), "%s", request->promo_code);
snprintf(_var_extra_i_2, sizeof(_var_extra_i_2), "%s", request->promo_value);
break;
default:
snprintf(_var_extra_i_1, sizeof(_var_extra_i_1), "%s", request->activation_date);
snprintf(_var_extra_i_2, sizeof(_var_extra_i_2), "%d", request->duration);
snprintf(_var_extra_i_3, sizeof(_var_extra_i_3), "%s", request->gsm_num);
break;
}
int ora_status = ora_force_execute(&_sth_it, 0);
request->db_retr = _var_retr;
if (ora_status < 0) {
LOG_CRITICAL("%s: Failed to EXECUTE SP_INIT_TRAN."
" STATEMENT: \"%s\", LIBSQLORA ERROR: \"%s\"",
__func__, sqlo_command(_sth_it), sqlo_geterror(_dbh));
//-- try to re-bind...
initTranBind();
return -1;
}
switch (request->tran_type) {
case TRAN_TYPE_ROAM_USURF_STATUS:
snprintf(request->expdate, sizeof(request->expdate), "%s", _var_extra_o_1);
break;
default:
request->min_bal = strtol(_var_extra_o_1, NULL, 10);
snprintf(request->others, sizeof(request->others), "%s", _var_extra_o_2);
request->nsn_flag = strtol(_var_extra_o_3, NULL, 10);
}
LOG_DEBUG("%s: retr: %d, trantype: %d, msisdn: %s, req_id: %d, ref_id: %d"
", min_bal: %d, nsn_flag: %d, activation_date: %s, deactivation_date: %s"
", duration: %d, country: %s, promo_code: %s, promo_value: %s, others: %s", __func__
, request->db_retr, request->tran_type, request->a_no, request->id, request->ref_id
, request->min_bal, request->nsn_flag, request->activation_date, request->deactivation_date
, request->duration, request->country, request->promo_code, request->promo_value, request->others);
return 0;
}
