Import data to Mysql

Tags

BASH, mysql

Follwoing BASH script will loop trough all the files and import them to mysql database.

#!/bin/bash

# show commands being executed, per debug
#set -x

# define database connectivity
_db="db_name"
_db_user="user_name"
_db_password="pass_word"

# define directory containing CSV files
_csv_directory="/home/fakrul/cymru_log"

# go into directory
cd $_csv_directory

# get a list of CSV files in directory
_csv_files=`ls -1 *.txt`

# loop through csv files
for _csv_file in ${_csv_files[@]}
do
echo $_csv_file

mysql -u $_db_user -p$_db_password -h localhost -D $_db --local-infile <<QUERY_INPUT
LOAD DATA local INFILE '$_csv_file'
INTO TABLE asn_data
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
IGNORE 2 LINES
(report, ip, asn, time, comments, asn_name)
SET slno = NULL;

INSERT INTO file_name VALUES ('$_csv_file')
QUERY_INPUT
done

Google Chart from dynamic data

Tags

Dynamic Data, Google Chart, mysql, PHP

I have found Google Chart very interesting while creating chart from dynamically driven data. I have tried to put things together for better understanding.

 

1. Include mysql connection string:

<?php </span>

include “conn_string.php”;

?>

2. Load the AJAX API:

script type=”text/javascript” src=”jquery-1.7.1.min.js”>

// /javascript”>

3. Load the Visualization API for Column Chart

google.load(‘visualization’, ‘1’, {‘packages’:[‘corechart’]});

4. Set a callback to run when the Google Visualization API is loaded

  google.setOnLoadCallback(drawChart);

  function drawChart() {

     var data = google.visualization.arrayToDataTable([

     [‘Time’, ‘Bots’, ‘Openresolvers’, ‘Proxy’, ‘Malwareurl’, ‘Phishing’, ‘Bruteforce’, ‘Scanners’, ‘Spam’]

     <?php <o:p>

     //$sql_query = “SELECT DATE_FORMAT(time, ‘%Y-%m-%d’) AS time,report,count(ip) AS countip FROM asn_data WHERE DATE_FORMAT(time, ‘%Y-%m-%d’

) > ‘2012-10-31’ GROUP BY DATE_FORMAT(time,’%Y-%m-%d’),report”;

     $sql_query = “

          SELECT DATE_FORMAT(time, ‘%Y-%m-%d’) AS time,

          SUM(CASE WHEN report = ‘bots’ THEN ipc ELSE 0 END) AS bots,

          SUM(CASE WHEN report = ‘openresolvers’ THEN ipc ELSE 0 END) AS openresolvers,

          SUM(CASE WHEN report = ‘proxy’ THEN ipc ELSE 0 END) AS proxy,

          SUM(CASE WHEN report = ‘malwareurl’ THEN ipc ELSE 0 END) AS malwareurl,

          SUM(CASE WHEN report = ‘phishing’ THEN ipc ELSE 0 END) AS phishing,

          SUM(CASE WHEN report = ‘bruteforce’ THEN ipc ELSE 0 END) AS bruteforce,

          SUM(CASE WHEN report = ‘scanners’ THEN ipc ELSE 0 END) AS scanners,

          SUM(CASE WHEN report = ‘spam’ THEN ipc ELSE 0 END) AS spam

          FROM (

          SELECT count(ip) AS ipc, report, DATE(time) as time

          FROM  asn_data

          GROUP BY report, DATE(time)) i

          GROUP BY time;

”;

 

     $result = mysql_query($sql_query);

 

     while($row = mysql_fetch_assoc($result)){

          echo “,[‘{$row[‘time’]}’,{$row[‘bots’]},{$row[‘openresolvers’]},{$row[‘proxy’]},{$row[‘malwareurl’]},{$row[‘phishing’]},{$row[‘br

uteforce’]},{$row[‘scanners’]},{$row[‘spam’]}]rn”;

     }

     ?>

     ]);

Lets explain what I have done here. First we have create a function name drawChart() and fetch the required data from mysql. As per Google Chart documentation, bellow is the format of data source:

[ ‘Month, ‘Dhaka’, ‘Chittagong’, ‘Sylhet’, ‘Khulna’ ]

[ ‘2011/05’, 150,     200,          210,       130  ]

[ ‘2011/06’, 180,     190,          215,       210  ]

[ ‘2011/07’, 196,     176,          190,       155  ]

To match format we have writer down the mysql query in such way that it will give output in desired format. Later using while loop we echo the output.

 

5. Options for the graph:

var options = {

          title : ‘Category Counts’,

          vAxis: {title: “Counts”},

          hAxis: {title: “Date”},

          seriesType: “bars”,

          series: {5: {type: “line”}}

        };

6. Call the variable and create graph:

var chart = new google.visualization.ComboChart(document.getElementById(‘chart_div’));

chart.draw(data, options);

7. Lastly use HTML tag to print the chart_div.

You can get the complete script from http://pastebin.com/HbfAdFas

Here is few sample graphs:

Monitor Network~PERL Script

Tags

GSM, monitor, My Work, mysql, network, perl, ping, sms, sms server

I have written a perl script (!) which will monitor the network & integrate this script with SMS Server Tools. What it does is check the network in every 5 mins. If any host down sms it to me with host name and down time. If the host up again it sms me the uptime. In the script following perl module are used :

use File::Copy;

use File::stat;

use DBI;

use Net::Ping::External qw(ping);

Hostnames are taken from MySql Database. Database has the followind table structure:

+——————-+———————+———+——-+————-+————————+

| Field               | Type               | Null   | Key | Default    | Extra |

+——————-+———————+———+——-+————-+————————+

| sl_no              | int(11)             | NO    | PRI   | NULL | auto_increment |

| ip_address         | varchar(15)         | YES   |       | NULL |                               |

| flag               | varchar(1)          | YES   |       | NULL |                              |

| description        | varchar(100)        | YES   |       | NULL |                              |

+——————-+———————+———+——-+————-+————————+

Complete perl script:

#!/usr/bin/perl

#———————————————————————————————-

# This script check reachability of remote hosts on a network

# fakrul@fakrul.com

# You only need to change the phone no in this file.

#———————————————————————————————-

use Net::Ping;

use File::Copy;

use File::stat;

use DBI;

use Net::Ping::External qw(ping);

#———define the phone no to send sms

my @phone_no =

  qw( 123456789 987654321);

#——-define the mysql variables

my $dsn = ‘dbi:mysql:sms:localhost:3306’;

my $user = “root”;

my $pass = “my_sql_password”;

my $sms_log = “/var/spool/sms/outgoing/output.” . time;

#——-time function

@months = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);

@weekDays = qw(Sun Mon Tue Wed Thu Fri Sat Sun);

($second, $minute, $hour, $dayOfMonth, $month, $yearOffset, $dayOfWeek, $dayOfYear, $daylightSavings) = localtime();

$year = 1900 + $yearOffset;

$theTime = “$hour:$minute $weekDays[$dayOfWeek] $months[$month] $dayOfMonth, $year”;

#——-create connection string

my $dbh = DBI->connect($dsn, $user, $pass) or die “Can not connect to the DB: $DBI::errstrn”;

#——-execute sql query

my $q_sth = $dbh->prepare(“select * from sms_host”);

$q_sth->execute;

while(@row = $q_sth->fetchrow_array()) {

        $alive = ping(hostname => $row[1], count => 6, size => 64, timeout => 100);

        if ($alive eq “1” ) {

                if ($row[2] eq “D”) {

                        my $qd_update = $dbh->prepare(“update sms_host set flag=’U’ where sl_no=’$row[0]’”) or die “Cannot prepare: ” . $dbh->errstr();

                        $qd_update->execute;

                        $qd_update->finish();

                        print “$row[3] UP at $theTimen”;

                        #—————now send the sms 🙂

                        foreach my $phone_no (@phone_no) {

                                open (OUT, “>$sms_log.$phone_no.$row[1]”) or die “Can’t open log-file”;

                                print OUT “To: $phone_non”;

                                print OUT “n”;

                                print OUT “$row[3] UP at $theTimen”;

                                close(OUT);

                        }

                }

                else {

                print “Nothing to Do, Host $row[1] already upn”;

                }

        }

        else {

                if ($row[2] eq “U”) {

                        my $qu_update = $dbh->prepare(“update sms_host set flag=’D’ where sl_no=’$row[0]’”) or die “Cannot prepare: ” . $dbh->errstr();

                        $qu_update->execute;

                        $qu_update->finish();

                        print “$row[3] DOWN at $theTimen”;

                        sleep(1);

                        #—————now send the sms 🙂

                        foreach my $phone_no (@phone_no) {

                                open (OUT, “>$sms_log.$phone_no.$row[1]”) or die “Can’t open log-file”;

                                print OUT “To: $phone_non”;

                                print OUT “n”;

                                print OUT “$row[3] DOWN at $theTime n”;

                                close(OUT);

                        }

                }

                else {

                print “Host $row[1] already DOWNn”;

                }

        }

}