Last Time Users Logged Into Polaris

Clearinghouse Repository Polaris Repository
,
1

Last Time Users Logged Into Polaris

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