Monday Minutes: Reporting on the Deleted

by Kelly McElligott on Mar 8, 2021

Nick, a developer with ByWater Solutions, joins Kelly and Jessie to share some handy reports on how a library can report on deleted items, bibs, and patrons from their Koha system.

Helpful SQL for Deleted Patrons

SELECT surname, firstname, cardnumber, branchcode, categorycode

FROM deletedborrowers

WHERE date(updated_on) BETWEEN <<Start date|date>> AND <<End date|date>>

Helpful SQL for Deleted Items

SELECT IF(deletedbiblio.biblionumber is NULL, 'active', 'deleted')as 'present' ,COALESCE(deletedbiblio.title,biblio.title) as 'Title', COALESCE(deletedbiblio.author,biblio.author) as 'Author', itemcallnumber, location, barcode

FROM deleteditems

LEFT JOIN deletedbiblio USING (biblionumber)

LEFT JOIN biblio USING (biblionumber)

WHERE deleteditems.timestamp BETWEEN <<Start date|date>> AND <<End date|date>>

Helpful SQL for Deleted Bibs

SELECT title, author

FROM deletedbiblio

WHERE timestamp BETWEEN <<Start date|date>> AND <<End date|date>>

Clean Up Database Cron

There is a clean-up database cron that we can run on your site, if you would like to limit how long this data is stored.

The portion of the cleanup database cron for deleting information about Items and Bibs is called, 'deleted-catalog', and this can be told to purge records deleted more than X days ago. This will purge information from the following tables: deleteditems, deletedbiblioitems, deletedbiblio_metadata, and deletedbiblio.

The portion of the cleanup cron for deleting patron deletions is called, 'deleted-patrons', which will also go by the number of days from when the patron was deleted.