<html>
<head>
<title>Snort IDS logs for Securetty.com</title>
<base target="_self">
</head>
<body bgcolor="gray">
<form action="tixedit.php" target="logdata" method="post">
<?php
/* filename= tixedit.php */
include ("mytime.php");
include ("myfunctions.php");
# If the includes are not working for the db settings then populate them locally:
include ("dbconn_out.php");
include ("tixdbconn_in.php");
## mysql snort database
# $db_host = ""; # mysql database server.
# $db_user = ""; # mysql user with SELECT, UPDATE, INSERT priveleges.
# $db_passwd = ""; # password for mysql user.
# $db_database = ""; # database name where snort logs to.
## mysql snort database for ticketing system
# $tix_db_host = ""; # IP of mysql server containing ticketing database.
# $tix_db_user = ""; # mysql user with SELECT, INSERT, UPDATE, DELETE priveleges.
# $tix_db_passwd = ""; # password for mysql user.
# $tix_db_database = ""; # name of the ticketing database.
#-----------------------------------------------------------------------------------------
# Do the the following if the 'Create Ticket' button was clicked
#-----------------------------------------------------------------------------------------
if ($tix_database=="Create Ticket")
{
#-----------------------------------------------------------------------------------------
# Begin Save New Data to Database
#-----------------------------------------------------------------------------------------
#----- Connect to Ticketing database ---------------------------------------
$tix_db_connection = mysql_connect($tix_db_host, $tix_db_user, $tix_db_passwd);
mysql_select_db($tix_db_database);
#----- Generate Ticket Number and INSERT new tix row in ticket table -------
$strtixdesc = strval($tixdesc);
$INSstr = "INSERT INTO ticket (timestamp, description, status_id, engineer_id) ";
$INSstr .= "VALUES ($intCurrentDateTime, '$tixdesc', $tix_status_id, $tix_engineer_id)";
$Result = mysql_query($INSstr, $tix_db_connection);
#----- Save new tix info onto history table --------------------------------
$newtix_number = mysql_insert_id();
$INSstr = "INSERT INTO history (tix_id, timestamp, engineer_id, status_id, notes) ";
$INSstr .= " VALUES ($newtix_number, $intCurrentDateTime, $tix_engineer_id, ";
$INSstr .= "$tix_status_id, '$tixnotes')";
$Result = mysql_query($INSstr, $tix_db_connection);
#-- Parse the str_cid_list so the numbers can be saved in the events table -
$cid_num = explode(",", $str_cid_list);
# for ($Y=0; $Y<count($cid_num); $Y++)
# {
# $cid_num[$Y]=intval($cid_num[$Y]);
# }
#----- Update snortdata.event table with tix_id ----------------------------
$db_connection = mysql_connect($db_host, $db_user, $db_passwd);
mysql_select_db($db_database);
for($X=0;$X<count($cid_num);$X++)
{
$INSstr = "UPDATE event SET tix_id=$newtix_number ";
$INSstr .= " WHERE cid=$cid_num[$X]";
# $INSstr .= " WHERE cid=9";
$Result = mysql_query($INSstr, $db_connection);
}
$theTix_number = $newtix_number;
#----- Ensure that the screen appears and gets populated -------------------
# $tix_database="Update Ticket";
}
#-----------------------------------------------------------------------------------------
# End Save New Data to Database
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
# Begin Save data from an existing Ticket
#-----------------------------------------------------------------------------------------
if (($tix_database == "Update Ticket") AND ($theTix_number != ""))
{
#----- Connect to Ticketing database ---------------------------------------
$tix_db_connection = mysql_connect($tix_db_host, $tix_db_user, $tix_db_passwd);
mysql_select_db($tix_db_database);
#----- INSERT new tix row in ticket table -------
$strtixdesc = strval($tixdesc);
$UPstr = "UPDATE ticket SET status_id=$tix_status_id, engineer_id=$tix_engineer_id ";
$UPstr .= "WHERE tix_id=$theTix_number";
$UPresult = mysql_query($UPstr, $tix_db_connection);
#----- Save new tix info onto history table --------------------------------
$newtix_number = mysql_insert_id();
$INSstr = "INSERT INTO history (tix_id, timestamp, engineer_id, status_id, notes) ";
$INSstr .= " VALUES ($theTix_number, NOW(), $tix_engineer_id, ";
$INSstr .= "$tix_status_id, '$tixnotes')";
$Result = mysql_query($INSstr, $tix_db_connection);
}
if ($tix_database != "View Ticket History")
{
#-----------------------------------------------------------------------------------------
# Begin Populate screen with data from existing Ticket
#-----------------------------------------------------------------------------------------
$tix_db_connection = mysql_connect($tix_db_host, $tix_db_user, $tix_db_passwd);
mysql_select_db($tix_db_database);
#----- Query Tix Database for Engineer data to populate Engineer List box ----------------
$Selstr = "SELECT engineer.engineer_id, eng_rank.rank_name, engineer.eng_name ";
$Selstr .= "FROM engineer, eng_rank ";
$Selstr .= "WHERE engineer.engineer_id=eng_rank.rank_id ";
$EngResult = mysql_query($Selstr, $tix_db_connection);
#----- Query mtstix database to populate the screen --------------------------------------
$Selstr = "SELECT tix_id, engineer_id, status_id, timestamp, description ";
$Selstr .= "FROM ticket WHERE tix_id=$theTix_number";
$ScrnResult = mysql_query($Selstr, $tix_db_connection);
if (mysql_num_rows($ScrnResult) != 0)
{
$scrnrow = mysql_fetch_row($ScrnResult);
}
#----- Query snortdata.event table to find all the cids for a particular tix_id ---------
$db_connection = mysql_connect($db_host, $db_user, $db_passwd);
mysql_select_db($db_database);
$Selstr = " SELECT event.cid FROM event WHERE event.tix_id=$theTix_number";
$cidResult = mysql_query($Selstr, $db_connection);
$the_cid_string = "";
if (mysql_num_rows($cidResult) != 0)
{
$cidrow = mysql_fetch_row($cidResult);
$the_cid_string = $cidrow[0];
for ($i=1; $i < mysql_num_rows($cidResult); $i++)
{
$cidrow = mysql_fetch_row($cidResult);
$the_cid_string .= ",".$cidrow[0];
}
}
#--------- Present Edit Ticket Screen -----------------------------------------------------------
print "<table width=\"100%\" bgcolor=\"#334678\" border=\"0\">\n";
print "<tr><th colspan=\"2\" bgcolor=\"orange\" align=\"left\">Edit Ticket:</th><tr>\n";
print " <td align=\"left\" valign=\"top\"><table bgcolor=\"purple\" border=\"1\">\n";
print " <tr><td width=\"110\" align=\"right\"><b>Ticket Number:</b></td>\n";
print " <td width=\"300\" align=\"left\">$scrnrow[0]</td></tr>\n";
print " <tr><td rowspan=\"3\" width=\"110\" align=\"center\"><b>Referenced<br>Log Numbers:</b></td>\n";
print " <td rowspan=\"3\" width=\"300\" align=\"left\">\n";
print " <textarea readonly rows=\"2\" cols=\"35\">$the_cid_string</textarea>\n";
print " </td></tr>\n";
print " </table>\n";
print " </td>\n";
print " <td align=\"left\" valign=\"top\"><table bgcolor=\"purple\" border=\"1\">\n";
print " <tr><td width=\"110\" align=\"right\"><b>Description:</b></td>\n";
print " <td width=\"300\" align=\"left\">$scrnrow[4]</td>\n";
print " </tr>\n";
print " <tr><td rowspan=\"3\" width=\"110\" align=\"center\"><b>Creation Date:</b></td>\n";
print " <td rowspan=\"3\" width=\"300\" align=\"left\">$scrnrow[3]</td></tr>\n";
print " </table>\n";
print " </td>\n";
print "</tr>\n";
print "<tr><td colspan=\"2\"><hr></td></tr>\n";
print "<tr><td colspan=\"2\" align=\"left\">\n";
print " <b>Notes:</b><br>\n";
print " <textarea rows=\"8\" cols=\"90\" name=\"tixnotes\"></textarea>\n";
print " </td></tr>\n";
print "<tr>\n";
print " <td colspan=\"2\">\n";
print " <table border=\"0\">\n";
print " <tr>\n";
print " <td width=\"200\"><b>Engineer:</b><select name=\"tix_engineer_id\" size=\"1\">\n";
if (mysql_num_rows($EngResult) != 0)
{
for ($i=0; $i < mysql_num_rows($EngResult); $i++)
{
$row = mysql_fetch_row($EngResult);
if ($row[0]==$scrnrow[1])
{
print "<option selected value=$row[0]>".$row[1]."-".$row[2]."</option>\n";
}
else
{
print "<option value=$row[0]>".$row[1]."-".$row[2]."</option>\n";
}
}
}
print " </select>\n";
print " </td><td>\n";
print " <table border=\"0\">\n";
switch ($scrnrow[2])
{
case 0:
$is_new="checked";
break;
case 1:
$is_assigned="checked";
break;
case 2:
$is_pending="checked";
break;
case 3:
$is_resolved="checked";
break;
case 4:
$is_dismissed="checked";
break;
default:
$is_new="";
$is_assigned="";
$is_pending="";
$is_resolved="";
$is_dismissed="";
}
print " <tr><th colspan=\"5\" bgcolor=\"orange\"><b>Ticket Status:</b></th></tr>\n";
print " <tr><td><b>NEW</b><input type=\"radio\" name=\"tix_status_id\" $is_new value=\"0\"></td>\n";
print " <td><b>ASSIGNED</b><input type=\"radio\" name=\"tix_status_id\" $is_assigned value=\"1\"></td>\n";
print " <td><b>PENDING</b><input type=\"radio\" name=\"tix_status_id\" $is_pending value=\"2\"></td>\n";
print " <td><b>RESOLVED</b><input type=\"radio\" name=\"tix_status_id\" $is_resolved value=\"3\"></td>\n";
print " <td><b>DISMISSED</b><input type=\"radio\" name=\"tix_status_id\" $is_dismissed value=\"4\"></td></tr>\n";
print " </table>\n";
print " </td>\n";
print " </tr><tr>\n";
print " <td></td>\n";
print " <td bgcolor=\"orange\">\n";
print " <input type=\"submit\" name=\"tix_database\" value=\"Update Ticket\">\n";
print " <input type=\"submit\" name=\"tix_database\" value=\"View Ticket History\">\n";
print " <a href=\"tixviewall.php\">View Tickets</a>\n";
print " </td>\n";
print " </tr>\n";
print " </table>\n";
print " </td>\n";
print "</tr>\n";
print "</table>\n";
#---------------------- END Edit Ticket Screen -----------------------------------------
#------ make public these variables --------------------------------------------
print "<input type=\"hidden\" name=\"theTix_number\" value=$theTix_number> ";
# print "<input type=\"hidden\" name=\"the_cid_string\" value=$the_cid_string> ";
}
#--------------------- THIS IS THE TICKET HISTORY PAGE ---------------------------------
if (($tix_database == "View Ticket History") AND ($theTix_number != ""))
{
#----- Connect to Ticketing database ---------------------------------------
$tix_db_connection = mysql_connect($tix_db_host, $tix_db_user, $tix_db_passwd);
mysql_select_db($tix_db_database);
#----- Query ticket table in mtstix database to populate the screen --------
$Selstr = "SELECT ticket.tix_id, ticket.engineer_id, ticket.timestamp, status.status_name, ticket.description ";
$Selstr .= "FROM ticket, status ";
$Selstr .= "WHERE tix_id=$theTix_number AND status.status_id=ticket.status_id";
$TixResult = mysql_query($Selstr, $tix_db_connection);
if (mysql_num_rows($TixResult) != 0)
{
$tixhdr = mysql_fetch_row($TixResult);
}
$EngID = intval($tixhdr[1]);
$Selstr = "SELECT engineer.eng_name, eng_rank.rank_name ";
$Selstr .= "FROM engineer, eng_rank ";
$Selstr .= "WHERE engineer.rank_id=eng_rank.rank_id ";
$Selstr .= "AND engineer_id=$EngID";
$EngResult = mysql_query($Selstr, $tix_db_connection);
if (mysql_num_rows($EngResult) != 0)
{
$EngRow = mysql_fetch_row($EngResult);
}
#----- Query snortdata.event table to find all the cids for a particular tix_id ---------
$db_connection = mysql_connect($db_host, $db_user, $db_passwd);
mysql_select_db($db_database);
$Selstr = " SELECT event.cid FROM event WHERE event.tix_id=$theTix_number";
$cidResult = mysql_query($Selstr, $db_connection);
$the_cid_string = "";
if (mysql_num_rows($cidResult) != 0)
{
$cidrow = mysql_fetch_row($cidResult);
$the_cid_string = $cidrow[0];
for ($i=1; $i < mysql_num_rows($cidResult); $i++)
{
$cidrow = mysql_fetch_row($cidResult);
$the_cid_string .= ",".$cidrow[0];
}
}
#----- Query history table in mtstix database to populate the screen --------
$Selstr = "SELECT history.timestamp, engineer.eng_name, eng_rank.rank_name, status.status_name, history.notes ";
$Selstr .= "FROM history, engineer, eng_rank, status ";
$Selstr .= "WHERE tix_id=$theTix_number AND engineer.engineer_id=history.engineer_id AND ";
$Selstr .= "status.status_id=history.status_id AND engineer.rank_id=eng_rank.rank_id";
$HistResult = mysql_query($Selstr, $tix_db_connection);
#--------- Present Ticket History Screen -----------------------------------------------------------
print "<input type=\"submit\" name=\"tix_database\" value=\"Update Ticket\">\n";
print "<a href=\"tixviewall.php\">View Tickets</a>\n";
print "<table width=\"100%\" bgcolor=\"blue\">\n";
print "<tr><th align=\"center\"><b>Ticket History</b></th></tr>\n";
print "<tr bgcolor=\"blue\"><td>\n";
print " <table width=\"100%\" border=\"0\" bgcolor=\"orange\">\n";
print " <tr><td width=\"120\" align=\"left\"><b>Ticket Number:</b></td>\n";
print " <td width=\"150\" align=\"left\"><b>Current Engineer:</b></td>\n";
print " <td width=\"150\" align=\"left\"><b>Creation Date:</b></td>\n";
print " <td width=\"120\" align=\"left\"><b>Current Status:</b></td>\n";
print " <td align=\"left\"><b>Title:</b></td></tr>\n";
print " <tr><td align=\"left\">$tixhdr[0]</td>\n";
print " <td align=\"left\">$EngRow[0] - $EngRow[1]</td>\n";
print " <td align=\"left\">$tixhdr[2]</td>\n";
print " <td align=\"left\">$tixhdr[3]</td>\n";
print " <td align=\"left\">$tixhdr[4]</td></tr>\n";
print " <tr><td align=\"right\"><b>Referenced<br>Log Numbers: </b></td>";
print " <td colspan=\"3\"><textarea readonly rows=\"2\" cols=\"35\">$the_cid_string</textarea></td></tr>\n";
print " </table></td></tr>\n";
print "<tr><td><hr></td></tr>\n";
print "<tr>\n";
print " <td>\n";
print " <table border=\"0\" width=\"100%\" bgcolor=\"purple\">\n";
if (mysql_num_rows($HistResult) != 0)
{
for ($i=0; $i < mysql_num_rows($HistResult); $i++)
{
$HistRow = mysql_fetch_row($HistResult);
print " <tr><td><table width=\"100%\" border=\"0\" bgcolor=\"purple\">\n";
print " <tr><td width=\"70\" align=\"left\"><b>Update#</b></td>\n";
print " <td width=\"150\" align=\"left\"><b>Date:</b></td>\n";
print " <td width=\"100\" align=\"left\"><b>Status:</b></td>\n";
print " <td align=\"left\"><b>Engineer:</b></td></tr>\n";
print " <tr><td >$i</td>\n";
print " <td >$HistRow[0]</td>\n";
print " <td >$HistRow[3]</td>\n";
print " <td >$HistRow[1] - $HistRow[2]</td></tr>\n";
print " <tr><td></td><td><b>Notes: </b></td></tr>\n";
print " <tr><td></td>\n";
print " <td colspan=\"3\"><textarea rows=\"5\" cols=\"95\" readonly>$HistRow[4]\n";
print " </textarea></td></tr>\n";
print " </table></td></tr>\n";
print " <tr><td><hr></td></tr>\n";
}
}
print " </table>\n";
print " </td>\n";
print "</tr>\n";
print "</table>\n";
#---- Pass on these variables -----
print "<input type=\"hidden\" name=\"theTix_number\" value=$theTix_number>";
}
?>
</body>
</html>