<div dir="ltr">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.<div><br></div><div>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?</div><div><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div>SELECT * <br>FROM<br>(<br>SELECT audit_id,audit_time, audit_user,id,card,<br><br>lag(card) over w AS previous_card, -- previous rows entry or null if there is no previous row<br>lag(audit_time) over w AS previous_audit_time,<br>lag(audit_user) over w AS previous_audit_user<br><br>FROM auditor.actor_usr_lifecycle aaul<br><br>WINDOW w AS (PARTITION BY <a href="http://aaul.id">aaul.id</a> ORDER BY audit_time,audit_id)<br><br>ORDER BY id,audit_time,audit_id<br><br>) AS AT<br><br>WHERE AT.card!=at.previous_card<br>AND AT.id=133033 -- Test user to look at.<br>;<br><div><br></div><div>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.</div><div><br></div><div>Josh<br><div><br></div><div><br></div></div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Sep 21, 2021 at 8:50 AM Chris Sharp <<a href="mailto:csharp@georgialibraries.org">csharp@georgialibraries.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>Josh,</div><div><br></div><div>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.</div><div><br></div><div>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) <a href="http://biblio.record_entry.id" target="_blank">biblio.record_entry.id</a>, 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:</div><div><br></div><div><a href="https://git.evergreen-ils.org/?p=contrib/pines.git;a=tree;f=pines-finder;h=408f6d1ccfba2188e2c2446b911f56150c5fc6b9;hb=HEAD" target="_blank">https://git.evergreen-ils.org/?p=contrib/pines.git;a=tree;f=pines-finder;h=408f6d1ccfba2188e2c2446b911f56150c5fc6b9;hb=HEAD</a></div><div><br></div><div>Hope that's helpful!</div><div><br></div><div>Chris<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Sep 21, 2021 at 12:06 AM Josh Stompro via Evergreen-dev <<a href="mailto:evergreen-dev@list.evergreen-ils.org" target="_blank">evergreen-dev@list.evergreen-ils.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">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? <div><br></div><div>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.<div><br></div><div>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?</div><div><br></div><div>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.</div><div><br></div><div>Thanks for any hints.</div><div>Josh</div><div><div><div><div dir="ltr"><table style="padding:0px;margin:10px 0px;border:medium none"><tbody><tr><td style="vertical-align:middle;padding:0px 7px 0px 0px"><img alt="Company logo" src="https://storage.googleapis.com/signaturesatori/customer-C039u5c5y/images/GJnfy.png" width="100" height="100"></td><td style="border-left:3px solid rgb(232,232,232);padding:7px 0px 0px 10px">
<div style="font-family:arial,helvetica,sans-serif;font-size:14px;line-height:17px;font-weight:bold;color:rgb(238,142,22)"><span style="font-size:16px"><strong>Josh Stompro</strong></span></div>
<div style="font-family:arial,helvetica,sans-serif;font-size:12px;line-height:14px;font-weight:normal;color:rgb(0,0,0);margin-bottom:10px"><span style="font-size:13px"><strong><em>IT Director</em><br>
<a href="mailto:stomproj@gsuite.larl.org" style="text-decoration:none;color:rgb(27,70,153)" target="_blank">stomproj@gsuite.larl.org</a></strong><br>
<strong>218-233-3757 ext. 139</strong> </span><span style="font-size:13px"><span style="color:rgb(1,186,239)">| </span><strong>Mobile: 218-790-2110</strong></span><br>
<br>
<span style="font-size:13px"><strong><span style="color:rgb(27,70,152)">Lake Agassiz Regional Library | </span></strong></span><span style="font-size:13px"><strong><a href="http://www.larl.org" style="text-decoration:none;color:rgb(27,70,153)" target="_blank">www.larl.org</a></strong><br>
118 5th ST S<br>Moorhead MN 56560<br>
<span style="color:rgb(1,186,239)"><em><strong>Celebrating 60 Years of Service in 2021!</strong></em></span></span></div>
</td></tr></tbody></table>
</div></div></div></div></div></div>
_______________________________________________<br>
Evergreen-dev mailing list<br>
<a href="mailto:Evergreen-dev@list.evergreen-ils.org" target="_blank">Evergreen-dev@list.evergreen-ils.org</a><br>
<a href="http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-dev" rel="noreferrer" target="_blank">http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-dev</a><br>
</blockquote></div><br clear="all"><br>-- <br><div dir="ltr"><div dir="ltr"><br><div dir="ltr" style="margin-left:30.75pt" align="left"><table style="border:medium none;border-collapse:collapse"><colgroup><col width="76"><col width="7"><col width="64"><col width="437"></colgroup><tbody><tr style="height:73.5pt"><td colspan="2" style="vertical-align:top;overflow:hidden"><p dir="ltr" style="line-height:1.2;text-align:right;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:Arial;color:rgb(0,0,0);background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap"><span style="border:medium none;display:inline-block;overflow:hidden;width:75px;height:75px"><img src="https://lh6.googleusercontent.com/S38ai2oZSovyYAaJYdM8xFm5G7GF0FzzSqGxV_oQIIIeqZ_ZnPACWIX__Cu6oWDwH6DmF0eHmaBjNZaVvAHzuCRaWC9QqlMYs7fIMY8QH2bqjH3p5ncG3xXhsmf4ZF-6uLU3U0n9" style="margin-left: 0px; margin-top: 0px;" width="75" height="75"></span></span></p></td><td colspan="2" style="vertical-align:top;padding:5pt;overflow:hidden"><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:Verdana;color:rgb(30,67,155);background-color:transparent;font-weight:700;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">Chris Sharp, PINES System Administrator</span></p><hr><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:10pt;font-family:Verdana;color:rgb(0,0,0);background-color:transparent;font-weight:700;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap"></span><span style="font-size:9pt;font-family:Verdana;color:rgb(30,67,155);background-color:transparent;font-weight:700;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">Georgia Public Library Service</span></p><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:9pt;font-family:Verdana;color:rgb(30,67,155);background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">2872 Woodcock Blvd, Suite 250 | Atlanta, GA 30341</span></p><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:9pt;font-family:Verdana;color:rgb(30,67,155);background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">(404) 235-7147 | <a href="mailto:csharp@georgialibraries.org" target="_blank">csharp@georgialibraries.org</a></span></p></td></tr><tr style="height:36pt"><td colspan="2" style="vertical-align:top;overflow:hidden"><br></td><td colspan="2" style="vertical-align:top;padding:5pt;overflow:hidden"><p dir="ltr" style="line-height:1.2;margin-top:0pt;margin-bottom:0pt"><a href="https://www.facebook.com/georgialibraries" style="text-decoration:none" target="_blank"><span style="font-size:9pt;font-family:Verdana;color:rgb(17,85,204);background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:underline;vertical-align:baseline;white-space:pre-wrap"><span style="border:medium none;display:inline-block;overflow:hidden;width:36px;height:36px"><img src="https://lh5.googleusercontent.com/WNQyQQJjmhPJpRpTK7SJ8eLnZXzJAMQviVTcKq0t11y1PYZaiB0YhRduR0pWd5cw3HR-3oOeLwY0q6zX5-pK2EdCoy4IYEsQX11OhZBUAgFcjyCZu2A_K5KO2Q4Px_11Gr4EXHce" style="margin-left: 0px; margin-top: 0px;" width="36" height="36"></span></span></a><a href="https://www.twitter.com/georgialibs" style="text-decoration:none" target="_blank"><span style="font-size:10pt;font-family:Verdana;color:rgb(17,85,204);background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:underline;vertical-align:baseline;white-space:pre-wrap"><span style="border:medium none;display:inline-block;overflow:hidden;width:35px;height:35px"><img src="https://lh4.googleusercontent.com/6BLlJMr1Qul2DCX565XWIvFTsI40oCQn7va4xOKTWmy0HS8Uu9Is-z-dtplQisBw1eNAuwbZoMbbta00EUlM4CJ3zBCCJmzY0gax4S3uS9XDX0yJWXRKzNn-2OeBkRGflcecqsxt" style="margin-left: 0px; margin-top: 0px;" width="35" height="35"></span></span></a><a href="https://www.instagram.com/georgialibraries/" style="text-decoration:none" target="_blank"><span style="font-size:11pt;font-family:Arial;color:rgb(17,85,204);background-color:transparent;font-weight:400;font-style:normal;font-variant:normal;text-decoration:underline;vertical-align:baseline;white-space:pre-wrap"><span style="border:medium none;display:inline-block;overflow:hidden;width:37px;height:37px"><img src="https://lh5.googleusercontent.com/TZoE0LwrJWYl_9D9azRJNsBCTGH2H6DHr5kTcFMzB74deV9Bkn2I48ZSOK3Rc2Ba4jTYuZuFpyQaNPNesfVGcI6SHO-wgqY6Tg6CnkZBDMph4FiXksl772FrYFMVRzOJi3t7xGeP" style="margin-left: 0px; margin-top: 0px;" width="37" height="37"></span></span></a></p></td></tr><tr style="height:36pt"><td colspan="2" style="vertical-align:top;overflow:hidden"><br></td><td colspan="2" style="vertical-align:top;padding:5pt;overflow:hidden"><p dir="ltr" style="line-height:1.2;margin-top:0pt;margin-bottom:0pt"><a href="http://georgialibraries.org/subscription" style="text-decoration:none" target="_blank"><span style="font-size:10pt;font-family:Verdana;color:rgb(17,85,204);background-color:rgb(255,255,255);font-weight:400;font-style:italic;font-variant:normal;text-decoration:underline;vertical-align:baseline;white-space:pre-wrap">Join our email list</span></a><span style="font-size:10pt;font-family:Verdana;color:rgb(0,0,0);background-color:rgb(255,255,255);font-weight:400;font-style:italic;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap"> </span><span style="font-size:10pt;font-family:Verdana;color:rgb(67,67,67);background-color:rgb(255,255,255);font-weight:400;font-style:italic;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">for stories of Georgia libraries making an impact in our communities.</span></p></td></tr></tbody></table></div></div></div>
</blockquote></div>