The Audit Logs contain data for all create, update, delete events that are related to users, Nymi Bands, certificates, application settings, and the external authenticator.
Overview of SQL queries
The following provides you with a high level overview of the steps to follow to build more complex queries that gather information that is contained in multiple schemas in the Nymi.instance_name database when a table contains a foreign key that is linked to the primary key of another table.
- Define a SELECT statement then list the subsequent table columns data values that the query retrieves.
- Add a FROM clause to define the primary table from which to retrieve the column data values, and use an AS statement renames the table.
- Add a JOIN clause to define the table that contains column value data that is related to the primary table, and the AS statement renames the table.
- Specify an ON clause to define the conditions of JOIN clause.
- Add an WHERE clause that defines a filter for the results.
Querying for the database to gather information about enrollments and the Nymi Band to user relationship
The Nymi.instance_name.audit.UserCore schema contains information that is specific the users in the CWP environment. The Nymi.instance_name.audit.NymiBand schema contains information that is specific to the Nymi Bands in the CWP environment
These two schemas share the a common UserID value, which allows you to generate results that provide details about a user and their associated Nymi Band.
To retrieve information from the Nymi.instance_name.audit.UserCore and Nymi.instance.audit.NymiBand tables and display information about the last 1000 enrollments, perform the following steps.
- Open SSMS and connect to the SQL server.
- On the Toolbar, click New Query.
- In the SQL Query window, type the following SQL query command.
SELECT TOP (1000) nb.[Identity] ,nb.[EventTime] ,nb.[EventType] ,nb.[SystemUser] ,nb.[ID] ,nb.[UserCoreID] ,nb.[NymiBandID] ,nb.[NfcUID] ,nb.[IsActive] ,nb.[IsPrimary] ,nb.[HasFingerprint] ,nb.[EnrollmentStatus] ,nb.[MiscNote] ,nb.[CreatedAt] ,uc.Domain ,uc.Username FROM [Nymi.NES].[audit].[NymiBand] AS nb JOIN [Nymi.NES].[audit].[UserCore] AS uc ON nb.UserCoreID = uc.ID WHERE nb.EventType = 'C'
In this query:
- SELECT statement returns the first 1000 rows and the subsequent table columns define the table columns data values that the query retrieves.
- FROM clause defines [Nymi.NES].[audit].[NymiBand] as the primary table from which to retrieve the column data values, and shortens the table name to nb.
- JOIN clause defines [Nymi.NES].[audit].[UserCore] as the table that contains column value data that is related to the primary table, and shortens the table name to uc.
- ON clause defines the primary key of the Nymi.NES.audit.NymiBand table. and the foreign key of Nymi.NES.audit.UserCore table.
- WHERE clause specifies that only Create (C) rows and the associated data values appear in the query results.
- On the Toolbar, click Execute.
The Nymi Support Knowledge Base provides more information about creating and running complex SQL database queries.