This query came from an IUG 2021 Annual Conference forum. My notes are incomplete so I’m not sure who contributed this to the group. If this is your code, feel free to contact me and I’ll credit you properly! This query pulls a list of Polaris users and the last time they logged into the system. It’s great for auditing your users list and making sure that users who are no longer with your library are cleaned out of the system.
DECLARE @library INT = 2 SELECT pu.Name, MAX(th.tranclientdate) as [Last Login Date] FROM Polaris.Polaris.PolarisUsers pu WITH (NOLOCK) LEFT JOIN PolarisTransactions.Polaris.TransactionHeaders th ON pu.PolarisUserID = th.PolarisUserID AND th.TransactionTypeID = 7200 WHERE pu.PolarisUserID NOT IN ( SELECT up.PolarisUserID FROM Polaris.Polaris.UsersPPPP up WITH (NOLOCK) WHERE up.AttrID = 659 AND ( up.Value = 'closed' OR up.Value = 'suspended' ) ) AND ( pu.OrganizationID IN ( SELECT OrganizationID FROM Polaris.Polaris.Organizations o WHERE @library IN ( o.OrganizationID, o.ParentOrganizationID) ) OR pu.BranchID IN ( SELECT OrganizationID FROM Polaris.Polaris.Organizations o WHERE @library in ( o.OrganizationID, o.ParentOrganizationID) ) ) GROUP BY pu.Name ORDER BY MAX(th.TranClientDate), pu.Name