/* * 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); }