Thirteenth edition of the N&O column / Spooks newsletter

(Date: Wed, 28 Apr 1999 21:15:50 GMT)

N&O database

A new project starts here today. AC created a N&O database and wrote an opening text for the project. It would be very nice if many people would co-operate in this project, so we are waiting for your help and suggestions. For the time being you can reach AC thru me.

The N&O Database - Storing, Sorting and Retrieving the Logs

Introduction

As megawatts of radio energy keyed for carrying coded informations are pumped into the thin air, the continuous flow of radio intercept logs pile up (approximately 600 records/month) forming a huge mass of data, which needs to be stored, sorted and if needed -retrieved for further analysis. For the time being, logs arrive by e-mail in free text form, which creates difficulties when storing and sorting (e.g. by frequency) is attempted.

The amount of accumulated data over the time goes beyond the limits of "manual" handling. Just look at the N&O Logs in this month, and estimate the total number of records by the end of the year, say 12 x 500 = 6000 (a modest guess) a pretty big mass of numbers and letters.

How can one analyze this mass for paralell transmissons, skeds or for frequencies of a particular station ?

The only way forward is to enter the existing and the new data into a searchable relational database, which will allow to filter, sort, analyze the data by a couple of keystrokes.

The idea is not new or revolutionary. When I discovered the WUN website, and fell in love with the N&O column and seing the logs it flashed through my mind right away: ...how can I find anything in this haystack ? Soon after this I looked into the technical possibilities and concluded at the followings:

incoming data should be pre-formatted - WUN format preferred-, or Sacha's, etc.- into records
records from emails should be stored away in a searchable relational database (dBase, Clipper, or similar)

I spent some time with trying to find an easy but satisfactory solution for this "worldwide data acquisition - centralized databasing" task. Frankly, there isn't any easy way. Let me summarize the problems in the followings:

For pre-formatting the intercepts into records, we all need to run a program on our confusers, which creates the same output record format. The -seamingly- easiest way is to type the data straight into an Excel worksheet (or even better into a database input record format), than email it to the person, who maintains the database. He could then import the records into the database, and that's it. So we all need the same version of Excel. Let's say MS Excel 7. Do you all have it ?

Probably, some of us have access to Excel 7, and we should start punching in our numbers and oddities for the relief of the poor "librarian". For those who doesn't, an input log form (similar to ham's station log book) is created and is going to be circulated soon.

A database is a database. There could be 1 PC and 1 only (and a dear operator) which keeps the data, i.e. the database. Where this PC should be? Who will be the custodian of the system? How can you access the data? This has to be worked out for everyone's satisfaction. I need your suggestions.

Regarding to the database program: as the Clipper is obsolete, and Excel is not a databasing program, the obvious choice is the Microsoft MS Access 7, which is a stable and good relational database program, having interfaces with Excel and other MS programs.

Yes, the database is created in Access 7, running, and has grown up to 460 records (still a baby)! Ary suggested an input form, which speeds up data input, saves eyesight with its big characters and already linked to the core database program. The program itself stores the following data:
Frequency in kHz, Called, Station, Code, UTC, Day, Date, Mode, Language, Speaker, Remarks

The called is the intended recipient, eg. VLB, or SYN, etc.
The Station is eg. LP, or Cherry Ripe, etc.
Code is Enigma-code.
UTC is the time (self explanatory, isn't it)
Day is Monday, Tuesday, etc. automatically calculated from the date.
Date format is dd/mm/yyyy (and no Y2K bug).
Mode has a pull down pick list (AM, AMSC, CW, MCW, USB, LSB, DSB, CAR, JAM, IMP)
Language has a pull down pick list (EE,SS,GE,HH,RR,CZ,SL,??)
Speaker could be YL,OM,NEU (pull down list, too)
Remarks - could be of any length (even for Charlie-Charlie-Romeo-Whiskeys & Co - better not though)

I know, the question is on your tongue(s) now: what is all that for ? The database is organized into records, each record has a primary key, which is the frequency in kHz. The records are unsorted as entered, as the new records always added to the end of the list of the existing (stored) ones. However, with a keystroke, all records are sorted in ascending or descending orders of frequencies. Sorting by station names, etc. does not make any sense (to me).

The real beauty in the "databasing" is when it comes to "filtering". In sort, records from the database could be selected on filtering attributes. Each data in the records could be a filtering attribute, except the name of the day. An example of filtering is the answer to the following request: Find all tx for "Atencion" ! The result is the list of all recorded Atencion traffic (could be printed, as well). The filtering attributes could be combined, such as: "All Atencion tfc on 13125 kHz in January" ... etc. The process is quite simple: click on the attribute (eg. on one "Atencion") and click on the funnel symbol: voila- the job is done, you may print it out.

Here I have to finish today. Next month we could go into more details, and include examples, screen dumps for reference. A final word, however: would you have access to Access 7, please let me know. For your convenience, I can email a copy of the database program for you.

[AC]