Creating Reports

Introduction

Effective with version 7.3, reports can be created and generated using custom SQL queries to fulfill audit and traceability requirements. Please refer to the following documentation and provided templates to guide your query development and execution.

This article assumes readers already have a basic understanding of SQL-like query languages.

Enable Feature

By default this feature will be included once you are on version 7.3 or later. If you are using an external database, such as MS SQL, it will be necessary to configure a read only SQL account that will be used when running your queries. From Options - Connections select the SQL Database section and edit your existing connection. Enable Use read only access for report queries and click the Read only button.

Enter your read-only MS SQL account and Ok to save and close.

Click the Test button to check that the user account is functional and Save to close and save the changes.

On your MS SQL side the read-only user should be configured with similar settings as below for your vSEC:CMS database.

To enable the feature navigate to Options - Security. In the Database Reporting Security section enable Allow SQL statements in report queries to enable the system to run SQL queries for reporting generation.

If you are using an external database (like MS SQL), you can enable the Skip check for read only database connection setting. This stops the system from verifying whether the database user has strict read-only permissions.

We highly recommend using a read-only user account for external connections to prevent accidental data writes or modifications.

Canned Reports

By default 3 sample canned reports are included in vSEC:CMS. The reports are for systems that are using the default SQLite database and for systems that are using external SQL database, MS SQL in this case.

Navigate to Repository - Reports and in the Repository Reports drop-down field you will see these 3 canned reports. These are:

Revoked last 100 days - SQL - this report will list all credentials that have been revoked in the system for the last 100 days. Two queries are provided, an SQLite (the actual query is inside sqlite{}sqlite tag) and an MS SQL (the actual query is inside mssql{}mssql tag) query. Depending on what type of database you are using either one will be performed.

sqlite{
  SELECT 
      t.TimeStamp AS "Revocation Date (UTC)", 
      t.CSN AS "Card Serial Number", 
      c.IdDispName AS "Card Holder Name", 
      IFNULL(o.Name, 'System / Automated') AS "Revoked By", 
      t.Param1 AS "Revocation Reason",
      t.ActionStrg AS "Action Description"
  FROM CvTLogCards t
  LEFT JOIN CvOperators o 
      ON t.UserID = o.ID
  LEFT JOIN CvCards c 
      ON t.CSN = c._OBJ_KEY
  WHERE t.ActionIDCode = 'PROCESS | REVOKE'
    AND t.TimeStamp >= datetime('now', '-100 days')
  ORDER BY t.TimeStamp DESC;
}sqlite
mssql{
  SELECT
      t.TimeStamp AS [Revocation Date (UTC)],
      t.CSN AS [Card Serial Number],
      c.IdDispName AS [Card Holder Name],
      ISNULL(o.Name, 'System / Automated') AS [Revoked By],
      t.Param1 AS [Revocation Reason],
      t.ActionStrg AS [Action Description]
  FROM CvTLogCards t
  LEFT JOIN CvOperators o
      ON t.UserID = o.ID
  LEFT JOIN CvCards c
      ON t.CSN = c._OBJ_KEY
  WHERE t.ActionIDCode = 'PROCESS | REVOKE'
    AND t.TimeStamp >= DATEADD(day, -100, GETUTCDATE())
  ORDER BY t.TimeStamp DESC;
}mssql

Issued last 100 days - SQL - this report will list all credentials that have been issued in the system for the last 100 days. Two queries are provided, an SQLite (the actual query is inside sqlite{}sqlite tag) and an MS SQL (the actual query is inside mssql{}mssql tag) query. Depending on what type of database you are using either one will be performed.

sqlite{
  SELECT 
      t.TimeStamp AS "Issue Date (UTC)", 
      t.CSN AS "Card Serial Number", 
      t.ActionStrg AS "Action Description", 
      t.Param1 AS "Details",
      IFNULL(o.Name, 'System/Self-Service') AS "Performed By"
  FROM CvTLogCards t
  LEFT JOIN CvOperators o 
      ON t.UserID = o.ID
  WHERE t.ActionIDCode = 'PROCESS | ISSUE'
    AND t.TimeStamp >= datetime('now', '-100 days')
  ORDER BY t.TimeStamp DESC;
}sqlite
mssql{
  SELECT
      t.TimeStamp AS [Issue Date (UTC)],
      t.CSN AS [Card Serial Number],
      c.IdDispName AS [Card Holder Name],
      ISNULL(o.Name, 'System/Automated') AS [Issued By],
      t.ActionStrg AS [Action Description],
      t.Param1 AS [Details]
  FROM CvTLogCards t
  LEFT JOIN CvOperators o
      ON t.UserID = o.ID
  LEFT JOIN CvCards c
      ON t.CSN = c._OBJ_KEY
  WHERE t.ActionIDCode = 'PROCESS | ISSUE'
    AND t.TimeStamp >= DATEADD(day, -100, GETUTCDATE())
  ORDER BY t.TimeStamp DESC;
}mssql

PIN reset last 100 days - SQL - this report will list all credentials that have had a PIN reset operation performed in the system for the last 100 days. Two queries are provided, an SQLite (the actual query is inside sqlite{}sqlite tag) and an MS SQL (the actual query is inside mssql{}mssql tag) query. Depending on what type of database you are using either one will be performed.

sqlite{
  SELECT 
      t.TimeStamp AS "Unblock Date (UTC)", 
      t.CSN AS "Card Serial Number", 
      c.IdDispName AS "Card Holder Name", 
      IFNULL(o.Name, 'User (Self-Service)') AS "Performed By", 
      t.ActionStrg AS "Action Description"
  FROM CvTLogCards t
  LEFT JOIN CvOperators o 
      ON t.UserID = o.ID
  LEFT JOIN CvCards c 
      ON t.CSN = c._OBJ_KEY
  WHERE t.ActionIDCode = 'UNBLOCK'
    AND t.TimeStamp >= datetime('now', '-100 days')
  ORDER BY t.TimeStamp DESC; 
}sqlite
mssql{
  SELECT
      t.TimeStamp AS [Unblock Date (UTC)],
      t.CSN AS [Card Serial Number],
      c.IdDispName AS [Card Holder Name],
      ISNULL(o.Name, 'User (Self-Service)') AS [Performed By],
      t.ActionStrg AS [Action Description]
  FROM CvTLogCards t
  LEFT JOIN CvOperators o
      ON t.UserID = o.ID
  LEFT JOIN CvCards c
      ON t.CSN = c._OBJ_KEY
  WHERE t.ActionIDCode = 'UNBLOCK'
    AND t.TimeStamp >= DATEADD(day, -100, GETUTCDATE())
  ORDER BY t.TimeStamp DESC;
}mssql

Click the Manage button and select the template you want to check and click Edit. Click the Configure query button to view and/or edit the SQL query.

SQL queries should be built using vSEC:CMS database schema document. Please email support to request this document.

Click the Execute query button to run the query. If the query is constructed correctly you will see similar to below. Click CTRL+C to copy the result and paste into a text editor.

To format your generated reports with a custom stylesheet, click Import to upload your stylesheet. To extract the stylesheet used click the Save default button. To reset to default stylesheet click Reset to default. To export an already imported stylesheet click Export.

Select the report you wish to generate and click Generate. Click View to review the results. Click Save to save the results to either an xml, html or JSON file. Click Copy to copy the content to the clipboard and paste into a text editor, the format of which will be csv.

Sample Queries

The following section provides a library of common query templates designed to illustrate standard construction and application logic. This documentation assumes a functional proficiency in SQL syntax and database management.

Please utilize the vSEC:CMS database schema document as a technical reference for all SQL development. You may request this document directly from Versasec at support@versasec.com.

SQLite Queries

If you are using the default database in vSEC:CMS then you will need to construct SQLite queries. This section will provide some samples to get you going with this type of query construction.

List Credential Details

Description of Query

Query to show all credentials issued in the last 30 days. The query will return the CSN, timestamp, operator who issued the credential(s), current status of the credential, certificate serial number, certificate expiration date, the name of the user who the credential was issued to, the template name used to issue the credential and the CSN in decimal format:

SELECT 
    t.CSN AS "Card Serial Number (Hex)",
    c2.RfidSNDec AS "Card Serial Number (Decimal)",
    t.TimeStamp AS "Issue Date (UTC)", 
    IFNULL(o.Name, 'System/Automated') AS "Operator Name", 
    c2.TokenStatusStrg AS "Current Card Status", 
    IFNULL(cert.CertSN, 'No Certificate Found') AS "Certificate Serial Number",
    IFNULL(cert.validTo, 'N/A') AS "Certificate Expiration Date",
    c2.IdDispName AS "Issued To (User)",
    c2.CardTypeStr AS "Template Name"
FROM CvTLogCards t
LEFT JOIN CvOperators o 
    ON t.UserID = o.ID
LEFT JOIN CvCards2 c2 
    ON t.CSN = c2._OBJ_KEY
LEFT JOIN CvCardCerts cert 
    ON t.CSN = cert.CsnKey
WHERE t.ActionIDCode = 'PROCESS | ISSUE'
  AND t.TimeStamp >= datetime('now', '-30 days')
ORDER BY t.TimeStamp DESC;

Full Transaction Log Details for a Credential

Description of Query

Query to show all transaction log history for a credential based on CSN of the card:

You need to replace YOUR_CARD_CSN_HERE with the actual CSN of the credential you want to check.

SELECT 
    t.TimeStamp AS "Date/Time (UTC)", 
    t.CSN AS "Card Serial Number", 
    IFNULL(o.Name, 'System/Cardholder') AS "Executed By", 
    t.ActionStrg AS "Action Performed", 
    t.Param1 AS "Action Details",
    t.ActionIDCode AS "Internal Lifecycle Code"
FROM CvTLogCards t
LEFT JOIN CvOperators o 
    ON t.UserID = o.ID
WHERE t.CSN = 'YOUR_CARD_CSN_HERE'
ORDER BY t.TimeStamp DESC;

List All Issued Certificates

Description of Query

Query to list all credentials who had their certificate reissued or enrolled over the last 30 days. The query will return the user's name, CSN, timestamp, who performed the operation and certificate serial number:

SELECT 
    t.TimeStamp AS "Operation Date (UTC)", 
    t.CSN AS "Card Serial Number", 
    c.IdDispName AS "Card Holder Name", 
    IFNULL(o.Name, 'System/Self-Service') AS "Performed By", 
    IFNULL(cert.CertSN, 'No Matching Record') AS "Certificate Serial Number"
FROM CvTLogCards t
LEFT JOIN CvOperators o 
    ON t.UserID = o.ID
LEFT JOIN CvCards c 
    ON t.CSN = c._OBJ_KEY
LEFT JOIN CvCardCerts cert 
    ON t.CSN = cert.CsnKey
WHERE t.ActionIDCode IN ('CERT_REISSUE', 'CERT_ENROLLED', 'NEW_CERT')
  AND t.TimeStamp >= datetime('now', '-30 days')
ORDER BY t.TimeStamp DESC;

List All Credentials with Specific Status

Description of Query

Query to list all credentials CSN, certificate serial number, assigned user and certificate expiration date that are "Assigned, Cert enrolled, Issued, Initiated" status.

If a credential has multiple certificate(s) issued it will list the additional certificate as a separate line item.

SELECT 
    c._OBJ_KEY AS "Card Serial Number", 
    IFNULL(cert.CertSN, 'No Certificate Found') AS "Certificate Serial Number",
    c.IdDispName AS "Assigned User",
    cert.validTo AS "Certificate Expiration Date",
    c.TokenStatusStrg AS "Current Status List"
FROM CvCards c
LEFT JOIN CvCardCerts cert 
    ON c._OBJ_KEY = cert.CsnKey
WHERE c.TokenStatus = 17538
ORDER BY c.IdDispName;

MS SQL Queries

If you are using MS SQL as the database for vSEC:CMS then you will need to construct SQL queries that are formulated in the correct way for MS SQL. This section will provide some samples to get you going with this type of query construction.

List Credential Details

Description of Query

SELECT 
    t.CSN AS [Card Serial Number (Hex)],
    t.TimeStamp AS [Issue Date (UTC)], 
    o.Name AS [Operator Name], 
    c2.TokenStatusStrg AS [Current Card Status], 
    cert.CertSN AS [Certificate Serial Number],
    cert.validTo AS [Certificate Expiration Date],
    c2.IdDispName AS [Issued To (User)],
    c2.CardTypeStr AS [Template Name]
FROM CvTLogCards t
INNER JOIN CvOperators o 
    ON t.UserID = o.ID
LEFT JOIN CvCards2 c2 
    ON t.CSN = c2._OBJ_KEY
LEFT JOIN CvCardCerts cert 
    ON t.CSN = cert.CsnKey
WHERE t.ActionIDCode = 'PROCESS | ISSUE'
  AND t.TimeStamp >= DATEADD(day, -30, GETUTCDATE())
ORDER BY t.TimeStamp DESC;

Full Transaction Log Details for a Credential

Description of Query

Query to show all transaction log history for a credential based on CSN of the card:

You need to replace YOUR_CARD_CSN_HERE with the actual CSN of the credential you want to check.

SELECT 
    t.TimeStamp AS [Date/Time (UTC)], 
    t.CSN AS [Card Serial Number], 
    o.Name AS [Operator Name], 
    t.ActionStrg AS [Action Performed], 
    t.Param1 AS [Action Details],
    t.ActionIDCode AS [Internal Action Code]
FROM CvTLogCards t
LEFT JOIN CvOperators o 
    ON t.UserID = o.ID
WHERE t.CSN = 'YOUR_CARD_CSN_HERE'
ORDER BY t.TimeStamp DESC;

List All PIN Resets

Description of Query

Query to show all performed PIN unblocks/reset over the last 30 says :

SELECT 
    t.TimeStamp AS [Action Date (UTC)], 
    t.CSN AS [Card Serial Number], 
    c.IdDispName AS [Card Holder Name], 
    ISNULL(o.Name, 'Self-Service / User') AS [Performed By], 
    t.ActionStrg AS [Action Description]
FROM CvTLogCards t
LEFT JOIN CvOperators o 
    ON t.UserID = o.ID
LEFT JOIN CvCards c 
    ON t.CSN = c._OBJ_KEY
WHERE t.ActionIDCode IN ('UNBLOCK', 'INIT_PIN', 'USS_UNBLOCK')
  AND t.TimeStamp >= DATEADD(day, -30, GETUTCDATE())
ORDER BY t.TimeStamp DESC;

List All Issued Certificates

Description of Query

SELECT 
    t.TimeStamp AS [Operation Date (UTC)], 
    t.CSN AS [Card Serial Number], 
    c.IdDispName AS [Card Holder Name], 
    ISNULL(o.Name, 'System/Self-Service') AS [Performed By], 
    cert.CertSN AS [Certificate Serial Number],
    t.ActionStrg AS [Action Description]
FROM CvTLogCards t
LEFT JOIN CvOperators o 
    ON t.UserID = o.ID
LEFT JOIN CvCards c 
    ON t.CSN = c._OBJ_KEY
LEFT JOIN CvCardCerts cert 
    ON t.CSN = cert.CsnKey
WHERE t.ActionIDCode IN ('CERT_REISSUE', 'CERT_ENROLLED', 'NEW_CERT')
  AND t.TimeStamp >= DATEADD(day, -30, GETUTCDATE())
ORDER BY t.TimeStamp DESC;

List All Revoked and Retired Credentials

Description of Query

Query to list all credentials revoked and retired in the last 30 days. The query will return CSN, timestamp, person who performed the operation, certificate serial number and reason:

SELECT 
    t.CSN AS [Card Serial Number], 
    t.TimeStamp AS [Operation Date (UTC)], 
    o.Name AS [Performed By], 
    c.IdDispName AS [Card Holder Name],
    cert.CertSN AS [Certificate Serial Number],
    t.Param1 AS [Reason/Details],
    t.ActionStrg AS [Action Performed]
FROM CvTLogCards t
INNER JOIN CvOperators o 
    ON t.UserID = o.ID
LEFT JOIN CvCards c 
    ON t.CSN = c._OBJ_KEY
LEFT JOIN CvCardCerts cert 
    ON t.CSN = cert.CsnKey
WHERE t.ActionIDCode IN ('PROCESS | REVOKE', 'PROCESS | RETIRE')
  AND t.TimeStamp >= DATEADD(day, -30, GETUTCDATE())
ORDER BY t.TimeStamp DESC;

List All Credentials with Specific Status

Description of Query

Query to list all credentials CSN, certificate serial number, assigned user and certificate expiration date that are "Assigned, Cert enrolled, Issued, Initiated" status.

If a credential has multiple certificate(s) issued it will list the additional certificate as a separate line item.

SELECT 
    c._OBJ_KEY AS [Card Serial Number], 
    ISNULL(cert.CertSN, 'No Certificate Found') AS [Certificate Serial Number],
    c.IdDispName AS [Assigned User],
    c.TokenStatusStrg AS [Current Status List],
    cert.validTo AS [Cert Expiration]
FROM CvCards c
LEFT JOIN CvCardCerts cert 
    ON c._OBJ_KEY = cert.CsnKey
WHERE c.TokenStatus = 17538
ORDER BY c.IdDispName;

Search

Introduction

Enable Feature

Canned Reports

Sample Queries

SQLite Queries

List Credential Details

Full Transaction Log Details for a Credential

List All Issued Certificates

List All Credentials with Specific Status

MS SQL Queries

List Credential Details

Full Transaction Log Details for a Credential

List All PIN Resets

List All Issued Certificates

List All Revoked and Retired Credentials

List All Credentials with Specific Status