1. Nymi
  2. Tutorials
  3. How to Articles

Performing More Complex Queries of the Audit Tables

Debbie Redmond

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.

  1. Define a SELECT statement then list the subsequent table columns data values that the query retrieves.
  2. 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.
  3. 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.
  4. Specify an ON clause to define the conditions of JOIN clause.
  5. 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.

Note: In the following example, the NES instance name is NES.
  1. Open SSMS and connect to the SQL server.
  2. On the Toolbar, click New Query.
  3. 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:

    1. SELECT statement returns the first 1000 rows and the subsequent table columns define the table columns data values that the query retrieves.
    2. 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.
    3. 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.
    4. ON clause defines the primary key of the Nymi.NES.audit.NymiBand table. and the foreign key of Nymi.NES.audit.UserCore table.
    5. WHERE clause specifies that only Create (C) rows and the associated data values appear in the query results.
  4. On the Toolbar, click Execute.

The Nymi Support Knowledge Base provides more information about creating and running complex SQL database queries.

Parent topic: Storage of NES Data

Articles in this section

See more
Was this article helpful?
0 out of 0 found this helpful
Share

Comments

0 comments

Please sign in to leave a comment.