#!/usr/bin/ruby ################################################################# # # Copyright (c) 2008,2010 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_cmd = Regexp.new( '^\t(\d+/\d+/\d+ \d+:\d+:\d+) pid (\d+) (.*)@(.*) (.*) \[(.*)\] \'(\w+-\w+) (.*)\'' ) re_cmd_noargs = Regexp.new( '^\t(\d+/\d+/\d+ \d+:\d+:\d+) pid (\d+) (.*)@(.*) (.*) \[(.*)\] \'(\w+-\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 new command line? # if (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("\\"){"\\\\"} 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 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 |