Location: PHPKode > projects > Snort Log Monitor and Ticketing System > snortlogmonitor/tixedit.php
<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>
Return current item: Snort Log Monitor and Ticketing System