Simple jQuery: how to validate IPv4 addresses and netmasks

Unfortunately jQuery doesn’t come with default form validation to check for ip-addresses or subnet masking. So without a long winded explanation here’s the code. Just include this as a separate JS file like the rest of your page’s JS.

// 'ipv4': IPv4 Address Validator
$.validator.addMethod('ipv4', function(value) {
    var ipv4 = /^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$/;    
    return value.match(ipv4);
}, 'Invalid IPv4 address');

// 'netmask': IPv4 Netmask Validator
$.validator.addMethod('netmask', function(value) {
    var mask = /^[1-2]{1}[2,4,5,9]{1}[0,2,4,5,8]{1}\.
[0-2]{1}[0,2,4,5,9]{1}[0,2,4,5,8]{1}\.
[0-2]{1}[0,2,4,5,9]{1}[0,2,4,5,8]{1}\.
[0-9]{1,3}$/;    
    return value.match(mask);
}, 'Invalid IPv4 netmask');

You can use it like this.

$("#myform_here").validate({
    rules:{
	ipaddress:{
	    required:true,
	    ipv4:true
	},
	netmask:{
	    required:true,
	    netmask:true
	}
.
.
.
Tagged , ,

Bash scripting: ElasticSearch and Kibana init.d scripts

As a follow up to the previous post about logstash, here are a couple of related init scripts for anyone implementing the OpenSource Log Analytics setup that is explained over at divisionbyzero. These have been tested on CentOS 6.3 and are based on generic RC functions from Redhat so they will work with Redhat, CentOS, Fedora, Scientific Linux, etc.

Tagged , , , , ,

Bash scripting: an improved init.d service for LogStash

LogStash is a great program, but I’m not going to get into that topic right now. So simply put, if you are aware of LogStash being great and are using it but feel like you need an init script to run it as a service then here’s the most recent and best (according to my personal functionality testing of all of the existing logstash init scripts available online) init script available: download the improved init script from my repo.

Note: this was originally written by Michael Ladd but was lacking some functionality to run on CentOS 6.3; so this is an improved version of that script.

Tagged , , ,

The “Big Data” buzzword finally gets a real definition

We’ve all heard the term “Big Data” thrown around a fair amount in the last several years ever since the rise of Hadoop and other distributed storage methods. But defining “Big Data” has always been a subjective term that hinges on perspective; what one engineer considers big can be vastly different than another’s.

However, there’s finally a definite description that says Big Data no matter what perspective you operate from: “That facility by my calculations that I submitted to the court for the Electronic Frontiers Foundation against NSA would hold on the order of 5 zettabytes of data. Just that current storage capacity is being advertised on the web that you can buy. And that’s not talking about what they have in the near future.” You can read more about the facility and its purpose here: http://rt.com/usa/news/surveillance-spying-e-mail-citizens-178/

Tagged , , ,

Simple MySQL: using TRIGGERs to keep datetime columns updated without direct SQL calls

If you’ve ever used non-opensource code, or applications that you don’t have complete control over, then you may have run into situations you need to alter data on a per-row basis but been unable to do so for lack of application SQL access. The solution to this type of problem is to use a MySQL TRIGGER, which allows us to execute arbitrary SQL commands when defined events occur. Why is this useful and how does it work? Well…

For example, I have a freeRADIUS server that uses MySQL as a backend for the user authentication, and one of my server applications (HostBill) provides a freeRADIUS plugin that allows my users to manage their RADIUS accounts; however the default freeRADIUS schema lacks a DATETIME column on the user table. When a user is created (INSERT) or has their password changed (UPDATE) I have no row data that tells me the dates when these operations were issued. Typically this would be a trivial change: issue an ALTER TABLE statement to add two columns and then add some NOW() statements to the application’s SQL calls.

However, the problem is that the application in question is ion-cube encrypted so I cannot make SQL changes to add support for my ‘date_created’ and ‘date_modified’ columns. I could intercept the TCP steams via MySQL Proxy or a custom script but that’s quite silly to have to do for this. Other methods also exist but are rather hack-ish.

MySQL TRIGGERs easily solve this situation. Here are two TRIGGERs, one that runs on INSERT and the other on UPDATE. Now the database can have ‘date_created’ and ‘date_modified’ data without any application changes. Whenever a row is inserted into the table the ‘date_create’ column will be populated via the NOW() function and correspondingly when a row is modified the ‘date_modified’ column will be updated with the NOW() function.

DELIMITER |
CREATE TRIGGER trigger_radcheckDatetimeInsert BEFORE INSERT ON radcheck FOR EACH ROW 
BEGIN
    SET NEW.date_create = NOW();
END; 
|
CREATE TRIGGER trigger_radcheckDatetimeModify BEFORE UPDATE ON radcheck FOR EACH ROW 
BEGIN
    SET NEW.date_modify = NOW();
END; 
|
DELIMITER ;
Tagged , , ,

OpenCode: MySQL procedures + python + shell code repositories now public

I write a fair number of scripts on this site and have posted a lot of code over the years. Generally if I am not pasting the code to be viewed on the webpage then I link to a file that a user can download; which leads to a lot of mish-mash code that doesn’t have a home. I’ve always kept the code files in a private SVN repo over the years but have recently moved them all to BitBucket Git repositories. So here they are: lots of code samples and useful bits of programming to save time.

Generic Shell Scripts: https://bitbucket.org/themattreid/generic-bash-scripts/src
Generic Python Scripts: https://bitbucket.org/themattreid/generic-python-scripts/src
Generic MySQL Stored Procs: https://bitbucket.org/themattreid/generic-sql-scripts/src

I’ll add more code to those as time goes on.

Tagged , , ,

Simple MySQL: Converting ANSI SQL to SQLite3

I was digging through some old project code and found this script. Sometimes one finds oneself in an odd situation and needs to convert regular SQL, say from a MySQL database dump, into SQLite3 format. There’s not too much else to say, but here is a script that helps with the process. It can likely be improved but this handles the items that came up during conversion on initial runs.

#!/bin/sh
####
# NAME: convert-mysql-to-sqlite3.sh
# AUTHOR: Matt Reid
# DATE: 2011-03-22
# LICENSE: BSD
####
if [ "x$1" == "x" ]; then
   echo "Usage: $0 "
   exit 
fi 
cat $1 |
grep -v ' KEY "' |   
grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' |
sed '/^SET/d' |          
sed 's/ unsigned / /g' | 
sed 's/ auto_increment/ primary key autoincrement/g' |
sed 's/ smallint([0-9]*) / integer /g' | 
sed 's/ tinyint([0-9]*) / integer /g' |  
sed 's/ int([0-9]*) / integer /g' |      
sed 's/ character set [^ ]* / /g' |      
sed 's/ enum([^)]*) / varchar(255) /g' | 
sed 's/ on update [^,]*//g' |            
perl -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' |
perl -pe '               
  if (/^(INSERT.+?)\(/) { 
     $a=$1;               
     s/\\'\''/'\'\''/g;   
     s/\\n/\n/g;          
     s/\),\(/\);\n$a\(/g; 
  }                       
  ' > $1.sql              
cat $1.sql | sqlite3 $1.db > $1.err   
ERRORS=`cat $1.err | wc -l`           
if [ $ERRORS == 0 ]; then             
  echo "Conversion completed without error. Output file: $1.db"
  rm $1.sql         
  rm $1.err         
    rm tmp          
else                
   echo "There were errors during conversion.  Please review $1.err and $1.sql for details."
fi
Tagged , ,

MySQL: a convenient stored procedure for memory usage reporting

If you’ve ever been troubleshooting on the MySQL command line and needed to quickly see how much memory is being used then you’ve probably noticed that there are no built in commands to give you this data. Unlike other enterprise databases MySQL doesn’t have a very robust management system built in to help make the DBA’s life easier. It doesn’t come with built in Stored Procedures to report on usage statistics or generate handy reports; so we have to code them and import them to MySQL — no relying on Oracle to help us out here.

So, here’s a stored procedure that can be imported to MySQL and run whenever you need to see the memory usage statistics. Installation and usage info is built into the SP below. The SP can also be downloaded from the repo: https://bitbucket.org/themattreid/generic-sql-scripts/src/15c75632f1af/mysql-memory-report-storedproc.sql

#####################################################################                                                                                                                                                           
## NAME: memory_report_sp.sql                                                                                                                                                                                                   
## AUTHOR: Matt Reid                                                                                                                                                                                                            
## SITE: http://themattreid.com                                                                                                                                                                                                 
## DATE: 2012-09-02                                                                                                                                                                                                             
## LICENSE: GPL v3                                                                                                                                                                                                              
##                                                                                                                                                                                                                              
## INSTALL METHODS:                                                                                                                                                                                                             
##  a) install via linux shell                                                                                                                                                                                                  
##     $> mysql --user=root -p mysql < memory_report_sp.sql                                                                                                                                                                     
##  b) install via mysql command line                                                                                                                                                                                           
##     mysql> use mysql; import memory_report_sp.sql                                                                                                                                                                            
##                                                                                                                                                                                                                              
## USAGE:                                                                                                                                                                                                                       
##  execute the stored procedure to generate the report                                                                                                                                                                         
##     mysql> use mysql;                                                                                                                                                                                                        
##     mysql> call memory_report();                                                                                                                                                                                             
##                                                                                                                                                                                                                              
## EXAMPLE OUTPUT:                                                                                                                                                                                                              
##  [localhost mysql://root@localhost/mysql > call memory_report();                                                                                                                                                             
##  +-----------------------------+----------+                                                                                                                                                                                  
##  | VARIABLE                    | VALUE    |                                                                                                                                                                                  
##  +-----------------------------+----------+                                                                                                                                                                                  
##  | TOTAL_BUFFERS_GLOBAL        | 420.00 M |                                                                                                                                                                                  
##  | TOTAL_BUFFERS_PER_THREAD    | 32.72 M  |                                                                                                                                                                                  
##  | MAX_CONNECTIONS_LIMIT       | 151      |                                                                                                                                                                                  
##  | MAX_CONNECTIONS_USED        | 2        |                                                                                                                                                                                  
##  | MAX_CONNECTION_USED_PERCENT | 1.32 %   |                                                                                                                                                                                  
##  | TOTAL_MEMORY_LIMIT          | 452.53 M |                                                                                                                                                                                  
##  | TOTAL_MEMORY_ACTIVE         | 47.44 M  |                                                                                                                                                                                  
##  | TOTAL_MEMORY_ACTIVE_PERCENT | 10.48 %  |                                                                                                                                                                                  
##  | HEAP_TABLE_LIMIT            | 16.00 M  |                                                                                                                                                                                  
##  | TEMP_TABLE_LIMIT            | 16.00 M  |                                                                                                                                                                                  
##  +-----------------------------+----------+                                                                                                                                                                                  
##  10 rows in set (0.02 sec)                                                                                                                                                                                                   
#####################################################################                                                                                                                                                           
                                                                                                                                                                                                                                
DELIMITER $$                                                                                                                                                                                                                    
DROP PROCEDURE IF EXISTS `memory_report` $$                                                                                                                                                                                     
CREATE PROCEDURE `memory_report` ()                                                                                                                                                                                             
BEGIN                                                                                                                                                                                                                           
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Declare some variables                                                                                                                                                                                                       
#####################################################################                                                                                                                                                           
DECLARE SUM_SGA BIGINT UNSIGNED;                                                                                                                                                                                                
DECLARE SUM_PGA BIGINT UNSIGNED;                                                                                                                                                                                                
DECLARE MAX_CONNECTIONS_LIMIT INT;                                                                                                                                                                                              
DECLARE MAX_CONNECTIONS_USED INT;                                                                                                                                                                                               
DECLARE CONNECTION_RATIO FLOAT;                                                                                                                                                                                                 
DECLARE TOTAL_HEAP BIGINT UNSIGNED;                                                                                                                                                                                             
DECLARE TOTAL_TEMPTABLE BIGINT UNSIGNED;                                                                                                                                                                                        
DECLARE k VARCHAR(255);                                                                                                                                                                                                         
DECLARE v BIGINT UNSIGNED;                                                                                                                                                                                                      
DECLARE TICK BOOL;                                                                                                                                                                                                              
                                                                                                                                                                                                                                
DECLARE MEM_LIMIT BIGINT UNSIGNED;                                                                                                                                                                                              
DECLARE MEM_USED BIGINT UNSIGNED;                                                                                                                                                                                               
DECLARE MEM_PERC FLOAT;                                                                                                                                                                                                         
DECLARE MAX_CONNECTION_USED_PERCENT FLOAT;                                                                                                                                                                                      
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Declare the queries                                                                                                                                                                                                          
#####################################################################                                                                                                                                                           
DECLARE GLOBALS CURSOR FOR SELECT                                                                                                                                                                                               
        VARIABLE_NAME, VARIABLE_VALUE                                                                                                                                                                                           
        FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES                                                                                                                                                                                
        WHERE VARIABLE_NAME IN                                                                                                                                                                                                  
                ('query_cache_size',                                                                                                                                                                                            
                'key_buffer_size',                                                                                                                                                                                              
                'innodb_buffer_pool_size',                                                                                                                                                                                      
                'innodb_additional_mem_pool_size',                                                                                                                                                                              
                'innodb_log_buffer_size',                                                                                                                                                                                       
                'read_buffer_size',                                                                                                                                                                                             
                'read_rnd_buffer_size',                                                                                                                                                                                         
                'sort_buffer_size',                                                                                                                                                                                             
                'thread_stack',                                                                                                                                                                                                 
                'join_buffer_size',                                                                                                                                                                                             
                'binlog_cache_size',                                                                                                                                                                                            
                'max_connections',                                                                                                                                                                                              
                'max_heap_table_size',                                                                                                                                                                                          
                'tmp_table_size')                                                                                                                                                                                               
        UNION                                                                                                                                                                                                                   
        SELECT VARIABLE_NAME, VARIABLE_VALUE                                                                                                                                                                                    
        FROM INFORMATION_SCHEMA.GLOBAL_STATUS                                                                                                                                                                                   
        WHERE VARIABLE_NAME IN                                                                                                                                                                                                  
                ('max_used_connections');                                                                                                                                                                                       
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
DECLARE CONTINUE HANDLER FOR NOT FOUND SET TICK = 1;                                                                                                                                                                            
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Set default values                                                                                                                                                                                                           
#####################################################################                                                                                                                                                           
SET SUM_SGA = 0;                                                                                                                                                                                                                
SET SUM_PGA = 0;                                                                                                                                                                                                                
SET MAX_CONNECTIONS_LIMIT = 0;                                                                                                                                                                                                  
SET MAX_CONNECTIONS_USED = 0;                                                                                                                                                                                                   
SET CONNECTION_RATIO = 0;                                                                                                                                                                                                       
SET TOTAL_HEAP = 0;                                                                                                                                                                                                             
SET TOTAL_TEMPTABLE = 0;                                                                                                                                                                                                        
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Query global variables                                                                                                                                                                                                       
#####################################################################                                                                                                                                                           
SET TICK = 0;                                                                                                                                                                                                                   
OPEN GLOBALS;                                                                                                                                                                                                                   
looper:LOOP                                                                                                                                                                                                                     
      FETCH GLOBALS INTO k,v;                                                                                                                                                                                                   
  IF TICK = 1 THEN                                                                                                                                                                                                              
    LEAVE looper;                                                                                                                                                                                                               
  END IF;                                                                                                                                                                                                                       
                                                                                                                                                                                                                                
    IF k in ('query_cache_size',
       'key_buffer_size',
       'innodb_buffer_pool_size',
       'innodb_additional_mem_pool_size',
       'innodb_log_buffer_size')                                                                                         
       THEN SET SUM_SGA = SUM_SGA + v;                                                                                                                                                                                          
    ELSEIF k in ('read_buffer_size',
       'read_rnd_buffer_size',
       'sort_buffer_size',
       'thread_stack',
       'join_buffer_size',
       'binlog_cache_size')                                                                                            
       THEN SET SUM_PGA = SUM_PGA + v;                                                                                                                                                                                          
    ELSEIF k in ('max_connections') THEN SET MAX_CONNECTIONS_LIMIT = v;                                                                                                                                                         
    ELSEIF k in ('max_heap_table_size') THEN SET TOTAL_HEAP = v;                                                                                                                                                                
    ELSEIF k in ('tmp_table_size','max_heap_table_size')                                                                                                                                                                        
     THEN SET TOTAL_TEMPTABLE = IF ((TOTAL_TEMPTABLE > v), TOTAL_TEMPTABLE, v);                                                                                                                                                 
    ELSEIF k in ('max_used_connections') THEN SET MAX_CONNECTIONS_USED = v;                                                                                                                                                     
                                                                                                                                                                                                                                
    END IF;                                                                                                                                                                                                                     
                                                                                                                                                                                                                                
END LOOP;                                                                                                                                                                                                                       
CLOSE GLOBALS;                                                                                                                                                                                                                  
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Output report                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
SET MEM_LIMIT = ROUND((SUM_SGA + (MAX_CONNECTIONS_LIMIT * SUM_PGA))/POW(1024,2),2);                                                                                                                                             
SET MEM_USED = ROUND((SUM_SGA + (MAX_CONNECTIONS_USED * SUM_PGA))/POW(1024,2),2);                                                                                                                                               
SET MEM_PERC = ROUND((MEM_USED * 100)/MEM_LIMIT,2);                                                                                                                                                                             
SET MAX_CONNECTION_USED_PERCENT = ROUND((MAX_CONNECTIONS_USED * 100)/MAX_CONNECTIONS_LIMIT,2);                                                                                                                                  
                                                                                                                                                                                                                                
SELECT "GLOBAL_BUFFERS_TOTAL" AS VARIABLE, CONCAT(ROUND(SUM_SGA/POW(1024,2),2),' M') AS VALUE UNION                                                                                                                             
SELECT "THREAD_BUFFERS_TOTAL", CONCAT(ROUND((SUM_PGA * MAX_CONNECTIONS_LIMIT)/POW(1024,2),2),' M') UNION                                                                                                                        
SELECT "THREAD_BUFFERS_EACH", CONCAT(ROUND(SUM_PGA/POW(1024,2),2),' M') UNION                                                                                                                                                   
                                                                                                                                                                                                                                
SELECT "MAX_CONNECTIONS_LIMIT", MAX_CONNECTIONS_LIMIT UNION                                                                                                                                                                     
SELECT "MAX_CONNECTIONS_USED", MAX_CONNECTIONS_USED UNION                                                                                                                                                                       
SELECT "MAX_CONNECTION_USED_PERCENT", CONCAT(MAX_CONNECTION_USED_PERCENT, ' %') UNION                                                                                                                                           
                                                                                                                                                                                                                                
SELECT "MEMORY_UTILIZATION_LIMIT", CONCAT(MEM_LIMIT,' M') UNION                                                                                                                                                                 
SELECT "MEMORY_UTILIZATION_ACTIVE", CONCAT(MEM_USED,' M') UNION                                                                                                                                                                 
SELECT "MEMORY_UTILIZATION_RATIO", CONCAT(MEM_PERC,' %') UNION                                                                                                                                                                  
                                                                                                                                                                                                                                
SELECT "HEAP_TABLE_LIMIT", CONCAT(ROUND(TOTAL_HEAP / POW(1024,2),2),' M') UNION                                                                                                                                                 
SELECT "TEMP_TABLE_LIMIT", CONCAT(ROUND(TOTAL_TEMPTABLE / POW(1024,2),2),' M') ;                                                                                                                                                
                                                                                                                                                                                                                                
END $$                                                                                                                                                                                                                          
DELIMITER ; 
Tagged , ,

TunnelMaker, a simple script to generate multi-hop SSH tunnels

SSH tunnels provide a very effective means to access remote services and applications. Not only does it provide encryption of data between hosts, but it allows you to route connections between a sequence of servers, thus chaining connections. A common use of this method is to provide encrypted connections to MySQL servers so that user accounts can be limited to only “localhost” privileges, yet accessed from remote workstations without having to run MySQL+SSL.

The concept is simple, for example let’s say you have three servers: localhost (your workstation in America), a server in Europe, and a server in Japan. You want to access Apache running on port 80 on the Japan server but because of firewall restrictions you cannot access port 80 remotely, and to make things more difficult the Japan server only allows SSH connections from the Europe server’s IP. We can solve this by creating a SSH tunnel that forwards localhost port 8080 (arbitrary port number) over an SSH connection to Europe, and then through another SSH tunnel to the Japan server’s port 80 to talk to Apache. Then we simply load http://localhost:8080 in our web browser and we’ll have access to Apache running on the server in Japan. You can think of the chain like this: localhost:8080->Europe:8080->Japan:80. The SSH command to create this chain is as follows: ssh -v -L 8080:localhost:8080 europe ssh -v -L 8080:localhost:80 -N japan

Since you are not limited to chaining between one or two hosts with this method, and you can choose different ports and services to forward, the opportunities are nearly endless. Here’s a script that makes building the tunnel commands easier.

#!/bin/bash
## NAME: TunnelMaker
## PURPOSE: Creates multi-hop SSH tunnels for forwarding data+connections
## AUTHOR: Matt Reid
## DATE: 2012-05-30
## VERSION: 1.0.2-jf
## SAMPLE: 
#   echo "ssh -v -L $localport:localhost:$remotehost1port $remotehost1 \
#   ssh -v -L $remotehost1port:localhost:$remotehost2port -N $remotehost2 ... repeat"

echo "------------------->"
echo "-->Tunnel-->Maker-->"
echo "-->version: 1.0.2-jf"
echo "-->themattreid.com"
echo "------------------->" 

## Get sequence value
echo -n "How many hops are we making [#remote servers]: "
read hops

## Check user, set initial port
user=`whoami`
notice=' [>1024]'
if [ "$user" = "root" ]; then notice=''; fi

## Initialize some vars
c=1             #session value counter
n=''            #session value for notice
p=''            #session value for port
final=''        #end result string for tunnel command
localport=''    #session value holder
let hops=hops+1 #increment hops for iteration in loop

## Build connection strings
while [  $c -lt $hops ]; do
    echo ""
    echo -n "Host[or IP] for hop#[$c]: "; read host
    echo -n "Localhost port for $host$notice: "; read localport
    echo -n "Destination port for $host$notice: "; read destport    

    if [ "$localport" = '' ]; then echo "no port selected. exiting." exit 1; fi
    if [ "$destport" = '' ]; then echo "no port selected. exiting." exit 1; fi
    if [ $c -gt 1 ]; then n="-N "; fi #added to suppress remote login

    string="ssh -v -L $localport:localhost:$destport $n$host "
    final="$final $string"

    let c=c+1
    p=$localport
done

echo "You can initiate your tunnels via command: \"$final\""
echo "Shall I start the tunnel now? [y,N]: "; read choice
if [ "$choice" = "Y" ] || [ "$choice" = "y" ]; then `$final`; else exit 0; fi

Here’s the sample output from our example with Europe and Japan.

> ./tunnelmaker 
------------------->
-->Tunnel-->Maker-->
-->version: 1.0.2-jf
-->themattreid.com
------------------->
How many hops are we making [#remote servers]: 2

Host[or IP] for hop#[1]: europe
Localhost port for europe [>1024]: 8080
Destination port for europe [>1024]: 8080

Host[or IP] for hop#[2]: japan
Localhost port for japan [>1024]: 8080
Destination port for japan [>1024]: 80

You can initiate your tunnels via command: " ssh -v -L 8080:localhost:8080 europe  ssh -v -L 8080:localhost:80 -N japan "
Shall I start the tunnel now? [y,N]: N
Tagged , , ,