Query PingFederate Audit Log Database

Once PingFederate audit logs are configured to be written into a database, individual events will be stored as table records (e.g. rows of the audit record table). The rows make up the security-relevant chronological record sequence of activities that have transpired across the PingFederate cluster of administration and runtime engines. By examining the audit records in the database, a security admin can inspect selected portions of the audit log, patterns of access, as well as monitor activity of specific users.

Each PingFederate audit log row provides the following columns:

• id – A system generated unique id for this entry.
• dtime – The date and time of the log entry.
• event – The type of the log entry. This could be one of the following:
  • SSO
  • AUTHN_ATTEMPT
  • OAuth
  • PWD_RESET_REQUEST
  • PWD_RESET_REQUEST_RESPONSE
  • PWD_RESET
  • SLO
  • STS
  • (null)
• username – The name of the user.
• ip – The IP Address of the request. This will often be the IP address of the load balancer directing the request to the PingFederate engine.
• app – The application associated with the request
• host – The PingFederate node where the log entry originates
• protocol – The protocol of the request, such as SAML20 or OAuth2
• role – The role of the connection (IdP or SP)
• partnerid – The partner id of the connection
• status – The status of the request (success, failure, in_progress ,…)
• adapterid – The adapter id of the adapter used to service the request
• description – A text description of the log message
• responsetime – The response time of the PingFederate server in milliseconds

Now that we have looked at the structure of the audit log record stored in the database, we need to design SQL queries to extract the data. One thing to be aware of the individual audit log records is that any of the values (e.g. columns) can be null, with the exception of id and dtime. Knowing that there are often null columns, queries to the information should be designed appropriately. Below are several SQL queries that we have found to extract meaningful information from the database.

How many unique users have authenticated with PingFederate?

SELECT COUNT(DISTINCT username) FROM audit_log

How many transactions have been processed today?

SELECT COUNT(*) FROM audit_log WHERE DATE(dtime) = CURDATE();

What is the average response time for SSO events?

SELECT AVG(responsetime) FROM audit_log WHERE event=’SSO’

List the OAUTH events in the system?

SELECT * FROM audit_log WHERE event=’OAuth’ order by dtime

List transactions for a user?

SELECT * FROM audit_log WHERE user=someuser order by dtime

List number of password changes in a day?

SELECT count(*) FROM audit_log WHERE DATE(dtime) = CURDATE() AND event=’PWD_RESET’

These queries can be combined to provide a wealth of information about what is happening in your environment. Unfortunately, these queries can grow to be extremely complex and the results can be verbose and difficult to process. Tune in next week to see how we can help make sense of this data and provide quick and meaningful insights into your IAM infrastructure.

I hope you found this article helpful and utilize the recommendation to leverage a database for logging when additional performance is needed within your PingFederate deployment. If you have comments or questions, please contact us.