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 <[email protected]> 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 < > [email protected]> 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 [email protected] <[email protected]>* >> *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 >> [email protected] >> 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 | [email protected] > > <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. >
_______________________________________________ Evergreen-dev mailing list [email protected] http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-dev
