Systems Life: Lost (and Found) in Log Data

This post is part of a series in which I write about experiences or specific challenges from my day-to-day work. I’m hoping that these will be interesting for other librarians that work in entirely different areas, for my colleagues who are solving different problems on different systems (or maybe eventually the same one after we migrate), and for those who are thinking about doing this kind of work in the future.

Building from navigating the distributed database, I want to get more deeply into what cross-system problem solving can look like. To re-set the stage (but for more details about these tools, check the previous post), transaction history of items is only available for most users via our Analytics tool.

Transaction Histories

Transaction history represents the ways an item’s traveled, checkouts but also transits and receipts. This is one of the many transactions created while my request for the Alien: Romulus DVD was filled. In this transaction, a coworker at York (I’ve redacted any details, but the user ID is in the actual log) sets the item to transit for reason “HOLD” to “UP-PAT”:

This is the Analytics export, which I transformed from a CSV into a table for readability in this post.

Unfortunately, even though the underlying Symphony database has unique item keys for records, Analytics seems to use the barcode as the primary key of an item table, not just the primary way to find an item record. An item’s transaction history is completely wiped from Analytics if someone changes the barcode. And sometimes, barcodes change. In our case, we change barcodes on everything that’s permanently shifted to the annex (see my post on macros). We also have barcodes wear out or fall off. So we have hundreds of thousands of items whose histories were lost, at least from the Analytics.

These lost records came to a head when our Collection Maintenance team needed to be able to track large sets of items being moved the Annex. Once the items arrived, their barcodes would be replaced with an Annex barcode, which serves a different function. So one could follow a set of barcodes on their journey until “poof,” every record related to them vanished. On the one hand, one could assume the item had been processed by the Annex since it had now disappeared. But it made tracking uneven and meant collections maintenance couldn’t tell what route an item had taken to get there or how long it’d taken.

First, I’ll note that our systems work is also quite distributed. While I was working with our collections maintenance data expert on getting access to older data, the Symphony admins were configuring item extended information to include an original barcode field, which is now populated when a barcode updates. They’ve also done some work hunting down barcode changes to update the original barcode fields. These will be exportable, even though they won’t be searchable the same way in our Analytics. Systems takes a village.

Where the Data Still Lives

Getting back to the problem-solving, this data can still be found through the oldest method of ILS data access: Workflows reports.

By running a Scan History Logs report against a set of barcodes, we can export every log in which that barcode shows up. This data wasn’t nearly as easy to use as an Analytics or Data Control export. It’s exported in a text file and uses opaque datacodes.¹ Here are two example log entries from a barcode change (the actual user’s ID has been replaced with REDACTED):

2/4/2025,10:23:04 Station: 0265 Request: Sequence #: 59 Command: Edit Item Part B
$<datacode_FF>:REDACTED  $<datacode_FE>:UP-ANNEX  $<datacode_Fc>:NONE  $<datacode_FW>:REDACTED  $<datacode_NQ>:000009387393  $<datacode_IQ>:DD3.M825M66 1976  $<datacode_NX>:A2  $<datacode_NY>:2046  $<datacode_0A>:0902  $<datacode_0B>:015  $<datacode_IN>:CATO-PARK  $<datacode_NR>:20460902015  0y:Y  $<datacode_Fv>:3000000  
 
2/4/2025,10:23:04 Station: 0265 Request: Sequence #: 71 Command: Edit Item Part B
$<datacode_FF>:REDACTED  $<datacode_FE>:UP-ANNEX  $<datacode_Fc>:NONE  $<datacode_FW>:REDACTED  $<datacode_NQ>:20460902015  $<datacode_IQ>:DD3.M825M66 1976  $<datacode_Io>:USERID  $<datacode_Fv>:3000000

That top entry is really important because, even though there are other ways of accessing a permanent item ID, it’s not in the logs. So by scanning for that original barcode, we can get the entry where the barcode is in NQ and the new barcode is in NR.

I wrote a Python script that processed entire log entries, since the colleague from Collection Maintenance wasn’t just looking for old/new barcodes but for the transaction histories of that item. He could apply a date range to the log export itself, so he could set it just to export the last few months. I’m not going to share the entire script here, but this is the overall approach that I used:

current = re.search(r'\<datacode_NQ\>:(.+?) ',line)

I wrote a conditional function for all the entries which might not be present. For the handful whose data might contain a space, I wrote the search to break at the $<datacode that begins the next entry and trimmed space off the right side.

The ouptut is a very large JSON object, which I’ve condensed below to reflect the key fields from this transaction. Even with its size, it’s a lot more compact and efficient than the Analytics output shared above and thus might be easier to process, so this script may end up being useful in other contexts.

    {
        "date": "2/4/2025",
        "time": "10:23:04",
        "sequence": "59",
        "command": "Edit Item Part B",
        "station_user_login": "REDACTED",
        "station_library": "UP-ANNEX",
        "station_user_ID": "REDACTED",
        "current_barcode": "000009387393",
        "new_barcode": "20460902015",
        "call": "DD3.M825M66 1976"
    },

Going forward (to migration) this new process should meet the use cases of:

1. connecting old and new barcodes in collection maintenance logs,
2. tracking item histories that had been dropped from Analytics, and
3. created a friendly JSON object vs. the same entry spread across a dozen lines or more of a CSV, making it of potential use for reporting on new barcodes as well.

So in sum:

1. Sysadmins created a new field for original barcodes and set it to populate when barcodes are changed.
2. Sysadmins began hunting through logs to find barcode changes and wrote a script to populate them in the database for export/reference.
3. I created a way to extract JSON objects for item transaction history out of log reports run on old barcodes since those transactions were no longer accessible in Analytics.