MySQL Queries How To Guide
From Swivel Knowledgebase Wiki
Overview
The MySQL PINsafe database contains some information that is not available through the PINsafe Administration Console. This document outlines some of the information that can be queried.
The Audit Log is where PINsafe maintains an activity log for users. For more information on the Audit Log feature within PINsafe, see the Audit Log How To Guide.
For information on querying the Internal PINsafe database see Reporting Using Agent-XML How to Guide
For information on querying a MS SQL database see MS SQL Queries How To Guide
Prerequisites
- SQL Database;
- Audit log requires PINsafe 3.4 or higher (for SQL queries on PINsafe versions prior to PINsafe 3.4 see the note at the bottom of the document).
Performing MySQL Queries
The MySQL database can be queried through the MySQL command line or through the Webmin interface. depending on the installation, the database name may be pinsafe or pinsafe_rep.
MySQL Queries on a PINsafe appliance using Webmin
To view the MySQL database on an appliance, log into webmin (https://<pinsafe_server>:10000). Go to Servers -> MySQL Database Server and select the PINsafe database: this will either be named pinsafe or pinsafe_rep. If you have both, pinsafe_rep will be the active one. Click on the Execute SQL button and enter one of the below queries. For further information on see SQL commands in Webmin
Querying individual Repositories
Each Repository may be queried my name or Repository ID. To find a Repository ID user:
SELECT A ID, B NAME FROM PINSAFEL
To query by NAME the following is used:
AND PINSAFEL.B = <NAME>
To query by ID the following is used:
AND I = <ID>
Example
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 repositories <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>
MySQL 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 (obviously, the period can be varied), The query NOW() specifies the current time going back the required time period, 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 last login date and time
The following query lists the last date/time that each user logged in (the 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 listed by login time.
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
Replace <repos_name> in the above query with the name of the repository.
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
Count the number of Logins
This gives the total number of authentications in the audit table. (see note above for Audit Log):
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. (see note above for Audit Log):
SELECT COUNT(*) FROM PINSAFEM WHERE A=14
Count the number of successful logins over last 30 days
The following query shows the number of successful login attempts over the last 30 days (see note above for Audit Log):
SELECT COUNT(*) FROM PINSAFEM WHERE A=0 AND E > DATE_SUB(NOW(), INTERVAL 30 DAY)
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 failed logins over last 30 days
The following query shows the number of successful login attempts over the last 30 days (see note above for Audit Log):
SELECT COUNT(*) FROM PINSAFEM WHERE A=14 AND E > DATE_SUB(NOW(), INTERVAL 30 DAY)
Count the number of times each user has logged in
The following query counts the number of times each user has logged in (see Audit Log note above):
SELECT I Username, Count(*) Count FROM PINSAFEM WHERE A = ? GROUP BY I
Replace ? by 4 for unlocked, 5 for locked, 2 for self-reset, 6 for admin reset and 0 for successful login.
To look for a specific user, change "GROUP BY I" to "WHERE I = 'name'"
Count the users number of unsuccessful logins and self resets
The following query will give the number of self-reset and unsuccessful logins since the last successful login:
SELECT H Username, B FailCount, F ResetCount FROM PINSAFEJ
to look for a particular user, add "WHERE H = 'name'"
List the users who have reset their PIN from a particular date
SELECT 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'
List user date of creation and last login
The following query will show when the account was created and when the user last connected per day:
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
Te following query lists all users which 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
Te following query lists all users which 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)
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);
Count the number of users who have never logged in
The following query reports 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
The following query logs the number of users who have not logged in during 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 the number of users who have never logged in over the last 30 days but created more than 30 days ago
Since the above query includes new users, who might not have had a chance to log in yet, the following query logs users who were created over 30 days ago, but have not logged in during the last 30 days:
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 30 DAY))
List users connected today who have not previously logged in
This query will show users who connected today, but have not connected before.
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))
Queries on versions prior to PINsafe 3.4
For versions of PINsafe 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.
For the specific query Quintiles refer to, the SQL command should be:
SELECT H Username FROM PINSAFEJ WHERE G NOT IN (SELECT DISTINCT A FROM PINSAFED WHERE C=1)
Note that the code for login on PINSAFED is C=1, rather than C=0 for PINSAFEN. Therefore, the query to list last login date and time is
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
lists all the Users Ids, email address, 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
Troubleshooting
Are we able to isolate which regional PINsafe server the user has logged in on from the database?
No not though SQL queries. However this information is available in the PINsafe logs and obtainable through a log parser. Depending on how the PINsafe 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.
