Database Queries
================

Overview
--------

The MariaDB Swivel database contains information that is not always available through the Swivel Administration Console. This document outlines information that can be queried directly.

* The :doc:`AuditLog` is where Swivel maintains an activity log for users. 
* For information on producing reports through the Swivel Administration console, see :doc:`Reporting`.
* For information on querying Swivel via the Reporting API, see the :doc:`API`.
* For information on querying a MS SQL database, see the :doc:`MicrosoftSQLServer`.

.. seealso::
   For database schema information to help you write your own queries, see the :doc:`DatabaseSchema` documentation.

Prerequisites
-------------

* SQL Database access.
* Audit log queries require Swivel 3.4 or higher (for SQL queries on Swivel versions prior to 3.4 see the note at the bottom of the document).

Performing MariaDB Queries
------------------------

The MariaDB database can be queried through the MariaDB command line. Depending on the installation, the database name may be ``pinsafe`` or ``pinsafe_rep``.


Querying individual Repositories
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Each Repository may be queried by name or Repository ID.

**To find a Repository ID:**

.. code-block:: sql

   SELECT A ID, B NAME FROM PINSAFEL;

**To filter a query by Name:**

Add the following to your ``WHERE`` clause:

.. code-block:: sql

   AND PINSAFEL.B = '<NAME>'

**To filter a query by ID:**

Add the following to your ``WHERE`` clause:

.. code-block:: sql

   AND I = <ID>

**Examples:**

*Users who have never logged on (Successfully), for all repositories:*

.. code-block:: sql

   USE pinsafe_rep;
   SELECT H Username FROM PINSAFEJ WHERE G NOT IN (SELECT DISTINCT A FROM PINSAFEN WHERE C=0);

*Users who have never logged on (Successfully), for repository <NAME>:*
(Replace ``<NAME>`` with the name of the repository)

.. code-block:: sql

   SELECT H Username FROM PINSAFEJ
   INNER JOIN PINSAFEL WHERE PINSAFEJ.I = PINSAFEL.A
   WHERE PINSAFEJ.G NOT IN (SELECT DISTINCT A FROM PINSAFEN WHERE C=0)
   AND PINSAFEL.B = '<NAME>';

*Users who have never logged on (Successfully), for repository <ID>:*
(Replace ``<ID>`` with the ID of the repository)

.. code-block:: sql

   SELECT H Username FROM PINSAFEJ
   WHERE G NOT IN (SELECT DISTINCT A FROM PINSAFEN WHERE C=0)
   AND I = <ID>;

MariaDB Queries
-------------

List users who never have performed a successful login
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following query lists all users that have never (successfully) logged on to PINsafe:

.. code-block:: sql

   SELECT H Username FROM PINSAFEJ WHERE G NOT IN (SELECT DISTINCT A FROM PINSAFEN WHERE C=0);

List users who have not had a login in a defined time period
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following query lists all users that have not logged in in the last 3 months from the current date and time.
(Note: ``NOW()`` specifies the current time; to query going back to the start of the day use ``DATE()``).

.. code-block:: sql

   SELECT U.H Username
   FROM PINSAFEJ U
   LEFT OUTER JOIN PINSAFEN A ON U.G = A.A AND A.C = 0
   WHERE A.D IS NULL OR A.D < DATE_SUB(NOW(), INTERVAL 3 MONTH);

List users who have logged in over the last 30 days
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT U.H Username, A1.D CreationTime, A2.D LoginTime
   FROM PINSAFEJ U
   INNER JOIN PINSAFEN A1 ON U.G = A1.A AND A1.C = 3
   LEFT OUTER JOIN PINSAFEN A2 ON U.G = A2.A AND A2.C = 0
   WHERE A2.D > DATE_SUB(NOW(), INTERVAL 30 DAY);

List users who have logged in over the last 3 months
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT U.H Username, R.B Repository
   FROM PINSAFEJ U
   INNER JOIN PINSAFEL R ON U.I = R.A
   LEFT OUTER JOIN PINSAFEN A ON U.G = A.A AND A.C = 0
   WHERE A.D IS NULL OR A.D < DATE_SUB(NOW(), INTERVAL 3 MONTH);

List users who have logged in over the last 3 months (showing alert transport)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT U.H Username, R.B Repository, T.A Email
   FROM PINSAFEJ U
   INNER JOIN PINSAFEL R ON U.I = R.A
   LEFT OUTER JOIN PINSAFEA T ON U.G = T.C
   LEFT OUTER JOIN PINSAFEN A ON U.G = A.A AND A.C = 0
   WHERE A.D IS NULL OR A.D < DATE_SUB(NOW(), INTERVAL 3 MONTH);

List users who have logged in over the last 3 months (showing strings transport)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT U.H Username, R.B Repository, T.A Email
   FROM PINSAFEJ U
   INNER JOIN PINSAFEL R ON U.I = R.A
   LEFT OUTER JOIN PINSAFEH T ON U.G = T.C
   LEFT OUTER JOIN PINSAFEN A ON U.G = A.A AND A.C = 0
   WHERE A.D IS NULL OR A.D < DATE_SUB(NOW(), INTERVAL 3 MONTH);

List users who have logged in over the last 3 months (showing email - v3.9.1+)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT U.H Username, R.B Repository, A.C Email
   FROM PINSAFEJ U
   INNER JOIN PINSAFEL R ON U.I = R.A
   LEFT OUTER JOIN PINSAFEP A ON U.G = A.A AND A.B = 'email'
   LEFT OUTER JOIN PINSAFEN A ON U.G = A.A AND A.C = 0
   WHERE A.D IS NULL OR A.D < DATE_SUB(NOW(), INTERVAL 3 MONTH);

List last login date and time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following query lists the last date/time that each user logged in. It orders by oldest login time first (add ``DESC`` at the end for most recent first):

.. code-block:: sql

   SELECT U.H Username, A.D LoginTime
   FROM PINSAFEJ U
   INNER JOIN PINSAFEN A ON U.G = A.A AND A.C = 0
   ORDER BY LoginTime;

List last login date and time by repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following query lists the last date/time that each user logged in from a specified repository.
(Replace ``<repos_name>`` with the name of the repository).

.. code-block:: sql

   SELECT U.H Username, A.D LoginTime
   FROM PINSAFEJ U
   INNER JOIN PINSAFEN A ON U.G = A.A AND A.C = 0
   INNER JOIN PINSAFEL R ON U.I = R.A AND R.B = '<repos_name>'
   ORDER BY LoginTime;

List username and repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT U.H Username, R.B
   FROM PINSAFEJ U
   JOIN PINSAFEN A1 ON U.G = A1.A AND A1.C = 3
   LEFT OUTER JOIN PINSAFEN A2 ON U.G = A2.A AND A2.C=0
   JOIN PINSAFEL R ON U.I = R.A
   WHERE ( A1.D < DATE_SUB( NOW(), INTERVAL 30 DAY ) )
   AND ( A2.D IS NULL OR A2.D < DATE_SUB( NOW(), INTERVAL 180 DAY ) );

Count number of users in each group
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT R.B, G.A, COUNT(U.G)
   FROM PINSAFEJ U
   JOIN PINSAFEL R ON U.I=R.A
   JOIN PINSAFEI G ON U.G=G.B
   GROUP BY R.B, G.A
   ORDER BY R.B, G.A;

List number of users in each group
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT R.B, G.A, U.C
   FROM PINSAFEJ U
   JOIN PINSAFEL R ON U.I=R.A
   JOIN PINSAFEI G ON U.G=G.B
   ORDER BY R.B, G.A, U.C;

List last login date and time and show users who have never logged in
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following query shows all users and their last login date. Any dates listed as ``NULL`` means that the user has never logged in, or never changed their PIN.

.. code-block:: sql

   SELECT U.H Username, A1.D LastLogin, A2.D PINChange
   FROM PINSAFEJ U
   LEFT OUTER JOIN PINSAFEN A1 ON U.G = A1.A AND A1.C = 0
   LEFT OUTER JOIN PINSAFEN A2 ON U.G = A2.A AND A2.C = 1;

List login times for a given user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

(Replace ``?`` with the username in quotes, e.g., ``'jdoe'``).

.. code-block:: sql

   SELECT I Username, E DateTime FROM PINSAFEM WHERE A = 0 AND I = ? ORDER BY E;

List unsuccessful login times for a given user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

(Replace ``?`` with the username in quotes).

.. code-block:: sql

   SELECT I Username, E DateTime FROM PINSAFEM WHERE A = 14 AND I = ? ORDER BY E;

List login times for all users
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT I Username, E DateTime FROM PINSAFEM WHERE A = 0 ORDER BY I, E;

List unsuccessful login times for all users
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT I Username, E DateTime FROM PINSAFEM WHERE A = 14 ORDER BY I, E;

Count the number of Logins
~~~~~~~~~~~~~~~~~~~~~~~~~~

This gives the total number of authentications in the audit table.

.. code-block:: sql

   SELECT COUNT(*) FROM PINSAFEM WHERE A=0;

Count the number of Failed Logins
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This gives the total number of failed authentications in the audit table.

.. code-block:: sql

   SELECT COUNT(*) FROM PINSAFEM WHERE A=14;

Count the number of successful logins over last 30 days
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT COUNT(*) FROM PINSAFEM WHERE A=0 AND E > DATE_SUB(NOW(), INTERVAL 30 DAY);

Count the number of failed logins over last 30 days
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT COUNT(*) FROM PINSAFEM WHERE A=14 AND E > DATE_SUB(NOW(), INTERVAL 30 DAY);

Count the number of logins and login failures per day over the last 30 days
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT D1 AS Date, L AS Logins, F AS Failures
   FROM (SELECT Date(e) D1, count(*) L FROM pinsafem WHERE a=0 GROUP BY date(e)) AS Logins
   LEFT OUTER JOIN (SELECT Date(e) D2, count(*) F FROM pinsafem WHERE a=14 GROUP BY date(e)) AS Failures
   ON d1=d2;

Count the number of users who have logged in over the last 30 days
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT COUNT(*)
   FROM PINSAFEJ U
   INNER JOIN PINSAFEN A1 ON U.G = A1.A AND A1.C = 3
   LEFT OUTER JOIN PINSAFEN A2 ON U.G = A2.A AND A2.C = 0
   WHERE A2.D > DATE_SUB(NOW(), INTERVAL 30 DAY);

Count the number of times each user has logged in
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

(To look for a specific user, change ``GROUP BY I`` to ``WHERE I = 'name'``).

.. code-block:: sql

   SELECT I Username, Count(*) Count FROM PINSAFEM WHERE A = ? GROUP BY I;

**Parameter replacement for '?':**
* 4 = Unlocked
* 5 = Locked
* 2 = Self-reset
* 6 = Admin reset
* 0 = Successful login

Count the users number of unsuccessful logins and self resets
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This query gives the number of self-reset and unsuccessful logins since the last successful login.
(To look for a particular user, add ``WHERE H = 'name'``).

.. code-block:: sql

   SELECT H Username, B FailCount, F ResetCount FROM PINSAFEJ;

List the users who have reset their PIN from a particular date
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This includes ChangePIN, ResetPIN and Admin Reset/Resend of the PIN.

.. code-block:: sql

   SELECT DISTINCT PINSAFEJ.H
   FROM PINSAFEJ
   INNER JOIN PINSAFEN ON PINSAFEJ.G = PINSAFEN.A
   WHERE (PINSAFEN.C = 1 OR PINSAFEN.C = 2 OR PINSAFEN.C=6)
   AND PINSAFEN.D > '2011-11-18 10:30';

**Query Key:**
* ``DISTINCT``: Allows each user to be listed only once for the combination of resets.
* ``PINSAFEN.C = 1``: ChangePIN
* ``PINSAFEN.C = 2``: Self Reset
* ``PINSAFEN.C = 6``: Admin Reset

List the users who have used ChangePIN from a particular date
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT PINSAFEJ.H FROM PINSAFEJ
   INNER JOIN PINSAFEN ON PINSAFEJ.G = PINSAFEN.A
   WHERE PINSAFEN.C = 1 AND PINSAFEN.D > '2013-12-23 16:30';

List the users who have used Self Reset from a particular date
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT PINSAFEJ.H FROM PINSAFEJ
   INNER JOIN PINSAFEN ON PINSAFEJ.G = PINSAFEN.A
   WHERE PINSAFEN.C = 2 AND PINSAFEN.D > '2013-12-23 16:30';

List the users who have had an Admin reset from a particular date
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT PINSAFEJ.H FROM PINSAFEJ
   INNER JOIN PINSAFEN ON PINSAFEJ.G = PINSAFEN.A
   WHERE PINSAFEN.C = 6 AND PINSAFEN.D > '2013-12-23 16:30';

List user date of creation and last login
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT U.H Username, A1.D CreationTime, A2.D LoginTime
   FROM PINSAFEJ U
   INNER JOIN PINSAFEN A1 ON U.G = A1.A AND A1.C = 3
   LEFT OUTER JOIN PINSAFEN A2 ON U.G = A2.A AND A2.C = 0;

List users who have been created in the last 30 days
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT U.H FROM PINSAFEJ U
   JOIN PINSAFEN A ON U.G=A.A AND A.C=3
   WHERE A.D > DATE_SUB(NOW(), INTERVAL 30 DAY);

List users who have been created in the last month
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT U.H FROM PINSAFEJ U
   JOIN PINSAFEN A ON U.G=A.A AND A.C=3
   WHERE A.D > DATE_SUB(NOW(), INTERVAL 1 MONTH);

Count the number of users who have never logged in
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT COUNT(*) FROM PINSAFEJ WHERE G NOT IN (SELECT A FROM PINSAFEN WHERE C=0);

Count the number of users who have never logged in over the last 30 days
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT COUNT(*) FROM PINSAFEJ U
   LEFT OUTER JOIN PINSAFEN A ON U.G = A.A AND A.C=0
   WHERE A.D IS NULL OR A.D < DATE_SUB(NOW(), INTERVAL 30 DAY);

Count users who have never logged in over the last 180 days but created > 30 days ago
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT COUNT(*) FROM PINSAFEJ U
   JOIN PINSAFEN A1 ON U.G = A1.A AND A1.C = 3
   LEFT OUTER JOIN PINSAFEN A2 ON U.G = A2.A AND A2.C=0
   WHERE (A1.D < DATE_SUB(NOW(), INTERVAL 30 DAY))
   AND (A2.D IS NULL OR A2.D < DATE_SUB(NOW(), INTERVAL 180 DAY));

List users who have never logged in over the last 180 days but created > 30 days ago
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT U.H Username FROM PINSAFEJ U
   JOIN PINSAFEN A1 ON U.G = A1.A AND A1.C = 3
   LEFT OUTER JOIN PINSAFEN A2 ON U.G = A2.A AND A2.C=0
   WHERE (A1.D < DATE_SUB(NOW(), INTERVAL 30 DAY))
   AND (A2.D IS NULL OR A2.D < DATE_SUB(NOW(), INTERVAL 180 DAY));

List users connected today who have not previously logged in
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT U.H Username, A1.D CreationTime, A2.D LoginTime
   FROM PINSAFEJ U
   INNER JOIN PINSAFEN A1 ON U.G = A1.A AND A1.C = 3
   INNER JOIN PINSAFEN A2 ON U.G = A2.A AND A2.C = 0
   WHERE A2.D > DATE_SUB(NOW(), INTERVAL 1 DAY)
   AND NOT EXISTS(SELECT G FROM PINSAFEM A3 WHERE A3.G=U.H AND A3.E < DATE_SUB(NOW(), INTERVAL 1 DAY));

List User IDs, email, created date, last login, last pin change and repository name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT U.H Username, U.E ReposName, T.A Email, A1.D CreateDate, A2.D LoginDate, A3.D PINChange
   FROM PINSAFEJ U
   LEFT OUTER JOIN PINSAFEA T ON U.G = T.C
   LEFT OUTER JOIN PINSAFEN A1 ON U.G = A1.A AND A1.C = 3
   LEFT OUTER JOIN PINSAFEN A2 ON U.G = A2.A AND A2.C = 0
   LEFT OUTER JOIN PINSAFEN A3 ON U.G = A3.A AND A3.C = 1;

List PIN expiry for users
~~~~~~~~~~~~~~~~~~~~~~~~~

(Replace ``60`` in the query below with your actual PIN validity period).

.. code-block:: sql

   SELECT U.H USERNAME, ADDDATE(GREATEST(COALESCE(A1.D, '2000-01-01'), COALESCE(A2.D, '2000-01-01'), COALESCE(A3.D, '2000-01-01')), 60) EXPIRY_DATE
   FROM PINSAFEJ U
   LEFT OUTER JOIN PINSAFEN A1 ON U.G = A1.A AND A1.C = 1
   LEFT OUTER JOIN PINSAFEN A2 ON U.G = A2.A AND A2.C = 6
   LEFT OUTER JOIN PINSAFEN A3 ON U.G = A3.A AND A2.C = 3;

List all Disabled accounts
~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT U.H Name FROM PINSAFEJ U JOIN PINSAFEC S ON U.G = S.C AND S.B = 0 WHERE S.D = 1;

List all users with inactive, locked, disabled and deleted state
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT U.H UID, R.B REPOS, S1.D DISABLED, S2.D LOCKED, S3.D DELETED, S4.D INACTIVE
   FROM PINSAFEJ U JOIN PINSAFEL R ON U.I=R.A
   LEFT OUTER JOIN PINSAFEC S1 ON U.G=S1.C AND S1.B=0
   LEFT OUTER JOIN PINSAFEC S2 ON U.G=S2.C AND S2.B=1
   LEFT OUTER JOIN PINSAFEC S3 ON U.G=S3.C AND S3.B=4
   LEFT OUTER JOIN PINSAFEC S4 ON U.G=S4.C AND S4.B=5;

List all users with state and show creation, login and PIN change date
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code-block:: sql

   SELECT U.H UID, R.B REPOS, S1.D DISABLED, S2.D LOCKED, S3.D DELETED, S4.D INACTIVE, A1.D CREATED, A2.D LOGGEDIN, A3.D PINCHANGE
   FROM PINSAFEJ U JOIN PINSAFEL R ON U.I=R.A
   LEFT OUTER JOIN PINSAFEC S1 ON U.G=S1.C AND S1.B=0
   LEFT OUTER JOIN PINSAFEC S2 ON U.G=S2.C AND S2.B=1
   LEFT OUTER JOIN PINSAFEC S3 ON U.G=S3.C AND S3.B=4
   LEFT OUTER JOIN PINSAFEC S4 ON U.G=S4.C AND S4.B=5
   LEFT OUTER JOIN PINSAFEN A1 ON U.G=A1.A AND A1.C=3
   LEFT OUTER JOIN PINSAFEN A2 ON U.G=A2.A AND A2.C=0
   LEFT OUTER JOIN PINSAFEN A3 ON U.G=A3.A AND A3.C=1;

Queries on versions prior to Swivel 3.4
---------------------------------------

.. warning::
   For versions of Swivel earlier than 3.4, the table ``PINSAFED`` performed a similar function to that now performed by ``PINSAFEN``, i.e. recording the latest date/time of various activities.

The code for login on ``PINSAFED`` is **C=1**, rather than **C=0** for ``PINSAFEN``.

**Query to list last login date and time (Pre-3.4):**

.. code-block:: sql

   SELECT U.H Username, A.D LoginTime
   FROM PINSAFEJ U
   INNER JOIN PINSAFED A ON U.G = A.A AND A.C = 1
   ORDER BY LoginTime;

Known Issues
------------

**Duplicate Entries in Lock Reports:**
When running reports to list locked users, some users may be listed multiple times due to duplicate locks in the database. However, the lock count and the number of locks displayed in the Swivel Administration Console will display the correct value for the number of locked users.

Troubleshooting
---------------

**Are we able to isolate which regional Swivel server the user has logged in on from the database?**

No, not though SQL queries. However, this information is available in the Swivel logs and obtainable through a log parser. Depending on how the Swivel agent is configured, there is a 'source' option in the Agent XML. This stores the IP information of the user logging on and there is a field in the database to store this information.

**Locked user account list shows some users as not locked**

The locked user count will report not only those that are flagged as locked but those accounts that have more than the number of failed authentications. When the user who has exceeded the maximum login attempts but whose account is not marked locked next tries to login, the account will be marked as locked.