Hacking shell scripts

Using sh to store data in a mysql database

As you may know, I'm a network adminstrator and I need to know certian infomation about my network. One of the need to know, was what IP addresses are being used per subnet I've been assigned to manage and what IP address belongs to that MAC poping up on our Netware server consoles. This is how I did it.. Getting MAC addresses

Query Router for MAC address

#!/bin/sh
snmptable rthou00 public at.atTable > rthou00.tmp
cat rthou00.tmp | awk '{print $2 $3 $4 $5 $6 $7 $8}' | sed 's/"//g' >
rthou00.macs
cat rthou00.tmp | awk '{print $9}' | sed 's/"//g'  > rthou00.ips
paste rthou00.ips rthou00.macs > rthou00.macips
cat rthou00.macips | grep -v at > rthou00.fin

Getting IP addresses USED...

List of Subnets
filename: subnets
22
23
24
26
27
83
140
142
144
148
150
208
212

script to get data needed + nmap
#!/bin/sh
# cat the file subnets to nmap
# direct the output to a file $subnet.0

cat subnets | while read subnet; do
  nmap -sP 192.168.$subnet.0/24 > $subnet.0 
done

# Get ready to insert data into table
# ls the $subnet.0 files and massage it.
# pass the results to mysql client

day=`date +%x`
ls *.0 | while read nmapdata; do
  
  cat $nmapdata | grep -v Starting | grep -v Nmap | while read ipinfo; do

  mysql -uroot -psimple iphistory -e "INSERT INTO ipsused
VALUES('','${ipinfo}','${day}','${nmapdata}')"

  done

done

I also built a php front end to the data in the mysql database

<?
  $f = mysql_connect("localhost","root","simple") or die("Server DOWN");
  
if($submit) {
  $query = "SELECT * FROM ipsused WHERE subnet = '$subnet' AND addr_info <> ' '
  AND dateran = '$dateran'";
  $res=mysql_db_query("iphistory",$query);
  $totalips = mysql_numrows($res);
  if(!$res) {
   print "ERROR:" . mysql_error();
  } else {
    print "<TABLE>";
    print "<tr><th>Address
Info</th><th>Subnet</th></tr>";
    while($row=mysql_fetch_array($res)) {
      print "<tr><td>" . $row[1]. "</td><td>". $row[3].
"</td></tr>";
   }
  } 
   print "</TABLE>";
   print "Total IP addresses used of 256: "  . $totalips;
} else {

$allQry = "SELECT subnet,addr_info,dateran, count(*) AS Count
          FROM ipsused WHERE subnet <> 'NULL' AND addr_info <> ' ' AND
dateran = '11/30/00' GROUP BY subnet";

$getallTotals = mysql_db_query("iphistory",$allQry);
if(!$getallTotals) {
  print "ERROR: " . mysql_error();
} else {
  print "<TABLE>";
  print "<TR><TH>Date</TH><TH>Subnet</TH><TH>Total
IPS</TH></TR>";
    while($totals=mysql_fetch_array($getallTotals)) {
      // $totals[2] = $totals[2]-1;
      print
"<tr><td>$totals[2]</td><td>$totals[0]</td><td>$totals[3]</td></tr>";
    }
    print "</TABLE>";
}

   $qry1 = "SELECT DISTINCT subnet FROM ipsused";
   $result1=mysql_db_query("iphistory",$qry1);

   $qry2 = "SELECT DISTINCT dateran FROM ipsused";
   $result2=mysql_db_query("iphistory",$qry2);

   if((!$result1) || (!$result2)){
     print "ERROR: ". mysql_error();
   } else {
    print "<H3> See What IP address are used</H3>";
    print "<FORM ACTION=$PHP_SELF METHOD=post>";
    print "<SELECT NAME=subnet>";
    while($row=mysql_fetch_array($result1)) {
     print "<OPTION value=$row[0]>" . $row[0];
    }
     print "</SELECT>";
    print "<SELECT NAME=dateran>";
    while($row=mysql_fetch_array($result2)) {
     print "<OPTION value=$row[0]>" . $row[0];
    }
     print "</SELECT>";     
   } 
   print "<INPUT TYPE=submit NAME=submit VALUE=\"Select Subnet\">";
   print "</FORM>";
}



?>