Base64 in export of Store&Forward quarantined item

Hi there:
In my gateway there is a quarantined item under Store&Forward. When I try to export it to see the data and try to correct the query (as per Inductive University / Databases In Ignition / About Store And Forward / Controlling Quarantine Data) the result is an xml file with the following content:

<?xml version="1.0"?>
<cachedata sourceStore="Produccion" creationTime="Tue Oct 10 16:24:53 ART 2017">
   <data flavor="__datasourcedata__" subtype="">
      <base64>H4sIAAAAAAAAAG2Sz2sTQRTHJ6kpNorUevIgLOjBgmyvQhEJboJbN5uYbVCpsExmh3TC7k46P9L0
IvSihx7sQc968Gb/BvEPEP8CQSlePXv1zWw2CdK5vNn3vu8zb2a/Z39QTQr0kvDMZXmiiWITirXi
GVaM5y4b5sxuhljRQ3zkSiLYWLkMp3ggXb8RQPCwwpJrQWhL58TI5Z2nmoqjqGVKqFiVKloJUD2Z
ixXaCEZ4grdSnA+3IiVYPtwOUO3AtB6gV6iyh1YnONVUKnRjb0nbGYwoUdvTsUL1ruAwNoFDVeVH
fQ1Wv+s1dpt2uyYSktld1NxdZNwWJfs4fgSD6ozHfs6g33ngPLy3pOlLjQXjper/csAVXaqF/SCw
1Vav0y5kPQp3xB4FkRI8bcMLCng3Kp1G5MwH88Ow2XN2On643CXnbTOx0wmduxBd34PT7AS+Fxfi
zQtBHZHQnAJo8UKGxZOxZUE0BKuKGt0COs9cjFxcIW5jIriM+wNGcInOMm3REA2oVC/NW6YK/LPH
zd7sPxXTmDd2GqFX5AqMydlvLUoTuMYE7swEp9+ff1iXm2kVoekYbHYJ7HzNqrRiqQsGpPuj48qT
F+rmipEcXgYrxuj889epQqujlGZczlssOODgw5Pfp9/e3v5ZRZUdVLMmnAq0vhCFOhtQ8frs/a0r
736dlIcbm38B2vWF0M8VHVKxcf7x09/jN/cB6JdA4/GrpuUfdi0XXoYDAAA=</base64>
   </data>
</cachedata>

Is there a way to decode that base64 text and see the actual data behind it?
Thanks.

Use base64 --decode on the command line? It’s part of coreutils, so should be on just about every linux box on the planet. If you’re not using Linux… somebody else might pipe up. /-:

base64 --help
Usage: base64 [OPTION]... [FILE]
Base64 encode or decode FILE, or standard input, to standard output.

With no FILE, or when FILE is -, read standard input.

Mandatory arguments to long options are mandatory for short options too.
  -d, --decode          decode data
  -i, --ignore-garbage  when decoding, ignore non-alphabet characters
  -w, --wrap=COLS       wrap encoded lines after COLS character (default 76).
                          Use 0 to disable line wrapping

      --help     display this help and exit
      --version  output version information and exit

The data are encoded as described for the base64 alphabet in RFC 4648.
When decoding, the input may contain newlines in addition to the bytes of
the formal base64 alphabet.  Use --ignore-garbage to attempt to recover
from any other non-alphabet bytes in the encoded stream.

GNU coreutils online help: <http://www.gnu.org/software/coreutils/>
Full documentation at: <http://www.gnu.org/software/coreutils/base64>
or available locally via: info '(coreutils) base64 invocation'

Window version can be found here. Look for base64.zip

Unfortunately, if you’re already getting base64 data in the quarantine export you’re not going to be able to turn this into human readable data. Most data types should be converted before going into export in 7.9.4, but it looks like this might be a generic INSERT/UPDATE that we’re not sure how to deserialize?

Is there a way to get some useful information from that data? Or there is nothing to do at this point?

This is what that decodes to:

00000000  1f 8b 08 00 00 00 00 00  00 00 6d 92 cf 6b 13 41  |..........m..k.A|
00000010  14 c7 27 a9 29 36 8a d4  7a f2 20 2c e8 c1 82 6c  |..'.)6..z. ,...l|
00000020  af 42 11 09 6e 82 5b 37  9b 98 6d 50 a9 b0 4c 66  |.B..n.[7..mP..Lf|
00000030  87 74 c2 ee 4e 3a 3f d2  f4 22 f4 a2 87 1e ec 41  |.t..N:?..".....A|
00000040  cf 7a f0 66 ff 06 f1 0f  10 ff 02 41 29 5e 3d 7b  |.z.f.......A)^={|
00000050  f5 cd 6c 36 09 d2 b9 bc  d9 f7 be ef 33 6f 66 bf  |..l6........3of.|
00000060  67 7f 50 4d 0a f4 92 f0  cc 65 79 a2 89 62 13 8a  |g.PM.....ey..b..|
00000070  b5 e2 19 56 8c e7 2e 1b  e6 cc 6e 86 58 d1 43 7c  |...V......n.X.C||
00000080  e4 4a 22 d8 58 b9 0c a7  78 20 5d bf 11 40 f0 b0  |.J".X...x ]..@..|
00000090  c2 92 6b 41 68 4b e7 c4  c8 e5 9d a7 9a 8a a3 a8  |..kAhK..........|
000000a0  65 4a a8 58 95 2a 5a 09  50 3d 99 8b 15 da 08 46  |eJ.X.*Z.P=.....F|
000000b0  78 82 b7 52 9c 0f b7 22  25 58 3e dc 0e 50 ed c0  |x..R..."%X>..P..|
000000c0  b4 1e a0 57 a8 b2 87 56  27 38 d5 54 2a 74 63 6f  |...W...V'8.T*tco|
000000d0  49 db 19 8c 28 51 db d3  b1 42 f5 ae e0 30 36 81  |I...(Q...B...06.|
000000e0  43 55 e5 47 7d 0d 56 bf  eb 35 76 9b 76 bb 26 12  |CU.G}.V..5v.v.&.|
000000f0  92 d9 5d d4 dc 5d 64 dc  16 25 fb 38 7e 04 83 ea  |..]..]d..%.8~...|
00000100  8c c7 7e ce a0 df 79 e0  3c bc b7 a4 e9 4b 8d 05  |..~...y.<....K..|
00000110  e3 a5 ea ff 72 c0 15 5d  aa 85 fd 20 b0 d5 56 af  |....r..]... ..V.|
00000120  d3 2e 64 3d 0a 77 c4 1e  05 91 12 3c 6d c3 0b 0a  |..d=.w.....<m...|
00000130  78 37 2a 9d 46 e4 cc 07  f3 c3 b0 d9 73 76 3a 7e  |x7*.F.......sv:~|
00000140  b8 dc 25 e7 6d 33 b1 d3  09 9d bb 10 5d df 83 d3  |..%.m3......]...|
00000150  ec 04 be 17 17 e2 cd 0b  41 1d 91 d0 9c 02 68 f1  |........A.....h.|
00000160  42 86 c5 93 b1 65 41 34  04 ab 8a 1a dd 02 3a cf  |B....eA4......:.|
00000170  5c 8c 5c 5c 21 6e 63 22  b8 8c fb 03 46 70 89 ce  |\.\\!nc"....Fp..|
00000180  32 6d d1 10 0d a8 54 2f  cd 5b a6 0a fc b3 c7 cd  |2m....T/.[......|
00000190  de ec 3f 15 d3 98 37 76  1a a1 57 e4 0a 8c c9 d9  |..?...7v..W.....|
000001a0  6f 2d 4a 13 b8 c6 04 ee  cc 04 a7 df 9f 7f 58 97  |o-J...........X.|
000001b0  9b 69 15 a1 e9 18 6c 76  09 ec 7c cd aa b4 62 a9  |.i....lv..|...b.|
000001c0  0b 06 a4 fb a3 e3 ca 93  17 ea e6 8a 91 1c 5e 06  |..............^.|
000001d0  2b c6 e8 fc f3 d7 a9 42  ab a3 94 66 5c ce 5b 2c  |+......B...f\.[,|
000001e0  38 e0 e0 c3 93 df a7 df  de de fe 59 45 95 1d 54  |8..........YE..T|
000001f0  b3 26 9c 0a b4 be 10 85  3a 1b 50 f1 fa ec fd ad  |.&......:.P.....|
00000200  2b ef 7e 9d 94 87 1b 9b  7f 01 da f5 85 d0 cf 15  |+.~.............|
00000210  1d 52 b1 71 fe f1 d3 df  e3 37 f7 01 e8 97 40 e3  |.R.q.....7....@.|
00000220  f1 ab a6 e5 1f 76 2d 17  5e 86 03 00 00           |.....v-.^....|
0000022d

Good luck.

You can create a new DB connection, copy over the HSQL db files (in the /datacache/<database name> directory in the installation folder), then retry the records and see what it tries to push into the database.
That’s the only way to force these records to deserialize and unpack. Even the data stored in the HSQL db is an (encrypted) BLOB, so it’s not human readable.

1 Like

Thanks a lot guys, it's been enlightening.
Just out of curiosity, any idea why some data gets saved as a BLOB and some other data gets saved in human readable form when exporting?

Is there any way to see where these queries are coming from or going to (table in addition to database)? I have a hard time troubleshooting these in a large system.

No. The BLOB itself represents a Java object of some kind - we have to manually register them for deserialization in the export mechanism. If you get in contact with support, they may be able to help - or, at the very least, add some urgency to the ticket to make all first-party quarantine data types export-able in a friendly format.

Bumping this topic from grave since it's my exact issue.

I have some quarantines stored in SF system coming from a single named query and failing to see what values are causing the insert to fail. I understand its a FK mismatch but not the specific values/where they are coming from.

Here's my Named Query:

Where:
OpID is a UUID string (example '41844571-CBCD-4FDE-B632-BA4857DA100C'). NOTE: the DB stores this as type 'uniqueidentifier' and maybe this is part of the issue.
Code is a positive integer (example 2201)
Timestamp (i.e. 2023-08-21 9:53:14 AM)

The database is MSSQL. There's a primary key in the table 'id' that indexes every entry, too.
image

Is there anything sticking out that causes the SF quarantine to be base64 intstead of non readable?

Thank you!!

You can try opening the SF cache directly with Kindling:

Which might give you more useful insight into what's gone wrong.

This export giving me .xml file:

And doesn't appear to be supported:
image

Is there another way to get the S&F file format?

You need to take the entire datacache/ folder (or a .zip of it) and open that in Kindling, not the export from the web UI; that web export has already lost too much information to be useful. In the Ignition install directory, under data/datacache/, there will be a folder with the same name as your database connection - either copy that whole directory somewhere and point Kindling at it (or the .script file within) or .zip it all up and open that with Kindling.

1 Like

OK this is fantastic. Full query with values and everything.

Thank you!!!

1 Like

For anyone in the future, another way that may work to find the failed SF query is to search logs for Store & Forward. This has the SQL Exception included. Not sure if it works every time but may be easier than DLing Kindling, pulling the DB script file, etc.

Earl

1 Like