Collect HMI trend logs over FTP into a database

The log file is the handoff point

Trend samples become collectable when the logger commits them to a file on the card. Remote access and the web interface serve screens and project files; FTP serves the data directory. They are separate services with separate faults, so test each with its own login and directory listing. A web page that will not load while FTP answers is a known split between the two services, and the web-server fault that leaves FTP running has its own writeup.

Design the collector around files, in this order:

1. The panel writes samples to log files on its card through every shift.
2. FTP exposes that directory to the collector host.
3. The collector downloads a file once its size has stopped growing.
4. The importer parses the text and inserts rows.
5. A unique database key absorbs repeats.

Two logger properties decide everything downstream: the rollover rule that closes one file and opens the next, and the flush behavior that moves buffered samples onto the card. Read the rollover rule before writing the file picker: how an HMI trend log file rolls over at midnight on USB covers date-based naming, and confirming oldest-file rollover on a full drive covers the card-full case. Flush behavior is panel-specific, so measure it once by watching the file size while the line runs, and carry that measurement into the lag estimate below.

Pick the poll interval from the logging rate

The poll interval sets the ceiling on database lag: sample interval + flush + at most one poll. Derive it from the sample rate and from the largest delay the data can carry.

• 60 seconds when trends sample between once per second and once per minute and someone watches the numbers during the shift. With 5-second samples and a 30-second flush measured on the panel, lag lands at 5 + 30 + 60 = 95 seconds.
• 5 minutes at one sample per minute or slower. Worst case sits near 6 minutes.
• 15 minutes when the data feeds shift or daily reports only. The pipeline loses nothing by the longer wait, and the panel serves fewer transfers.

Faster than 30 seconds buys little, because flush dominates the lag and the collector controls only its own half of the sum.

What you need

• The panel's FTP service enabled with an account for the collector, and the path to the data directory. Plain FTP sends the password as clear text, so keep the connection inside the plant network or reach it through a VPN; if the panel offers FTPS, use that.
• Logging enabled per trend to the card. If the storage choice is still open, can an HMI panel store PLC data for later retrieval? compares the options.
• One real exported file. Build the parser against its actual columns, delimiter, timestamp format and trend naming.
• A collector host that reaches the panel over the network; an office PC or small server is enough for the traffic in the scenario below.
• A database table with a unique key of trend name plus timestamp. Add a panel ID to that key when more than one panel feeds the same table.

Set up the collection loop

1. Read one real log file. Write down the columns, the delimiter, the timestamp format, and where the trend name lives, in the filename or in a column. Every later rule keys off these notes.
2. Create the table with the unique key. Illustrative schema, rename the columns to match step 1:

CREATE TABLE trend_rows (
  trend_name  TEXT NOT NULL,       -- from the filename or a column
  sample_ts   TIMESTAMP NOT NULL,  -- the panel's clock, not the import time
  value       REAL,
  UNIQUE (trend_name, sample_ts)
);

3. List the directory each cycle and pick files by the name pattern from step 1. Skip any file whose name and byte size match the last successful import.
4. Download in binary type. FTP is defined in RFC 959, which fixes the command set (USER, PASS, LIST, RETR), the reply codes, and an image transfer type that moves bytes unchanged. The protocol carries no file checksum, so the size check belongs in the script: compare the downloaded byte count to the directory listing.
5. Import complete files only. Treat a file as complete when its byte size is identical on two consecutive polls, or when it has rolled over and matches the listing on a later cycle. A file still growing fails the check and waits.
6. Parse and insert. Drop a trailing line that lacks its full fields, because an interrupted transfer can cut the last row in half; the next poll delivers it whole. Insert every row through the duplicate rule, so re-importing a file adds only the rows the table lacks.
7. Record state and retry. After each import, write name, size and row count to an imported-files table. Keep the downloaded file until the table's row count for that file equals the file's line count minus the header. A failed transfer retries on the next cycle; nothing marks a partial download as done.

Scenario: nine trends on a 2 GB card

A panel runs 9 trend screens, each sampling every 5 seconds, one line per sample at about 40 bytes, logging to a 2 GB card.

Daily volume per trend: 86,400 s ÷ 5 s = 17,280 samples. At 40 B per line that is 17,280 × 40 = 691,200 B, about 0.69 MB. Nine trends: 691,200 × 9 = 6,220,800 B, about 6.2 MB per day. The card holds 2,000,000,000 B as drive makers label it, so 2,000,000,000 ÷ 6,220,800 ≈ 321 days of trend history. Alarm logs and rollover copies take their share, which is why the card-full check stays in the loop.

Load per cycle at the 60-second schedule: 60 s ÷ 5 s = 12 new samples per trend, 12 × 40 B = 480 B, about 4.3 KB across nine trends. The name-and-size skip keeps most cycles down to a directory listing; the open file downloads only when the listing shows growth.

Lag: sample interval + flush + at most one poll = 5 s + measured flush + 60 s. With the flush measured at 30 s, the database trails the line by 95 seconds. Name the setup for what it is, file collection with a polling delay, and that number is the honest answer when someone asks for live data.

Deduplication in action: the open file re-imports on every cycle where its size changed, and each re-import adds 0 rows once (trend_name, sample_ts) already holds the file's rows. Yesterday's closed file, imported once at its final size, is skipped by name and size from then on.

How to check it worked

• Run the importer twice against the same file. The second run must add 0 rows; take the table count before and after to confirm.
• Interrupt a transfer mid-file, drop the network for a moment, then let the next cycle run. The size check rejects the short file, the retry fetches it whole, and the final row count equals the file's line count minus the header.
• Compare one trend's oldest and newest database timestamps against the panel's file for the same day, then repeat per trend. Nine trends means nine files and nine checks.
• Watch lag in the data: the newest row per trend should be younger than about 2 minutes on the standard schedule. Alert when a trend's newest row passes 10 minutes old, ten missed cycles, since a single missed cycle happens on a busy network.

When rows stop arriving

Work down the list in this order.

1. Confirm the panel still logs. Watch the current file on the card: a growing size means logging is alive and the fault is downstream. A full card is its own case; confirm oldest-file rollover before reading a missing file as a stopped logger.
2. Confirm FTP from the collector host: log in, list the directory, fetch one small file by hand. The web interface failing while FTP answers is the split covered at the top of this page.
3. Backfill by hand when the reports cannot wait: download the missing files, import them, and record them in the imported-files table so the next scheduled cycle skips them. For a quick spreadsheet view of a backfill, combining daily HMI log files into one CSV shows the merge.
4. Fix the collector last. Its own log shows where the cycle stopped: listing, download, parse or insert. Text that arrives scrambled or half-overwritten is a documented pattern; HMI data log looks mixed over FTP but fine once saved locally explains why the saved file reads clean and what the parser must tolerate. Leave the panel's project untouched while working the host side: changing the project to chase a script fault costs the very data being collected.

The decision

Recommendation: Run a 60-second poll with the consecutive-poll size check and a unique key of trend name plus timestamp when trends sample between once per second and once per minute and someone watches the numbers during the shift. Move to a 5-minute poll at one sample per minute or slower, and to 15 minutes when only shift or daily reports read the data. In every case, import only files that pass the size check, keep the raw files as the archive, and alert at 10 minutes of silence.

Several panels feeding one table take a panel ID in the unique key and schedules staggered by a minute. If the line also needs a visible status board built from the collected numbers, start at shop-floor screens and andon-style boards.

Questions