Millstream Computing

Subscribe to the RSS feed by copy/paste the link below

Hacking a Megger PAT4

Posted by Admin on 28. March, 2020

I bought a used Megger PAT4 which works great but I need to pull the test data off the machine to write reports and print test lables.

The Megger has a serial port but ther's no documentation, that I can find, as to the protocol. Using mincom, on Linux, and a serial lead I managed to find the correct response key, on the PC side to intitiate data transfer. How did I find the key? By connecting minicom and random keyboard mashing until I got a response from the Megger. Yay data! I suppose I could have scripted that huh.

Now all I have to do is:

Parse the data
Understand the data
Stick it into a MySQL database
Write some report and label making code.

Simple!

Python to the rescue!

The transmitted data is broken into sections:

assets
clients
faults
repairs
results
sites

So I just dumped each section's data into a separate table. Before this though I had to decipher the fields in each section. Ech data line has a section identifier letter in the first field, with a bunch of comma delineated data following. Understanding each field was the next, and most difficult, problem.

An example line for an asset:

D,25,6,"AV005","AVP","","","1",091118,091119,12,1,104

What does all that mean? There's no test result data in there, that's a sepaate section and it looks like:

A,25,091118,1409,1,56,255,-,-,-,>,0.000,0.00,-,-,0

What does all that mean? Note though that dates are stored with two digit years. Have we leaned nothing?

Now I haven't yet decoded all the fields but I have enough to determine the test results data and to print reports and PAT labels,

In MySQL I hae the following tables:

assets;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| asset_num     | int(11)      | NO   | PRI | NULL    |       |
| site          | int(11)      | YES  |     | NULL    |       |
| asset_id      | varchar(100) | NO   |     | NULL    |       |
| test          | varchar(100) | YES  |     | NULL    |       |
| serial        | varchar(100) | YES  |     | NULL    |       |
| name          | varchar(100) | YES  |     | NULL    |       |
| location      | varchar(100) | YES  |     | NULL    |       |
| test_date     | date         | YES  |     | NULL    |       |
| next_date     | date         | YES  |     | NULL    |       |
| test_interval | varchar(100) | YES  |     | NULL    |       |
| VA            | int(11)      | YES  |     | NULL    |       |
| m1            | int(11)      | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

results;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| asset_num  | int(11)      | YES  |     | NULL    |       |
| test_date  | date         | NO   | PRI | NULL    |       |
| test_time  | int(11)      | NO   | PRI | NULL    |       |
| user_num   | int(11)      | YES  |     | NULL    |       |
| m1         | int(11)      | YES  |     | NULL    |       |
| m2         | int(11)      | YES  |     | NULL    |       |
| e_bond_1   | double       | YES  |     | NULL    |       |
| e_bond_2   | double       | YES  |     | NULL    |       |
| e_bond_3   | double       | YES  |     | NULL    |       |
| insulation | double       | YES  |     | NULL    |       |
| VA         | double       | YES  |     | NULL    |       |
| e_leakage  | double       | NO   |     | NULL    |       |
| m4         | varchar(100) | YES  |     | NULL    |       |
| fault_num  | varchar(100) | YES  |     | NULL    |       |
| repair_num | varchar(100) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

The full tables set is:

| Tables_in_megger |
+------------------+
| assets           |
| clients          |
| faults           |
| repairs          |
| results          |
| sites            |
+------------------+

The other tables data are not vital to the use case fo this project, but are populated. Unfortunately magic numbers are used for mush of the other data and I have not yet decoded these. The table's fields were determined by manually comparing the CSV output to that displayed on the megger's screen. Luckily, enough of the raw CSV was in plain text.

Having done that I was able to write Python program to donload the data and parse into the MariaDb database for further processing.

Printing Labels and Reports

LibreOffice has good database connection and report producing so this is what I use to print reports and PAT labels. I was goung to use pure Python for both tasks but expediency won the day.

My printer is a Brother QL-700, which is usefule for labeling jam, component drawers, LAN cables and, more importantly for this application, LibreOffice will print to it.

The LibeOffice SQL query to list test results:

SELECT DATE_FORMAT( `results`.`test_date`,
 '%Y-%m-%d' ) AS `Test Date`,
TIME_FORMAT( `results`.`test_time` * 100,
 '%H:%i' ) AS `Test Time`,
`assets`.`asset_id` AS `Asset ID`,
`assets`.`serial` AS `Serial Num`,
`assets`.`test`,
`results`.`insulation` AS `Insulation Mohm`,
`results`.`e_bond_1` AS `E Bond-1 mA`,
`results`.`e_bond_2` AS `E Bond-2 mA`,
 `results`.`e_bond_3` AS `E Bond-3 mA`,
`results`.`e_leakage` AS `E Touch mA`,
`results`.`VA` AS `Measured VA`,
`results`.`fault_num` AS `Fault`,
`results`.`repair_num` AS `Repair`,
DATE_FORMAT( `assets`.`next_date`,
'%Y-%m-%d' ) AS `Next Date` 
FROM `megger`.`assets` AS `assets`,
`megger`.`results` AS `results` 
WHERE `assets`.`asset_num` = `results`.`asset_num` 
AND `assets`.`asset_id` LIKE '%AV%' 
AND YEAR( `results`.`test_date` ) = 2019 
ORDER BY `Test Date` ASC,
 `Asset ID` AS

Hacking a Megger PAT4

Printing Labels and Reports

Comments