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 Audit Log is where Swivel maintains an activity log for users.

  • For information on producing reports through the Swivel Administration console, see Reporting.

  • For information on querying Swivel via the Reporting API, see the API Reference.

  • For information on querying a MS SQL database, see the Microsoft SQL Server.

See also

For database schema information to help you write your own queries, see the Database Schema 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:

SELECT A ID, B NAME FROM PINSAFEL;

To filter a query by Name:

Add the following to your WHERE clause:

AND PINSAFEL.B = '<NAME>'

To filter a query by ID:

Add the following to your WHERE clause:

AND I = <ID>

Examples:

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

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)

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)

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:

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()).

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

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

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)

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)

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+)

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):

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).

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

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

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

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.

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').

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).

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

List login times for all users

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

List unsuccessful login times for all users

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.

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.

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

Count the number of successful logins over last 30 days

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

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

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

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').

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').

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.

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

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

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

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

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

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

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

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

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

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

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

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

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).

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

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

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

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):

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.