Merging Related Information from 2 Tables

[Giorgos Keramidas]

On Thu, 29 Oct 2009 10:38:56 -0500, Martin McCormick <martin at dc.cis.okstate.edu> wrote:
> This is probably going to be a hashing exercise but I am checking to see
> if any of the building blocks needed are already out there.
>
> The problem is simple to describe in that there are 2 tables. One is a
> DNS zone transfer table of all the A or Address records in a given zone
> or from several zones for that matter.  the other table is from the same
> zones and consists of text or TXT records. The only thing the 2 tables
> have in common is that some of the TXT records share the exact same name
> field as the A records so we should be able to display the important
> contents of the A and TXT records on the same line if their names match.
> The challenge is to do this quickly so some sort of hash function is
> needed to locate A and TXT records having the same name.

Hi Martin,

You should use a Perl or Python script, and a hash...

If you show us a few sample lines from the input file and how you want the
output to look, it shouldn't be too hard to quickly hack one of those
together.

With a short input file like this:

: keramida at kobe:/tmp$ cat input-file
: localhost       IN      A       127.0.0.1
: kobe            IN      A       127.0.0.1
: kobe            IN      TXT     "This is a test"

You can construct a hash map of hostname -> list of records in
Python with a relatively short script:

: #!/usr/bin/env python
:
: import re
: import sys
:
: are = None                      # a regexp for matching 'A' records
: txtre = None                    # a regexp for matching 'TXT' records
:
: try:
:     are = re.compile(r'^\s*(\S+)\s+[iI][nN]\s+[aA]\s+(((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)).*$')
:     txtre = re.compile(r'^\s*(\S+)\s+[iI][nN]\s+[tT][xX][tT]\s+(.*)$')
: except Exception, inst:
:     sys.stderr.write('regexp error: %s' % str(inst))
:     sys.exit(1)
:
: hosts = {}
:
: for l in sys.stdin.readlines():
:     l = l.rstrip('\n\r')
:     # Is this an A record?
:     m = are.match(l)
:     if m:
:         (name, addr) = (m.group(1), m.group(2))
:         rec = ('A', addr)
:         if not name in hosts:
:             hosts[name] = [rec]
:         else:
:             hosts[name].append(rec)
:     # Is this a TXT record?
:     m = txtre.match(l)
:     if m:
:         (name, text) = (m.group(1), m.group(2))
:         rec = ('TXT', text)
:         if not name in hosts:
:             hosts[name] = [rec]
:         else:
:             hosts[name].append(rec)
:
: print hosts

Running this script should produce something like:

: keramida at kobe:/tmp$ python martin.py < input-file 
: {'kobe': [('A', '127.0.0.1'), ('TXT', '"This is a test"')],
:  'localhost': [('A', '127.0.0.1')]}

When you have the hash map of hostname to record-list for each host, you
can select and print any combination of host<=>record from this hash.