[Evergreen-dev] Pulling audit_user for a change from auditor table

Tue Sep 21 11:28:26 EDT 2021

Thanks Chris, this is just to give an example in the release notes for how
someone might try and set some historical data to close to the truth based
on the auditor info.  More of a one time manual process, not something that
would be included in Evergreen going forward.  I understand that some sites
have auditor tables turned off, or some sites prune it regularly.

I want to write a query that asks "what was the audit_user and audit_time
when au.card first shows up".  The fact that I have to look at the previous
row's data to get that is what I find confusing.  I'm looking at your
auditor_actor_usr_history.sh script, and I don't think I see any attempt to
shift the audit_* data to the next row.  So I'm guessing the user just
needs to know that if they want to see what was changed by a specific user
at a specific time, they look at the data in the row after?

My plan locally is to set the initial actor.card(create_date) to the patron
account creation date, then see if I can grab extra data for all the
patrons that have had multiple barcodes assigned over the years, at least
for the most recently added barcodes.

This is what I've come up with so far, but I haven't used window functions
much, so I don't fully understand what I'm doing.

This seems to be telling me the previous audit time and audit user when the
card changes.  My test account I'm looking at has had 20 barcodes,
including the one that was migrated in.  And this query gives me 19 rows,
which I think correspond to when each barcode was added other than the
first that was migrated in.  But I haven't double checked the data yet.

SELECT *
FROM
(
SELECT audit_id,audit_time, audit_user,id,card,

lag(card) over w AS previous_card,  -- previous rows entry or null if there
is no previous row
lag(audit_time) over w AS previous_audit_time,
lag(audit_user) over w AS previous_audit_user

FROM auditor.actor_usr_lifecycle aaul

WINDOW w AS (PARTITION BY aaul.id ORDER BY audit_time,audit_id)

ORDER BY id,audit_time,audit_id

) AS AT

WHERE AT.card!=at.previous_card
AND AT.id=133033  -- Test user to look at.
;

This runs pretty quickly on our system, but we only have 432000 rows in our
auditor.actor_usr_history, so it is a relatively small dataset.

Josh

On Tue, Sep 21, 2021 at 8:50 AM Chris Sharp <csharp at georgialibraries.org>
wrote:

> Josh,
>
> I would avoid using the auditor tables for this if possible.  I've always
> seen those as an internal means of accountability.  They aren't exposed by
> the fieldmapper and so aren't available to end users at this point at all,
> which I think is mostly a good thing.
>
> However, answering your question at face value, as I understand it, since
> the auditor tables record the ID (or other primary key) of the source
> table, I usually search by (say) biblio.record_entry.id, then sort by
> audit_id or audit_date.  We have some scripts we use for PINES staff to
> search the audit tables here for reference:
>
>
> https://git.evergreen-ils.org/?p=contrib/pines.git;a=tree;f=pines-finder;h=408f6d1ccfba2188e2c2446b911f56150c5fc6b9;hb=HEAD
>
> Hope that's helpful!
>
> Chris
>
> On Tue, Sep 21, 2021 at 12:06 AM Josh Stompro via Evergreen-dev <
> evergreen-dev at list.evergreen-ils.org> wrote:
>
>> Hello, I'm stuck on how to grab the user that made a change from an
>> auditor table?  How do I look for the first occurrence of a new value, and
>> then grab the previous rows audit_user and audit_date?
>>
>> The way that I understand it, the auditor tables store the user that made
>> a change, and then the old values that were in the audited table before the
>> change.  So the audit_user and audit_date are not for the data in the same
>> row, but are for the next newest row.
>>
>> Do I need a window function so I can look at previous rows?  Or is there
>> a much simpler way to do it?  Maybe a window with lag()/lead() to shift the
>> audit_user and audit_date to the next row?
>>
>> I'm working on seeing if I can pull out barcode creation info, to fill in
>> actor.card creator and create_date for LP#1705332, which adds those fields
>> into actor.card.
>>
>> Thanks for any hints.
>> Josh
>> [image: Company logo]
>> *Josh Stompro*
>>
>> *IT Director stomproj at gsuite.larl.org <stomproj at gsuite.larl.org>*
>> *218-233-3757 ext. 139* | *Mobile: 218-790-2110*
>>
>> *Lake Agassiz Regional Library | **www.larl.org <http://www.larl.org>*
>> 118 5th ST S
>> Moorhead MN 56560
>> *Celebrating 60 Years of Service in 2021!*
>> _______________________________________________
>> Evergreen-dev mailing list
>> Evergreen-dev at list.evergreen-ils.org
>> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-dev
>>
>
>
> --
>
> Chris Sharp, PINES System Administrator
> ------------------------------
>
> Georgia Public Library Service
>
> 2872 Woodcock Blvd, Suite 250 | Atlanta, GA 30341
>
> (404) 235-7147 | csharp at georgialibraries.org
>
> <https://www.facebook.com/georgialibraries>
> <https://www.twitter.com/georgialibs>
> <https://www.instagram.com/georgialibraries/>
>
> Join our email list <http://georgialibraries.org/subscription> for
> stories of Georgia libraries making an impact in our communities.
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-dev/attachments/20210921/05be3505/attachment.html>