Need help unraveling the format (time, string, float/decimal, integer/float/decimal) in .dat file

Question

The files (idx, dat) are from a plant/facility operational historian. The .dat file is supposed to have entries, each with identifier (tag name/id/key), value (decimal/float) for different times (timestamp) along with their confidence rating (int/percentage). The application is windows-based. Each .dat file would represent entries of few days.

I've managed to identify timestamps at specific byte offsets. It also looks like the "records" are of 2049 byte chunks. The timestamp (and additional timestamps) repeat within each 2049 chunk (which is a bit strange), but perhaps it's also keeping a reference to the last recorded time, or maybe there's a nesting (parent/child) of records.

I'm a stuck as to how I should proceed.

Sample dat file (truncated): https://drive.google.com/open?id=0B8ACIyH1qALnLUY2cEdzVWp6T1U

Full dat file: https://drive.google.com/open?id=0B8ACIyH1qALnNU5wYkotMktVWVk

One of the first time values: 80CAFEBB168DD201 at offset 2061 decimal [] (131322467930000000 -> 2017-02-27 7:19:53 AM)

Sample idx file: https://drive.google.com/open?id=0B8ACIyH1qALnQzVueEJuRWNfUk0

Appreciate the help.

UPDATE

As requested, some further information:

• The files are generated by Honeywell PHD Uniformance Server. I don't have access to the software itself - but working on it. I don't know what language it was written in.

• Some online guide for the server suggest the presence of MAX_ARCRECSIZE configuration parameter <quote>"which defaults to 2048. This controls the record size within the archive files" </endquote>. The dat files are of 2049 byte chunks (2048 bytes + 1 byte "0A")

• The historian, as far as I know, doesn't provide SQL-like interface. The only protocols understood are OPC variants (OPC-DA, OPC-HDA and possibly OPC-UA), however those are slow when it comes to extracting historical records.

• The offset given for one of the dates (but not the first one), is at 2061 decimal

  =>hexdump -C -s 2061 -n 64 SCAN00356-sample.dat
  0000080d  80 ca fe bb 16 8d d2 01  02 00 00 00 12 00 00 00  |................|
  0000081d  96 07 46 04 00 00 00 00  20 5f a0 02 c2 00 00 80  |..F..... _......|
  0000082d  bf 00 3c 00 00 00 00 00  00 00 04 00 00 00 96 07  |..<.............|
  0000083d  8e 07 80 ca fe bb 16 8d  d2 01 0a 00 00 86 07 00  |................|
  0000084d
  

• I've found 4 different timestamps (ftime) in each 2049 'chunks' so far. They are at consistent offsets (at bytes 12, 62, 75, 94 from the beginning of each 2049 byte chunk). Possible sequence value at byte 8. Possible 'color' value at byte 38.

• One more thing; OPC Servers leverage COM and DCOM architectures. I came across the Compound Binary File Format, but the corresponding readers aren't able to read the files.

Answer 1

This post consists of the results of preliminary analysis. Hopefully it saves other interested parties a little work.

Similar questions on SO

possible type of database that uses dat, idx files

How do I open .bin/.idx database files?

What database uses these specific type of .dat and .idx files?

And most interestingly:

How can I extract data from DAT and IDX files of SCADA CIMPLICITY software?

Preliminary Analysis

SCAN00356-sample.dat and SCAN00356.idx were examined. The full .dat file, SCAN00356.dat, is 4.0GB in size and too large load into memory on the VM being used and so has not been examined.

sample .dat file SCAN00356-sample.dat

The structure of SCAN00356-sample.dat is non-uniform:

Additional visualization allows us to further appreciate the heterogeneous distribution of data within the file:

There is a very brief header in which the the string "SCAN" appears, composed of 16-bit characters.

$ hexdump -C -n 100 SCAN00356-sample.dat
00000000  00 00 00 00 00 00 00 00  00 00 00 00 00 01 00 00  |................|
00000010  00 00 00 00 00 01 00 00  80 fa 6d 1c a5 8b d2 01  |..........m.....|
00000020  00 f1 d7 49 75 8f d2 01  e8 07 00 00 53 00 43 00  |...Iu.......S.C.|
00000030  41 00 4e 00 00 00 00 00  00 00 00 00 00 00 00 00  |A.N.............|
00000040  00 00 01 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000050  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*

sample .idx file SCAN00356.idx

Unlike SCAN00356-sample.dat, the structure of SCAN00356.idx is fairly uniform:

Visualization:

SCAN00356.idx also has a very small header. The ASCII string "dism" appears here afterward, prior to the bulk of the encoded data.

 $ hexdump -C -n 2208 SCAN00356.idx
00000000  fe 53 02 02 04 04 03 ff  00 01 07 04 00 08 00 00  |.S..............|
00000010  00 00 02 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000020  00 00 1f ac 54 00 00 f9  3d 03 8a f0 33 00 00 00  |....T...=...3...|
00000030  01 00 00 00 00 00 08 00  00 00 00 00 00 00 00 00  |................|
00000040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
000003f0  00 00 00 00 00 00 00 00  00 00 00 00 64 69 73 6d  |............dism|
00000400  00 1c 00 00 00 00 00 16  00 00 00 03 00 00 04 00  |................|
00000410  08 06 00 08 00 00 0d 00  02 00 16 05 00 00 00 00  |................|
00000420  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
000007f0  00 00 00 00 00 00 00 00  00 00 00 00 00 ff 7e 00  |..............~.|
00000800  02 9c 88 0a 00 00 ff f9  71 9f ce 72 2d 7e 00 00  |........q..r-~..|
00000810  00 00 a3 1e 0d 16 00 00  ff 77 18 28 86 72 2d 7e  |.........w.(.r-~|
00000820  00 00 00 00 4c d2 99 18  00 00 ff 03 71 b9 f1 72  |....L.......q..r|
00000830  2d 7e 00 00 00 00 a0 06  1e 28 00 00 ff 63 c4 ff  |-~.......(...c..|
00000840  ed 72 2d 7e 00 00 00 00  28 c3 94 35 00 00 7f c9  |.r-~....(..5....|
00000850  28 94 e2 72 2d 7e 00 00  00 00 a1 42 21 41 00 00  |(..r-~.....B!A..|
00000860  7f dd 10 f2 f1 72 2d 7e  00 00 00 00 4e 5e 24 51  |.....r-~....N^$Q|
00000870  00 00 ff 1c 82 c0 c5 72  2d 7e 00 00 00 00 95 50  |.......r-~.....P|
00000880  58 5b 00 00 7f 5c f5 c5  f1 72 2d 7e 00 00 00 00  |X[...\...r-~....|
00000890  16 34 02 69 00 00 ff 6f  79 56 e5 72 2d 7e 00 00  |.4.i...oyV.r-~..|

The images were created using binwalk and binvis.io.

Answer 2

The SCAN00356.idx file has easily identifiable structures. The data region begins starting at offset 0x800 and continues to the end of the file. Each 0x400 page contains 18-byte records. When a page fills up (or data ends), it ends the current set of records with a 1-word index value after the last record.

FYI - Just change the view width of your hex editor to 18 bytes. The structures will pop right out at you. Here is an excerpt from your file:

EF 73 05 50 01 00 FF 72 7F 46 88 72 2D 7E 00 00 00 05 
C7 14 05 50 01 00 7F E4 1C 09 8C 72 2D 7E 00 00 00 05 
A0 20 05 50 01 00 FF 55 BA CB 8F 72 2D 7E 00 00 00 05 
67 18 05 50 01 00 7F C7 57 8E 93 72 2D 7E 00 00 00 05 
44 D6 05 50 01 00 FF 38 F5 50 97 72 2D 7E 00 00 00 05 
0F 84 05 50 01 00 7F AA 92 13 9B 72 2D 7E 00 00 00 04 
CA A0 05 50 01 00 FF 1B 30 D6 9E 72 2D 7E 00 00 00 04 
A9 49 05 50 01 00 7F 8D CD 98 A2 72 2D 7E 00 00 00 04 
76 89 05 50 01 00 FF FE 6A 5B A6 72 2D 7E 00 00 00 04 
4D 6F 05 50 01 00 7F 70 08 1E AA 72 2D 7E 00 00 00 04 
2C BF 05 50 01 00 FF E1 A5 E0 AD 72 2D 7E 00 00 00 03 
FD 5B 05 50 01 00 7F 53 43 A3 B1 72 2D 7E 00 00 00 03

Some of the columns never seem to change, such as the byte string 0x72 7D 7E 00 00 00.

Others appear as flags, such as the column that alternates between 0xFF and 0x7F.

There seem to be a few different types of records stored, although they all conform to this format. Some will use more of the data bytes than others.

This has the feel of some type of logging record, though I don't know what the values of the data fields mean. I hope this helps.