Authority Reports

From Koha Wiki
Jump to navigation Jump to search

Reports about anything to do with authority records

The following SQL statements have been written by Koha users world-wide. Keep in mind that not all reports have been tested and should be read thoroughly before applying them to your own system.

Return to the SQL Reports Library

Authors not in the Authorities

  • Developer: MJ Ray, software.coop
  • Module: Catalog
  • Purpose: List of author names found on biblio records but not authority records
  • Status: Production
  • Version: Will not work after upgrade to 17.05 or later
SELECT DISTINCT(author) AS heading
FROM biblio
WHERE author NOT IN
   (SELECT ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code="a"]')
      AS heading
    FROM auth_header
    WHERE authtypecode='PERSO_NAME')
ORDER BY heading

Authors not in the Authorities, with Biblio numbers

  • Developer: E. Bradtke
  • Module: Catalog
  • Purpose: List of author, corporate author and meeting names that are not linked to authority records. Lists Biblio numbers next to names.
  • Status: Completed
SELECT DISTINCT biblionumber, heading FROM (
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="9"]')) = 0
UNION
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="9"]')) = 0
UNION
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="9"]')) = 0
UNION
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="9"]')) = 0
) AS heads
ORDER BY heading

Classes of MARC fields missing authorities

  • Developer: Barton Chittenden
  • Module: Catalog
  • Purpose: Bibs with personal name missing authority link
  • Status: Completed

This report is easily extended to

  • Bibs with corporate name missing authority link ( s/00/10/g )
  • Bibs with meeting name missing authority link ( s/00/11/g )

And with only slightly more tweaking,

  • Bibs with uniform title missing authority link ( tags 130, 630, 730 or 830 ),
  • Bibs with "Series Statement/Added Entry-Title" missing authority link (tag 440 )
  • Bibs with Subject or Genre missing authority link ( tags 650, 651, or 655 )
SELECT
    CONCAT(
        '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',
        biblionumber,
        '\">',
        biblionumber,
        '</a>'
    ) AS biblionumber,
    CONCAT_WS( '|',
        ExtractValue( marcxml, '//datafield[@tag=100]/subfield[@code="a"]' ),
        ExtractValue( marcxml, '//datafield[@tag=400]/subfield[@code="a"]' ),
        ExtractValue( marcxml, '//datafield[@tag=600]/subfield[@code="a"]' ),
        ExtractValue( marcxml, '//datafield[@tag=800]/subfield[@code="a"]' )
    ) AS 'personal name'
FROM
    biblioitems
WHERE
    ( length(ExtractValue(marcxml, '//datafield[@tag="100"]/subfield[@code="a"]')) != 0
      AND length(ExtractValue(marcxml, '//datafield[@tag="100"]/subfield[@code="9"]')) = 0 )
    OR ( length(ExtractValue(marcxml, '//datafield[@tag="400"]/subfield[@code="a"]')) != 0
      AND length(ExtractValue(marcxml, '//datafield[@tag="400"]/subfield[@code="9"]')) = 0 )
    OR ( length(ExtractValue(marcxml, '//datafield[@tag="600"]/subfield[@code="a"]')) != 0
      AND length(ExtractValue(marcxml, '//datafield[@tag="600"]/subfield[@code="9"]')) = 0 )
    OR ( length(ExtractValue(marcxml, '//datafield[@tag="800"]/subfield[@code="a"]')) != 0
      AND length(ExtractValue(marcxml, '//datafield[@tag="800"]/subfield[@code="9"]')) = 0 )

Searching 667 notes for particular authorities

  • Developer: By Mark Miller, ByWater Solutions for support ticket (contributed to Wiki by Heather Hernandez)
  • Module: Catalog
  • Purpose: List of hyperlinked authority record number, 1XX field (all subfields), and text in 667 note, retrieved by searching for text string in 667
  • Status: Completed
SELECT CONCAT('<a href=\"/cgi-bin/koha/authorities/detail.pl?authid=',a.authid,'\">',a.authid,'</a>') AS auth_record,
	ExtractValue(a.marcxml, '//datafield[contains(concat(" ",@tag)," 1")]/*') AS '1XX',
    ExtractValue(a.marcxml, '//datafield[@tag="667"]/*') AS '667'
FROM auth_header a
WHERE ExtractValue(a.marcxml, '//datafield[@tag="667"]/*') LIKE CONCAT('%',<<Enter search term>>,'%')

Terms not in the Authorities

  • Developer: Adapted from a report by Bernardo Gonzalez Kriegel
  • Module: Catalog
  • Purpose: List of terms found on biblio records in 6XX fields that are not in Authorities, with associated biblio numbers.
  • Status: Completed
SELECT biblionumber, ExtractValue(metadata,'//datafield[@tag="650"]/subfield[@code="a"]') AS heading
FROM biblio_metadata
WHERE length(ExtractValue(metadata, '//datafield[@tag="650"]/subfield[@code="a"]')) != 0 
      AND length(ExtractValue(metadata, '//datafield[@tag="650"]/subfield[@code="9"]')) = 0
ORDER BY heading

Duplicate authorities

  • Developer: Sarah Cornell
  • Module: Catalog
  • Purpose: Finds multiple occurrences of main headings. Useful for manual cleanup after migrating from a system that stores separate name and topic authority files.
  • Status: Completed
  • Works with: 17.11
SELECT 
  GROUP_CONCAT(authid SEPARATOR ', ') AS "AuthIDs",
  CONCAT('<a href=\"/cgi-bin/koha/authorities/merge.pl?authid=',MAX(authid),'&authid=',MIN(authid),'\" target="_blank">Merge</a>') AS "Merge",
  CONCAT(
    ExtractValue(`marcxml`,'//datafield[@tag="100"]/*'), -- PERSO_NAME
    ExtractValue(`marcxml`,'//datafield[@tag="110"]/*'), -- CORPO_NAME
    ExtractValue(`marcxml`,'//datafield[@tag="111"]/*'), -- MEETI_NAME
    ExtractValue(`marcxml`,'//datafield[@tag="130"]/*'), -- UNIF_TITLE
    ExtractValue(`marcxml`,'//datafield[@tag="148"]/*'), -- CHRON_TERM
    ExtractValue(`marcxml`,'//datafield[@tag="150"]/*'), -- TOPIC_TERM
    ExtractValue(`marcxml`,'//datafield[@tag="151"]/*'), -- GEOGR_NAME
    ExtractValue(`marcxml`,'//datafield[@tag="155"]/*')  -- GENRE/FORM
    ) AS "Heading",
  GROUP_CONCAT(Extractvalue(marcxml,'//datafield[@tag="035"]/subfield[@code="a"]') SEPARATOR ', ') AS "ControlNumbers",
  authid, 
  datecreated, 
  modification_time, 
  authtrees, 
  marc
FROM auth_header
GROUP BY heading
HAVING count(authid) > 1
ORDER BY heading

Authorities records added in time frame, with headings

  • Developer: Stefano Bargioni 2017-03-09, Pontificia Università della Santa Croce
  • Module: Statistical (Catalog)
  • Purpose: This report will show the authorities added to Koha in a time period.
  • Status: Complete
SELECT authid, datecreated, authtypecode, concat(
ExtractValue(`marcxml`,'//datafield[@tag="100"]/*'), -- PERSO_NAME
ExtractValue(`marcxml`,'//datafield[@tag="110"]/*'), -- CORPO_NAME
ExtractValue(`marcxml`,'//datafield[@tag="111"]/*'), -- MEETI_NAME
ExtractValue(`marcxml`,'//datafield[@tag="130"]/*'), -- UNIF_TITLE
ExtractValue(`marcxml`,'//datafield[@tag="148"]/*'), -- CHRON_TERM
ExtractValue(`marcxml`,'//datafield[@tag="150"]/*'), -- TOPIC_TERM
ExtractValue(`marcxml`,'//datafield[@tag="151"]/*'), -- GEOGR_NAME
ExtractValue(`marcxml`,'//datafield[@tag="155"]/*')  -- GENRE/FORM
) main_heading
FROM `auth_header`
WHERE datecreated BETWEEN <<(from yyyy-mm-dd)>> AND <<(to yyyy-mm-dd)>>
ORDER BY datecreated, authtypecode, main_heading

Authorities records added/deleted in time frame

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Statistical (Catalog)
  • Purpose: This report will show the authorities added/deleted at a branch in a time period.
  • Status: Complete
  • IMPORTANT: Only works if you're logging authority actions (AuthoritiesLog system preference activated).
SELECT l.action, count(l.timestamp) AS 'authorities'
FROM action_logs l
LEFT JOIN borrowers p on (p.borrowernumber=l.user)
WHERE module='AUTHORITIES' AND p.branchcode=<<Branch|branches>>
      AND date(l.timestamp) between <<Between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>
GROUP BY l.action
Retrieved from "https://wiki.koha-community.org/w/index.php?title=Authority_Reports&oldid=38506"