awk program to extract bitcoin handshake from debug.log files in ~/bin or ~/.bitcoin/log

debug-example.log

2013-04-10 12:50:01 ProcessBlock: ACCEPTED
2013-04-10 12:50:01 received block 000000000025452101193a1a2c17a9da68af6ed50daab562e79ff693b98abdb0
2013-04-10 12:50:01 connected 78.158.7.238:6333
2013-04-10 12:50:01 send version message: version 70001, blocks=11003, us=[2001:470:c019:0:213:72ff:fe7e:b4cb]:6333, them=78.158.7.238:6333, peer=78.158.7.238:6333
2013-04-10 12:50:01 SetBestChain: new best=000000000025452101193a1a2c17a9da68af6ed50daab562e79ff693b98abdb0  height=11004  work=7020901190660448  tx=11230  date=2013-04-04 12:51:08
2013-04-10 12:50:01 ProcessBlock: ACCEPTED
2013-04-10 12:50:19 connection timeout
2013-04-10 12:50:19 trying connection 184.22.74.21:6333 lastseen=0.4hrs
2013-04-10 12:50:19 connected 184.22.74.21:6333
2013-04-10 12:50:19 send version message: version 70001, blocks=11061, us=[2001:470:c019:0:213:72ff:fe7e:b4cb]:6333, them=184.22.74.21:6333, peer=184.22.74.21:6333
2013-04-10 12:50:20 SetBestChain: new best=00000000000f58825274612cd87b80fae426cb7fd9113053b6491d6ddd2b1a49  height=11062  work=7275991780677984  tx=11294  date=2013-04-04 14:31:15
2013-04-10 12:50:48 Added time data, samples 5, offset -28 (+0 minutes)
2013-04-10 12:50:48 nTimeOffset = -12  (+0 minutes)
2013-04-10 12:50:48 receive version message: version 70001, blocks=14298, us=66.234.123.28:4513, them=184.22.74.21:6333, peer=184.22.74.21:6333
2013-04-10 12:50:48 getblocks -1 to 0000000000000000000000000000000000000000000000000000000000000000 limit 500
2013-04-10 12:50:49 received block 000000000013fe066b60d21bcc4874d9081f392611f3bcf24c59b3ecb78d77ef

debug-example2.log

2013-05-07 11:01:16 init message: Done loading
2013-05-07 11:01:16 send version message: version 70001, blocks=18224, us=[2001:470:c019::2013:401]:6333, them=0.0.0.0:0, peer=127.0.0.1:0 
2013-05-07 11:01:16 ThreadRPCServer started
2013-05-07 11:01:17 connected 198.50.161.215:6333
2013-05-07 11:01:17 send version message: version 70001, blocks=18224, us=[2001:470:c019::2013:401]:6333, them=198.50.161.215:6333, peer=198.50.161.215:6333 
2013-05-07 11:01:17 6 addresses found from DNS seeds
2013-05-07 11:01:17 ThreadDNSAddressSeed exited
2013-05-07 11:01:17 Added time data, samples 2, offset -1 (+0 minutes)
2013-05-07 11:01:17 receive version message: version 70001, blocks=18224, us=66.234.123.28:4668, them=198.50.161.215:6333, peer=198.50.161.215:6333 
2013-05-07 11:01:17 trying connection 85.25.243.143:6333 lastseen=0.2hrs
2013-05-07 11:06:08 trying connection 198.199.83.44:6333 lastseen=1.0hrs
2013-05-07 11:06:08 connected 198.199.83.44:6333
2013-05-07 11:06:08 send version message: version 70001, blocks=18225, us=[2001:470:c019::2013:401]:6333, them=198.199.83.44:6333, peer=198.199.83.44:6333
2013-05-07 11:06:08 Added time data, samples 10, offset -1 (+0 minutes)
2013-05-07 11:06:08 receive version message: version 70001, blocks=18225, us=66.234.123.28:1198, them=198.199.83.44:6333, peer=198.199.83.44:6333
2013-05-07 11:06:09 trying connection 86.31.175.21:6333 lastseen=28.8hrs
2013-05-07 11:06:12 Added 1 addresses from 198.199.83.44: 55 tried, 3858 new
2013-05-07 11:06:14 connection timeout
2013-05-07 11:08:43 trying connection 220.244.29.202:6333 lastseen=3.1hrs
2013-05-07 11:08:44 accepted connection [2001:0:5ef5:79fb:a9:fbff:e7ea:8b70]:54338
2013-05-07 11:08:44 send version message: version 70001, blocks=18225, us=[2001:470:c019::2013:401]:6333, them=[2001:0:5ef5:79fb:a9:fbff:e7ea:8b70]:54338, peer=[2001:0:5ef5:79fb:a9:fbff:e7ea:8b70]:54338
2013-05-07 11:08:45 Added time data, samples 11, offset -2 (+0 minutes)
2013-05-07 11:08:45 nTimeOffset = -1  (+0 minutes)
2013-05-07 11:08:45 receive version message: version 70001, blocks=18225, us=[2001:470:c019:0:213:72ff:fe7e:b4cb]:6333, them=24.21.116.143:6333, peer=[2001:0:5ef5:79fb:a9:fbff:e7ea:8b70]:54338
2013-05-07 11:08:48 connection timeout
2013-05-07 11:08:49 trying connection 222.126.168.10:6333 lastseen=8.0hrs
2013-05-07 11:08:54 connection timeout
2013-05-07 11:08:54 trying connection 78.108.79.217:6333 lastseen=2.5hrs

prime.awk

#  -------------------------------------------------------------
#  -- parse bitcoin 0.8.1 debug.log files and create a .sql file
#  -- with the verified announce time of the peers.
#  -- Aubrey McIntosh, PhD, aka ProfMac
#  --
#  -- use sqlite3
#  -- .read 
#  --
#  -- updating with feedback from freenode::/#awk
#  -- 2018-03-28 15:48 Patsie
#  -- 2018-03-18 16:26 geirha
#  --   if the IP address has a single quote in it, it is illegal.
#  --   this program double quotes it, so it is legal input to sqlite,
#  --   and ignores the fact that it is an illegal value.
#  -------------------------------------------------------------

function Quote(item) {
  gsub(/'/, "''", item)
  return "'" item "'"
}

function SplitIpPort(ins) {
  n=split(ins, a, ":"); 
  addr_port = ""; 
  for (ix=1; ix<n-1; ix++) addr_port = addr_port a[ix] ":";
  Quote(addr_port a[ix]);
  addr_port = Quote(addr_port a[ix]) ", " a[n];
  return addr_port;
}

#  -------------------------------------------------------------

BEGIN {
  logname = "Fred"
  print "BEGIN TRANSACTION; -- v2.18 " 
  FS=" |=|,|, |\f|\n|\r"
  OFS=", "
  print "--CREATE TABLE tried(logline int, logtime varchar(19), \
    direction varchar(8), ""version int, blocks int, usip varchar(64), \
    usport int, themip varchar(64), themport int, peerip varchar(64),  \
    peerport int);"
  print "--  " "find . -name debug-0\"*\"[0123456789].log -exec awk -f ~/bin/prime.awk  {} \\; > temp.sql"
}

#  -------------------------------------------------------------
/version message: version/ {
  if (logname!=FILENAME) print "-- " FILENAME
  logname = FILENAME;
  print "INSERT INTO \"tried\" VALUES(" NR, Quote($1 " " $2),   \
    Quote($3), $7, $9 ", " SplitIpPort($11), SplitIpPort($13),   \
    SplitIpPort($15) ");"
  ;
}

#  -------------------------------------------------------------
END {
  print "-- SELECT DISTINCT usip FROM tried ORDER BY them LIMIT 25;" 
  print "COMMIT;"
}