log2sql.rb #3

  • //
  • guest/
  • pascal_soccard/
  • Scripts/
  • log2sql.rb
  • View
  • Commits
  • Open Download .zip Download (9 KB)
#!/usr/bin/ruby

#################################################################
#
# Copyright (c) 2008, Perforce Software, Inc.  All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions are met:
#
# 1.  Redistributions of source code must retain the above copyright
#     notice, this list of conditions and the following disclaimer.
#
# 2.  Redistributions in binary form must reproduce the above copyright
#     notice, this list of conditions and the following disclaimer in the
#     documentation and/or other materials provided with the distribution.
#
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
# ARE DISCLAIMED. IN NO EVENT SHALL PERFORCE SOFTWARE, INC. BE LIABLE FOR ANY
# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
# THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
#  
# = Description
#
#   Parse a Perforce server log (-v server=3) and produces a SQL
#   file or a text with tab separator as output.
#   log2sql does not parse vtrack output. See track2sql:
#   http://kb.perforce.com/P4dServerReference/Performance/Track2sql 
#
#   log2sql populates the following tables:
#   +------------+---------------+------+-----+
#   | P R O C E S S                           |
#   +------------+---------------+------+-----+
#   | Field      | Type          | Null | Key |
#   +------------+---------------+------+-----+
#   | processKey | int(11)       | NO   | PRI |
#   | start      | int(11)       | NO   |     |
#   | completed  | int(11)       | YES  |     |
#   | compute    | FLOAT(10,2)   | YES  |     |
#   | lapse      | int(10)       | YES  |     |
#   | running    | int(10)       | YES  |     |
#   | pid        | int(11)       | NO   |     |
#   | user       | varchar(255)  | NO   |     |
#   | workspace  | varchar(255)  | NO   |     |
#   | ip         | varchar(255)  | NO   |     |
#   | client     | varchar(255)  | NO   |     |
#   | cmd        | varchar(255)  | NO   |     |
#   | args       | text          | YES  |     |
#   +------------+---------------+------+-----+
#
# = Usage
#
#   log2sql.rb [-m text] | [-m sql] -d <database_name> log_file
#
# = Requirements
#  
#   Ruby: http://www.ruby-lang.org
#   SQL database if using sql output
#
#################################################################

require "parsedate"
require "getoptlong"

class Command
    def initialize(key, pid, start, running, user, workspace, ip, client, cmd, args)
	@key = key
	@pid = pid
	@start = start
	@running = running
	@compute = 0.0
	@completed = 0
	@lapse = 0
	@user = user
	@workspace = workspace
	@ip = ip
	@client = client
	@cmd = cmd
	@args = args
    end

    attr_accessor :key
    attr_reader :pid
    attr_accessor :start
    attr_accessor :running
    attr_accessor :compute
    attr_accessor :completed
    attr_accessor :lapse
    attr_accessor :user
    attr_accessor :workspace
    attr_accessor :ip
    attr_accessor :client
    attr_accessor :cmd
    attr_accessor :args

    def to_sql
	if completed == 0
	   sprintf("INSERT INTO process VALUES (%d,%d,\"%s\",NULL,%f,%d,%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\");\n",key,pid,start.strftime("%Y-%m-%d %H:%M:%S"),compute,lapse,running,user,workspace,ip,client,cmd,args)
        else
	   sprintf("INSERT INTO process VALUES (%d,%d,\"%s\",\"%s\",%f,%d,%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\");\n",key,pid,start.strftime("%Y-%m-%d %H:%M:%S"),completed.strftime("%Y-%m-%d %H:%M:%S"),compute,lapse,running,user,workspace,ip,client,cmd,args)
	end
    end

    def to_text
        sprintf("%d\t%s\t%f\t%s\t%d\t%d\t%s\t%s\t%s\t%s\t%s\t%s\n",pid,start.strftime("%Y-%m-%d %H:%M:%S"),completed.strftime("%Y-%m-%d %H:%M:%S"),compute,lapse,running,user,workspace,ip,client,cmd,args)
    end
end

def db_create_output(dbname)
    puts(sprintf("CREATE DATABASE IF NOT EXISTS %s;",dbname))
    puts(sprintf("USE %s;",dbname))
    puts("DROP TABLE IF EXISTS process;")
    puts("CREATE TABLE process (")
    puts(" processkey INT(10) UNSIGNED NOT NULL,")
    puts(" pid INT(10) UNSIGNED NOT NULL,")
    puts(" start DATETIME NULL,")
    puts(" completed DATETIME NULL,")
    puts(" compute FLOAT(10,2) UNSIGNED NOT NULL,")
    puts(" lapse INT(10) UNSIGNED NOT NULL,")
    puts(" running INT(10) UNSIGNED NOT NULL,")
    puts(" user VARCHAR(255) NOT NULL,")
    puts(" workspace VARCHAR(255) NOT NULL,")
    puts(" ip VARCHAR(255) NOT NULL,")
    puts(" client VARCHAR(255) NOT NULL,")
    puts(" cmd VARCHAR(255) NOT NULL,")
    puts(" args TEXT NULL,")
    puts(" PRIMARY KEY (processkey)") 
    puts(");")
end

def flush_output(cmds,mode)
    cmds.values.each do
        |p|
	if mode=="sql"
    	   puts( p.to_sql )
        elsif mode=="text"
    	   puts( p.to_text )
        end
    end
end

def croakusage
    puts("Usage: log2sql.rb [-m text] | [-m sql] -d <database_name> log_file" )
    exit(0)
end

######################
# START OF MAIN SCRIPT
######################

opts = GetoptLong.new(  [ "--dbname", "-d",         GetoptLong::OPTIONAL_ARGUMENT   ],
                        [ "--mode", "-m",            GetoptLong::OPTIONAL_ARGUMENT   ],
                        [ "--help", "-h",            GetoptLong::NO_ARGUMENT   ]
                     )
mode = "sql"
dbname = ""
opts.each do
    |opt,arg|
    if opt == "--mode" || opt == "-m"
        mode = arg
    elsif opt == "--dbname" || opt == "-d" 
        dbname = arg
    elsif opt=="--help" || opt == "-h" 
        croakusage
    end
end
if (mode != "text" && mode != "sql") || (mode == "sql" && dbname=="")
  croakusage
end

re_starting = Regexp.new( '^\tPerforce Server starting')
re_cmd = Regexp.new( '^\t(\d+/\d+/\d+ \d+:\d+:\d+) pid (\d+) (.*)@(.*) (.*) \[(.*)\] \'(user-\w+) (.*)\'' )
re_cmd_noargs = Regexp.new( '^\t(\d+/\d+/\d+ \d+:\d+:\d+) pid (\d+) (.*)@(.*) (.*) \[(.*)\] \'(user-\w+)\'' )
re_compute = Regexp.new( '^\t(\d+/\d+/\d+ \d+:\d+:\d+) pid (\d+) compute end (.*)s .*s' )
re_completed = Regexp.new( '^\t(\d+/\d+/\d+ \d+:\d+:\d+) pid (\d+) completed' )
re_track = Regexp.new( '^---')
cmds = Hash.new
key = 0

#
# Initial queries for sql mode
#
db_create_output(dbname) if mode == "sql"

line = $<.gets
while line
    next_line = false
    #
    # Is current line a "starting" line?
    #  
    if (match = re_starting.match(line))
       # need to deal with commands not completed
       flush_output(cmds,mode)
    #
    # Is current line a new command line?
    #
    elsif (match = re_cmd.match(line)) || (match = re_cmd_noargs.match(line))
          line = $<.gets
          next_line = true
          #
          # Is current line part of a vtrack block?
          #
          if !re_track.match( line )
             t = ParseDate.parsedate( match[1] )
             start = Time.local( t[0], t[1], t[2], t[3], t[4], t[5] )
             pid = match[2]
             user = match[3]
             workspace = match[4]
             ip = match[5]
             client = match[6]
             cmd = match[7]
	     args = match[8]
	     if args
             	args = args.gsub("\\"){"\\\\"}
	     end
             cmds[pid] = Command.new(key, pid, start, cmds.length(), user, workspace, ip, client, cmd, args)
             key = key + 1
	  else 
		line
          end
    #
    # Is current line a compute line?
    #
    elsif match = re_compute.match(line)
          pid = match[2]
          if cmds.has_key?( pid )
             compute = match[3].to_f
             # just sum the all the compute values of a same command
             cmds[pid].compute = cmds[pid].compute + compute
          end
    #
    # Is current line a completed line?
    #
    elsif match = re_completed.match(line)
          pid = match[2]
          if cmds.has_key?( pid )
             t = ParseDate.parsedate( match[ 1 ] )
             completed = Time.local( t[0], t[1], t[2], t[3], t[4], t[5] )
             lapse = completed - cmds[pid].start
             cmds[pid].completed = completed
             cmds[pid].lapse = lapse
             if mode == "sql"
                puts( cmds[pid].to_sql )
             elsif mode == "text"
                puts( cmds[ pid ].to_text )
             end
             # command can be removed from the hash
             cmds.delete( pid )
          end
    end
    if !next_line
       line = $<.gets
    end
end
#
# need to deal with commands not completed
#
flush_output( cmds,mode )
# Change User Description Committed
#13 10970 Pascal Soccard 1) Dealt with non-ascii characters that were breaking the script
The encode() method is only supported with Ruby 1.9 or greater.
With Ruby 1.8, I cannot (easily) deal with non ASCII characters.
If that matters to users, they need to switch to Ruby 1.9.
2) Escaping \ in user column
#12 8226 Pascal Soccard Increased size of circular list because on busy server, vtrack output
can be logged much later than the completed message. May increase
memory usage.
Database performance improvement by committing insert/update only after
all the rows have been inserted/updated.
This fix was necessary because of the dramatical performance
degradation after upgrading to Ubuntu 12.04 which probably uses
a newer Mysql
#11 8042 Pascal Soccard Fixed endTime for vtrack command
Added support for Broker log parsing
#10 7983 Pascal Soccard Forgot to submit as ktext
#9 7982 Pascal Soccard New improved version, includes now vtrack output parsing (same as
track2sql.php). See script header for more details.
#8 7705 Pascal Soccard Striped 0x0 characters in client value introduced by a buggy P4V version
#7 7647 Pascal Soccard Wrong type in sprintf call of the to_text method
#6 7562 Pascal Soccard "Perforce Server starting" block was incorrectly handled
#5 7483 Pascal Soccard Command parsing was not considering "dm-*" commands
#4 7470 Pascal Soccard Fixed an issue with " in command arguments
#3 7267 Pascal Soccard Fixed few bugs
#2 6471 Pascal Soccard Added new "running" field which gives the number of commands
not completed at the time the command was started
#1 6384 Pascal Soccard Added a Ruby Perforce log analyser