#!/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 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 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 )