Asterisk Queues Log In to Database

In order to properly manage ACD queues, it is important to be able to keep track of details of call setups and teardowns in much greater detail  than traditional call detail records provide. In order to support this,
extensive and detailed tracing of every queued call is stored in the  queue log, located (by default) in /var/log/asterisk/queue_log. 

For asterisk 1.6.1Above

Create a table queue_log in MYSQL DB

CREATE TABLE `asterisk.queue_log` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `time` int(10) unsigned default NULL,
  `callid` varchar(32) NOT NULL default '',
  `queuename` varchar(32) NOT NULL default '',
  `agent` varchar(32) NOT NULL default '',
  `event` varchar(32) NOT NULL default '',
  `data` varchar(255) NOT NULL default '',

  `data1` varchar(255) NOT NULL default '',
  `data2` varchar(255) NOT NULL default '', 

  `data3` varchar(255) NOT NULL default '',
  `data4` varchar(255) NOT NULL default '',
  PRIMARY KEY (`id`)
);

mysql> select * from queue_log;
+----+------------+--------------+------------------+-------+------------+-------+
| id | time       | callid       | queuename        | agent | event      | data  |
+----+------------+--------------+------------------+-------+------------+-------+
| 1  | 1198356717 | 1198356717.0 | voipsolutions.ru | NONE  | ENTERQUEUE | |serg |
| 2  | 1198356719 | 1198356717.0 | voipsolutions.ru | NONE  | ABANDON    | 1|1|2 |
+----+------------+--------------+------------------+-------+------------+-------+ 

Data field values is separated by Pipe(|) symbols 

CREATE TABLE IF NOT EXISTS `agent_status` (
 `agentId` varchar(40) NOT NULL default '',
 `agentName` varchar(40) default NULL,
 `agentStatus` varchar(30) default NULL,
 `timestamp` timestamp NULL default NULL,
 `callid` double(18,6) unsigned default '0.000000',
 `queue` varchar(20) default NULL,
 PRIMARY KEY  (`agentId`),
 KEY `agentName` (`agentName`),
 KEY `agentStatus` (`agentStatus`,`timestamp`,`callid`),
 KEY `queue` (`queue`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `call_status` (
 `callId` double(18,6) NOT NULL,
 `callerId` varchar(13) NOT NULL,
 `status` varchar(30) NOT NULL,
 `timestamp` timestamp NULL default NULL,
 `queue` varchar(25) NOT NULL,
 `position` varchar(11) NOT NULL,
 `originalPosition` varchar(11) NOT NULL,
 `holdtime` varchar(11) NOT NULL,
 `keyPressed` varchar(11) NOT NULL,
 `callduration` int(11) NOT NULL,
 PRIMARY KEY  (`callId`),
 KEY `callerId` (`callerId`),
 KEY `status` (`status`),
 KEY `timestamp` (`timestamp`),
 KEY `queue` (`queue`),
 KEY `position` (`position`,`originalPosition`,`holdtime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

And then Created a trigger on the DB asterisk

DROP TRIGGER IF EXISTS `asterisk`.`queueEvents`;

DELIMITER //

CREATE TRIGGER `asterisk`.`queueEvents` BEFORE INSERT ON `asterisk`.`queue_log`

FOR EACH ROW BEGIN

 IF NEW.event = 'ADDMEMBER' THEN

   INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES (NEW.agent,'READY',NEW.time,NULL) ON DUPLICATE KEY UPDATE agentStatus = "READY", timestamp = NEW.time, callid = NULL;

 ELSEIF NEW.event = 'REMOVEMEMBER' THEN

   INSERT INTO `agent_status` (agentId,agentStatus,timestamp,callid) VALUES (NEW.agent,'LOGGEDOUT',NEW.time,NULL) ON DUPLICATE KEY UPDATE agentStatus = "LOGGEDOUT", timestamp = NEW.time, callid = NULL;

 ELSEIF NEW.event = 'PAUSE' THEN

   INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES (NEW.agent,'PAUSE',NEW.time,NULL) ON DUPLICATE KEY UPDATE agentStatus = "PAUSE", timestamp = NEW.time, callid = NULL;

 ELSEIF NEW.event = 'UNPAUSE' THEN

   INSERT INTO `agent_status` (agentId,agentStatus,timestamp,callid) VALUES (NEW.agent,'READY',NEW.time,NULL) ON DUPLICATE KEY UPDATE agentStatus = "READY", timestamp = NEW.time, callid = NULL;

 ELSEIF NEW.event = 'ENTERQUEUE' THEN

   REPLACE INTO `call_status` VALUES (NEW.callid,replace(replace(substring(substring_index(NEW.data, '|', 2), length(substring_index(New.data, '|', 2 - 1)) + 1), '|', ''), '|', ''),'inQue',NEW.time,NEW.queuename,'','','','',0);

 ELSEIF NEW.event = 'CONNECT' THEN

   UPDATE `call_status` SET callid = NEW.callid,status = NEW.event,timestamp = NEW.time,queue = NEW.queuename,holdtime = replace(substring(substring_index(NEW.data, '|', 1), length(substring_index(NEW.data, '|', 1 - 1)) + 1), '|', '') where callid = NEW.callid;

INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES(NEW.agent,NEW.event,NEW.time,NEW.callid)ON DUPLICATE KEY UPDATEagentStatus = NEW.event,timestamp = NEW.time,callid = NEW.callid;

 ELSEIF NEW.event in ('COMPLETECALLER','COMPLETEAGENT') THEN

   UPDATE `call_status` SET callid = NEW.callid,status = NEW.event,timestamp = NEW.time,queue = NEW.queuename,originalPosition = replace(substring(substring_index(NEW.data, '|', 3), length(substring_index(NEW.data, '|', 3 - 1)) + 1), '|', ''),holdtime = replace(substring(substring_index(NEW.data, '|', 1), length(substring_index(NEW.data, '|', 1 - 1)) + 1), '|', ''),callduration = replace(substring(substring_index(NEW.data, '|', 2), length(substring_index(NEW.data, '|', 2 - 1)) + 1), '|', '') where callid = NEW.callid;

INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES (NEW.agent,NEW.event,NEW.time,NULL) ON DUPLICATE KEY UPDATE agentStatus = "READY", timestamp = NEW.time, callid = NULL;

 ELSEIF NEW.event in ('TRANSFER') THEN

   UPDATE `call_status` SET callid = NEW.callid,status = NEW.event,timestamp = NEW.time,queue = NEW.queuename,holdtime = replace(substring(substring_index(NEW.data, '|', 1), length(substring_index(NEW.data, '|', 1 - 1)) + 1), '|', ''),callduration = replace(substring(substring_index(NEW.data, '|', 3), length(substring_index(NEW.data, '|', 3 - 1)) + 1), '|', '') where callid = NEW.callid;

INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES(NEW.agent,'READY',NEW.time,NULL)ON DUPLICATE KEY UPDATE agentStatus = "READY",timestamp = NEW.time,callid = NULL;

 ELSEIF NEW.event in ('ABANDON','EXITEMPTY') THEN

   UPDATE `call_status` SET callid = NEW.callid,status = NEW.event,timestamp = NEW.time,queue = NEW.queuename,position = replace(substring(substring_index(NEW.data, '|', 1), length(substring_index(NEW.data, '|', 1 - 1)) + 1), '|', ''),originalPosition = replace(substring(substring_index(NEW.data, '|', 2), length(substring_index(NEW.data, '|', 2 - 1)) + 1), '|', ''),holdtime = replace(substring(substring_index(NEW.data, '|', 3), length(substring_index(NEW.data, '|', 3 - 1)) + 1), '|', '') where callid = NEW.callid;

 ELSEIF NEW.event = 'EXITWITHKEY'THEN

   UPDATE `call_status` SET callid = NEW.callid,status = NEW.event,timestamp = NEW.time,queue = NEW.queuename,position = replace(substring(substring_index(NEW.data, '|', 2), length(substring_index(NEW.data, '|', 2 - 1)) + 1), '|', ''),keyPressed = replace(substring(substring_index(NEW.data, '|', 1), length(substring_index(NEW.data, '|', 1 - 1)) + 1), '|', '') where callid = NEW.callid;

 ELSEIF NEW.event = 'EXITWITHTIMEOUT' THEN

   UPDATE `call_status` SET callid = NEW.callid,status = NEW.event,timestamp = NEW.time,queue = NEW.queuename,position = replace(substring(substring_index(NEW.data, '|', 1), length(substring_index(NEW.data, '|', 1 - 1)) + 1), '|', '') where callid = NEW.callid;

END IF;

 END

//

DELIMITER ;

Linux Monitoring Tools

Need to monitor Linux server performance? Try these built-in command and a few add-on tools. Most Linux distributions are equipped with tons of monitoring. These tools provide metrics which can be used to get information about system activities. You can use these tools to find the possible causes of a performance problem. The commands discussed below are some of the most basic commands when it comes to system analysis and debugging server

Monitoring the system:

pstree	Processes and parent-child relationships
top	Show top processes
iostat	Report CPU statistics and input/output statistics for devices and partitions.
ps -auxw	process status
uname -a	print system information
cat /proc/version	Display Linux kernel version in use.
cat /etc/redhat-release	Display Red Hat Linux Release. (also /etc/issue)
uptime	Tell how long the system has been running. Also number of users and system's load average.
w	Show who is logged on and what they are doing.
/sbin/lsmod	List all currently loaded kernel modules. Same as cat /proc/modules
/sbin/runlevel	Displays the system's current runlevel.
hostname	Displays/changes the system's node name. (Must also manually change hostname setting in /etc/sysconfig/network. Command will change entry in /etc/hosts)
service	Red Hat/Fedora command to display status of system services. Example: service --status-all Help: service --help

Process Management:

• pstree -p
  OR
• ps -auxw
  OR
• top
• kill <process-id-number>
• killall <command-name>

Memory Usage:

vmstat	Monitor virtual memory
free	Display amount of free and used memory in the system. (Also: cat /proc/meminfo)
pamp	Display/examine memory map and libraries (so). Usage: pmap pid
top	Show top processes
sar -B	Show statistics on page swapping.
time -v date	Show system page size, page faults, etc of a process during execution. Note you must fully qualify the command as "/usr/bin/time" to avoid using the bash shell command "time".
cat /proc/sys/vm/freepages	Display virtual memory "free pages". One may increase/decrease this limit: echo 300 400 500 > /proc/sys/vm/freepages
cat /proc/meminfo	Show memory size and usage.

 

  Filesystems and Storage Devices:

df -k	report filesystem disk space usage. (-k reports in Kbytes)
du -sh	Calculates file space usage for a given directory. (and everything under it) (-s option summarizes)
mount	Displays all mounted devices, their mount point, filesystem, and access. Used with command line arguments to mount file system.
cat /proc/filesystems	Display filesystems currently in use.
cat /proc/mounts	Display mounted filesystems currently in use.
showmount	Displays mount info for NFS filesystems.
cat /proc/swaps	Displays swap partition(s) size, type and quantity used.
cat /proc/ide/hda/any-file	Displays disk information held by kernel.

• cfdisk - Curses based disk partition table manipulator. (very nice)
• fdisk- Manipulate/configure the partition table.
• sfdisk - Manipulate/configure the partition table.
• fsck - Manipulate/configure the partition table. 

User Info:

who	Displays currently logged in users. Use who -uH for idle time and terminal info.
w	Displays currently logged in users and processes they are running.
whoami	Displays user id.
groups	Display groups you are part of. Use groups user-id to display groups for a given user.
set	Display all environment variables in your current environment.
id	Display user and all group ids. Use id user-id to display info for another user id.
last	Listing of most recent logins by users. Show where from, date and time of login (ftp, ssh, ...) Also see lastlog command. Show last 100 logins: last -100
history	Shell command to display previously entered commands.

Asterisk CLI commands

Asterisk commands
Introduction

Asterisk CLI supports large variety of commands which can be used for testing, configuration and monitoring. In this tutorial we will describe all commands available at the standard Asterisk version 1.4.0. We will divide this tutorial into few sections in order to facilitate the reading.

General CLI commands

! - Execute a shell command
abort halt - Cancel a running halt
cdr status - Display the CDR status
feature show - Lists configured features
feature show channels - List status of feature channels
file convert - Convert audio file
group show channels - Display active channels with group(s)
help - Display help list, or specific help on a command
indication add - Add the given indication to the country
indication remove - Remove the given indication from the country
indication show - Display a list of all countries/indications
keys init - Initialize RSA key passcodes
keys show - Displays RSA key information
local show channels - List status of local channels
logger mute - Toggle logging output to a console
logger reload - Reopens the log files
logger rotate - Rotates and reopens the log files
logger show channels - List configured log channels
meetme - Execute a command on a conference or conferee
mixmonitor - Execute a MixMonitor command.

moh reload - Music On Hold

moh show classes - List MOH classes
moh show files - List MOH file-based classes
no debug channel (null)
originate - Originate a call
realtime load - Used to print out RealTime variables.
realtime update - Used to update RealTime variables.
restart gracefully - Restart Asterisk gracefully
restart now - Restart Asterisk immediately
restart when convenient - Restart Asterisk at empty call volume
sla show - Show status of Shared Line Appearances
soft hangup - Request a hangup on a given channel
stop gracefully - Gracefully shut down Asterisk
stop now - Shut down Asterisk immediately
stop when convenient - Shut down Asterisk at empty call volume
stun debug - Enable STUN debugging
stun debug off - Disable STUN debugging
udptl debug - Enable UDPTL debugging
udptl debug ip - Enable UDPTL debugging on IP
udptl debug off - Disable UDPTL debugging

AEL commands

ael debug contexts - Enable AEL contexts debug (does nothing)
ael debug macros - Enable AEL macros debug (does nothing)
ael debug read - Enable AEL read debug (does nothing)
ael debug tokens - Enable AEL tokens debug (does nothing)
ael nodebug - Disable AEL debug messages
ael reload - Reload AEL configuration

Agents commands

agent logoff - Sets an agent offline
agent show - Show status of agents
agent show online - Show all online agents

AGI commands

agi debug - Enable AGI debugging
agi debug off - Disable AGI debugging
agi dumphtml - Dumps a list of agi commands in html format
agi show- List AGI commands or specific help
dnsmgr reload - Reloads the DNS manager configuration
dnsmgr status - Display the DNS manager status
http show status - Display HTTP server status

Console commands

console active - Sets/displays active console
console answer - Answer an incoming console call
console autoanswer - Sets/displays autoanswer
console boost - Sets/displays mic boost in dB
console dial - Dial an extension on the console
console flash - Flash a call on the console
console hangup - Hangup a call on the console
console mute - Disable mic input
console send text - Send text to the remote device
console transfer - Transfer a call to a different extension
console unmute - Enable mic input

 Core related commands

core clear profile - Clear profiling info
core set debug channel - Enable/disable debugging on a channel
core set debug - Set level of debug chattiness
core set debug off - Turns off debug chattiness
core set global - Set global dialplan variable
core set verbose - Set level of verboseness
core show applications - Shows registered dialplan applications
core show application - Describe a specific dialplan application
core show audio codecs - Displays a list of audio codecs
core show channels - Display information on channels
core show channel - Display information on a specific channel
core show channeltypes - List available channel types
core show channeltype - Give more details on that channel type
core show codecs - Displays a list of codecs
core show codec - Shows a specific codec
core show config mappings - Display config mappings (file names to config engines)
core show file formats - Displays file formats
core show file version - List versions of files used to build Asterisk
core show functions - Shows registered dialplan functions
core show function - Describe a specific dialplan function
core show globals - Show global dialplan variables
core show hints - Show dialplan hints
core show image codecs - Displays a list of image codecs
core show image formats - Displays image formats
core show license - Show the license(s) for this copy of Asterisk
core show profile - Display profiling info
core show switches - Show alternative switches
core show threads - Show running threads
core show translation - Display translation matrix
core show uptime - Show uptime information
core show version - Display version info
core show video codecs - Displays a list of video codecs
core show warranty - Show the warranty (if any) for this copy of Asterisk

 Database commands

database del - Removes database key/value
database deltree - Removes database keytree/values
database get - Gets database value
database put - Adds/updates database value
database show - Shows database contents
database showkey - Shows database contents

 Dialplan commands

dialplan add extension - Add new extension into context
dialplan add ignorepat - Add new ignore pattern
dialplan add include - Include context in other context
dialplan reload - Reload extensions and *only* extensions
dialplan remove extension - Remove a specified extension
dialplan remove ignorepat - Remove ignore pattern from context
dialplan remove include - Remove a specified include from context
dialplan save - Save dialplan
dialplan show - Show dialplan

 DUNDI commands

dundi debug - Enable DUNDi debugging
dundi flush - Flush DUNDi cache
dundi lookup - Lookup a number in DUNDi
dundi no debug - Disable DUNDi debugging
dundi no store history - Disable DUNDi historic records
dundi precache - Precache a number in DUNDi
dundi query - Query a DUNDi EID
dundi show entityid - Display Global Entity ID
dundi show mappings - Show DUNDi mappings
dundi show peers - Show defined DUNDi peers
dundi show peer - Show info on a specific DUNDi peer
dundi show precache - Show DUNDi precache
dundi show requests - Show DUNDi requests
dundi show trans - Show active DUNDi transactions
dundi store history - Enable DUNDi historic records

 GTalk & Jabber commands

gtalk reload - Enable Jabber debugging
gtalk show channels - Show GoogleTalk Channels
jabber debug - Enable Jabber debugging
jabber debug off - Disable Jabber debug
jabber reload - Enable Jabber debugging
jabber show connected - Show state of clients and components
jabber test - Shows roster, but is generally used for mog's debugging.

 IAX2 commands

iax2 provision - Provision an IAX device
iax2 prune realtime - Prune a cached realtime lookup
iax2 reload - Reload IAX configuration
iax2 set debug - Enable IAX debugging
iax2 set debug jb - Enable IAX jitterbuffer debugging
iax2 set debug jb off - Disable IAX jitterbuffer debugging
iax2 set debug off - Disable IAX debugging
iax2 set debug trunk - Enable IAX trunk debugging
iax2 set debug trunk off - Disable IAX trunk debugging
iax2 show cache - Display IAX cached dialplan
iax2 show channels - List active IAX channels
iax2 show firmware - List available IAX firmwares
iax2 show netstats - List active IAX channel netstats
iax2 show peers - List defined IAX peers
iax2 show peer - Show details on specific IAX peer
iax2 show provisioning - Display iax provisioning
iax2 show registry - Display IAX registration status
iax2 show stats - Display IAX statistics
iax2 show threads - Display IAX helper thread info
iax2 show users - List defined IAX users
iax2 test losspct - Set IAX2 incoming frame loss percentage

 
Manager commands

manager show command - Show a manager interface command
manager show commands - List manager interface commands
manager show connected - List connected manager interface users
manager show eventq - List manager interface queued events
manager show users - List configured manager users
manager show user - Display information on a specific manager user

MGCP commands

mgcp audit endpoint - Audit specified MGCP endpoint
mgcp reload - Reload MGCP configuration
mgcp set debug - Enable MGCP debugging
mgcp set debug off - Disable MGCP debugging
mgcp show endpoints - List defined MGCP endpoints

Module management

module load - Load a module by name
module reload - Reload configuration
module show - List modules and info
module show like - List modules and info
module unload - Unload a module by name

PRI commands

pri debug span - Enables PRI debugging on a span
pri intense debug span - Enables REALLY INTENSE PRI debugging
pri no debug span - Disables PRI debugging on a span
pri set debug file - Sends PRI debug output to the specified file
pri show debug - Displays current PRI debug settings
pri show spans - Displays PRI Information
pri show span - Displays PRI Information
pri unset debug file - Ends PRI debug output to file

 Queue commands

queue add member - Add a channel to a specified queue
queue remove member - Removes a channel from a specified queue
queue show - Show status of a specified queue
rtcp debug ip - Enable RTCP debugging on IP
rtcp debug - Enable RTCP debugging
rtcp debug off - Disable RTCP debugging
rtcp stats - Enable RTCP stats
rtcp stats off - Disable RTCP stats
rtp debug ip - Enable RTP debugging on IP
rtp debug - Enable RTP debugging
rtp debug off - Disable RTP debugging
say load - Set/show the say mode
show parkedcalls - Lists parked calls
show queue - Show information for target queue
show queues - Show the queues

SIP commands

sip history - Enable SIP history
sip history off - Disable SIP history
sip notify - Send a notify packet to a SIP peer
sip prune realtime - Prune cached Realtime object(s)
sip prune realtime peer - Prune cached Realtime peer(s)
sip prune realtime user - Prune cached Realtime user(s)
sip reload - Reload SIP configuration
sip set debug - Enable SIP debugging
sip set debug ip - Enable SIP debugging on IP
sip set debug off - Disable SIP debugging
sip set debug peer - Enable SIP debugging on Peername
sip show channels - List active SIP channels
sip show channel - Show detailed SIP channel info
sip show domains - List our local SIP domains.
sip show history - Show SIP dialog history
sip show inuse - List all inuse/limits
sip show objects - List all SIP object allocations
sip show peers - List defined SIP peers
sip show peer - Show details on specific SIP peer
sip show registry - List SIP registration status
sip show settings - Show SIP global settings
sip show subscriptions - List active SIP subscriptions
sip show users - List defined SIP users
sip show user - Show details on specific SIP user

Skinny commands

skinny reset - Reset Skinny device(s)
skinny set debug - Enable Skinny debugging
skinny set debug off - Disable Skinny debugging
skinny show devices - List defined Skinny devices
skinny show lines - List defined Skinny lines per device

Voicemail commands

voicemail show users - List defined voicemail boxes
voicemail show users for - List defined voicemail boxes for target context
voicemail show zones - List zone message formats

Zaptel commands

zap destroy channel - Destroys a channel
zap restart - Fully restart zaptel channels
zap show cadences - List cadences
zap show channels - Show active zapata channels
zap show channel - Show information on a channel
zap show status - Show all Zaptel cards status

Vi Commands Cheat Sheet

The ultimate Vi cheat sheet for anyone learning Vi Commands or the Vi editor in general. Keep this guide close by when using the editor and you will learn how to use Vi in no time. Vim commands will be the same as Vi for the most part – Windows however has Gvim which allows some GUI interaction.

I – Inserts text at the beginning of the text line, not the beginning column of the vi screen
a – Appends to the end of the right of the cursor
A – Appends at the end of the current line
o – Begins a new line below the current line
O – Drops the current line and begins a new one in its place

Vi Replace

cw – Vi replace a single word from the current cursor position. To replace a whole word, you put the cursor on the the first character of the word.
c$ – replace the current line but doesn’t extend to change the rest of a wrapped sentence on the screen
r – Vi Replace the character under the cursor
R – Replaced the text on the same line until Esc is pressed, but it doesn’t change text on the next line. Instead, it pushes to ahead of the current changes.

Vi Delete

 x – Deletes a single character under the cursor
X – Deletes a single character before the cursor
dw – Deletes a single word that’s currently under the cursor, from the cursor position onward.

Vi Delete Line

 dd – Vi delete line, regardless of the cursors position on the line
D – Deletes all text from the cursor position to the end of the line
dL – Deletes all text from the cursor position to the end of the screen
dG – Deletes all text from the cursor to the EOF
d^ – Deletes all text from the beginning of the line to the cursor

Vi Copy & Paste

 Commands for Vi copy & paste:

yy – Vi copy line – copies a line of text to the unnamed buffer
3yy – Copies 3 lines of text to the unnamed buffer
yw – Copies a word (under the cursor) to the unnamed buffer
3yw – Copies 3 words to the unnamed buffer
P – Pastes the contents 0f the unnamed buffer to the right of the cursor
p – Pastes the contents of the unnamed buffer to the left of the cursor

Navigation Within a File

This may confuse you to start with,
H - This is the left arrow; it’s easy to remember because it’s the leftmost key in the four key set
J – Use this for the down arrow; I remember this by thinking of jown instead of down.
K – This is the up arrow; I remember this by thinking of kup for up.
L – Use this for the right arrow; I remember this as L is right, which I always thought sounded dumb, it’s alright on the right side of the keyboard…

Vi Page Down

Ctrl+F – Vi page down – Moves forward a page
Ctrl+D – Moves forward half a page

Vi Page Up

Ctrl+B – Vi page up – Moves back a page
Ctrl+U – Moves backward a half-page

Named and Unnamed Buffers

“ayy – Pulls a line the named buffer (a), overwriting the current contents
“Ayy – Appends the current line to the buffer
“A3yy – Pulls three lines from the current cursor position and appends the lines to the A buffer
“ap – Pastes the a buffer to the right of the cursor (the case of the buffer letter is meaningless)

Vi Search

How to perform a Vi Search.
N – Vi Search forward
Shift+N – Search Backward

Vi Search and Replace

:s/bob/BOB/ – Replaces the first instance of bob with BOB
:s/bob/BOB/g – Replaces all instances of bob with BOB in that line (note g stands for global)
:%s/bob/BOB/g – Replaces all instances of bob with BOB in that file no matter how many exist or how many changes made to each line

Vi Search for Part of a Word

A fuzzy search allows you to find something that you only know part of, for example if you wanted to find all instances of lines starting with the word “Uber” you would use the following:

/^Uber

To find all instances of the word “ninja” at the end of a line you would use:

/ninja$

In some instances you’ll need to find what’s called a metacharacter. For example, say you wanted to find the instances in a file for the asterisk character (*), because it stands for many characters. You could use something like this:

/The \* character

Another example might be finding the text ninja, with the period being treated only as a period. Otherwise, you’d find ninjas, ninja?, ninja! and so on. To find JUST ninja you would use the following:

/ninja\.

Finally, matching a range of characters is handy, such as trying to find all instances of the version number string v2.9. You either have to perform several searches of use something like this:

/v2.[1-9]

The square brackets denote a single character, stretching from the first character to the one after the dash. If you wanted instead to find all versions of the word the, including THE, THe and tHE, you would use the following:

/ [tT] [hH [eE]

Options in Vi

set number
set tabstop=5
set noh1search
The above code should be placed in the .exrc file which is located in the users home dir.
There are more than 60 options available in vi, to view them all type

:set all

To find out about an options status type

:set optionname?

:set number – turns on line numbers
:set nonumber – turns the number option off

Advanced Vi commands

How to run external commands in vi:
Say for example you want to run “ls -l” inside of vi as you can’t remember a file name, you would enter:

:! ls -l

Pressing enter or command will return you to the vi session. If the output is more than one screen it is piped to the more command.

Joining lines in vi

Back space only works on current lines, so to join lines in vi you need to position the curser in either line and press Shift+J

Split windows in vi

When you are editing a file and want to see a different section of the file or even a different file altogether, you can use the following:
:split – This splits the window horizontally
:vsplit – this splits the file vertically, with the same file on each side
To switch between the windows, hit Ctrl+W
To edit two files in vi at the same time, open the first file and then type:

:split file2

To set the hight of the split window:

:15split /blah/file

The above will split the top 15 lines of the screen and display the contents of the /blah/file.
To close the split window, take focus by hitting Ctrl+W and then enter :close
Or to close all the other split windows, take focus of the main window and enter:

: only

This will close all other windows apart from your window :p

Vi Save

 :w – Vi Save, write the file out to disk
Vi Save & Exit

:q – Vi exit – this will close Vi
:wq – Vi save & exit
: x – Vi exit, and prompts it you want to save on exit.
Shift+ZZ - Alternative way to save and exit Vi
:q! – Exits vi and discards and changes you made
:wq! – Vi Save and exit if you are root and do not have the write bit set for the file you are attempting to write.

Misc / Additional

U – Vi Undo, easy to remember, enter U in command mode to undo the last command.
:+X+! – In command mode this will undo everything you have done since the last disk write.
Ctrl+G – Shows the file name, total number of lines and the current position expressed as a percentage of the total number of lines in the file.

Multipliers

Just about any keystroke or action can be done multiple times by prefixing it with a number.
For example to move the curser to line 5 you would press 5g. To move 12 words to the right you would enter 12W.

If you have any additional commands or questions / suggestions drop us a comment below.

Create new MySQL user

Nowadays security is more and more important even in case of smaller websites. As a lot of site uses some database so this is one point where we can make some improvements.

In this article we will work with MySQL on our goal is to create a new user in the database.

There are more ways how you can do this.

• Using CREATE USER and/or GRANT commands
• Inserting a new record into the mysql.user table
  

First let's see how to use the CREATE USER command. Here I have to mention that thi s command is available only in MySQL 5 (5.0.2) and newer releases. The syntax is the following:

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']

Here is an example:

Code: 

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'user1'@'localhost';
GRANT ALL ON *.* TO 'user2'@'localhost' IDENTIFIED BY 'pass1';


Database F1

Now if you check the mysql.user table you can find a new record in it. Notice that all priviliges are set to No so this user can do nothing in the DB. To add some preiviliges we can use the GRANT command as follows:

Passive Mode FTP with iptables

There’s lots of advice on the net about how to setup a server with iptables to allow passive mode FTP. Below is the approach that we’ve found to be most effective.
Start by configuring your FTP daemon to use a fixed range of ports. We use 41361 to 65534 which is the IANA registered ephemeral port range. The exact config depends on what FTP software you’re using:
vsftpd
Edit /etc/vsftpd/vsftpd.conf and add the following lines:

pasv_min_port=49152 
pasv_max_port=65534

proftpd
Edit /etc/proftpd.conf and add to the Global section:

</Global>
......
PassivePorts 49152 65534
</Global>

Now restart your FTP service so the changes take effect.
Next you’ll need to configure the ip_conntrack_ftp iptables module to load. On Redhat/CentOS just edit /etc/sysconfig/iptables-config and add “ip_conntrack_ftp” to the IPTABLES_MODULES like this:

IPTABLES_MODULES="ip_conntrack_ftp"

Now restart the iptables service:

/sbin/service iptables restart

You can verify that the correct port range has been registered with lsmod like this:

lsmod | grep conntrack_ftp

and you’ll get something like this:

ip_conntrack_ftp       41361  0
ip_conntrack           91621  2 ip_conntrack_ftp,xt_state

And that’s all it takes to get passive mode ftp working behind iptables.
One extra note: If your server is NATed behind a physical firewall then you’ll probable need to load the “ip_nat_ftp” iptables module.