/*
* Function: printArtistComment
* Parameters: funcName stuct
* Returns: (void)
*
* This function checks if the current user is a librarian or not
* so it knows whether the user has permission to add a new artist.
* If they don't have permission they will recieve a message stating
* they are not privleged to add new artists. The function will then
* check that the cgi form was a success and then will add the new artist
* by communicating with the database.
*/
static void doAddArtist(void)
{
Boolean needAddForm = TRUE;
int result = 0;
/* Temporary Struct to store form data */
artistNode_t *formdata = NULL;
/* Array to store errors */
int *errors = NULL;
Boolean isAdding = FALSE;
/* Check privileges of current user */
if (isUserLibrarian(_currUserLogon) == FALSE) {
fprintf(cgiOut, "You are not privileged to add new Artists\n");
return;
}
fprintf(cgiOut, "<div class=\"head1\">Adding New Artist</div>\n");
/* if adding field is set */
result = cgiFormIntegerBounded("adding", &isAdding, FALSE, TRUE, FALSE);
if (result != cgiFormSuccess) {
/* Some sort of failure */
isAdding = FALSE;
}
/* Malloc space for form data */
formdata = malloc(sizeof(artistNode_t));
if (formdata == NULL) {
isAdding = FALSE;
}
else {
Boolean formOK = FALSE;
formdata->name = NULL;
/* Malloc space for error code of each field */
errors = malloc(sizeof(int) * 1);
if (errors == NULL) {
free(formdata);
isAdding = FALSE;
}
else {
/* Set errors to E_NOERROR */
int i = 0;
for (i = 0; i < 1; i++) {
errors[i] = E_NOERROR;
}
}
/* The curr data is ready for processing */
formOK = processAddForm(errors, formdata);
if (isAdding == TRUE && formOK == TRUE) {
int newArtistid = -1;
/* All form data is good */
/* Add artist to database */
newArtistid = addArtist(formdata->name);
if (newArtistid > 0) {
/* User added ok */
needAddForm = FALSE;
fprintf(cgiOut, "Adding successful<br />\n");
fprintf(cgiOut,
"<a href=\"./?page=artist&artistid=%d&hash=%d\">[View Artist]</a><br />\n",
newArtistid, _currUserLogon);
fprintf(cgiOut,
"<a href=\"./?page=album&func=add&artistid=%d&hash=%d\">[Add Album by this Artist]</a><br />\n",
newArtistid, _currUserLogon);
fprintf(cgiOut,
"<a href=\"./?page=artist&func=add&hash=%d\">[Add Another Artist]</a>",
_currUserLogon);
}
else {
/* Artist adding error */
fprintf(cgiOut, "DB Save Error<br />\n");
}
}
}
if (needAddForm == TRUE) {
/* Need to print form */
printAddForm(isAdding, errors, formdata);
}
/* Free the memory */
if (errors != NULL) {
free(errors);
}
if (formdata != NULL) {
if (formdata->name != NULL) {
free(formdata->name);
}
free(formdata);
}
}
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 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);
}
