Querying the NES 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  to the users in the NEE and CWP environments.

The Nymi.instance_name.audit.NymiBand schema contains information that is specific to the Nymi Bands in the NEE and CWP environments.

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_name.audit.NymiBand tables and display information about the last 1000 enrollments, perform the following steps.

  1. In the following example, the NES instance name is NES.
  2. Open SSMS and connect to the SQL server.
  3. On the Toolbar, click New Query.
  4. 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.nesadmin].[audit].[NymiBand] AS nb
    JOIN [Nymi.nesadmin].[audit].[UserCore] AS uc
    ON nb.UserCoreID = uc.ID
    WHERE nb.EventType = 'C'

NOTE: Change the database name in the FROM and JOIN statements to match your configuration.  The following figure shows you your database name as it appears in SSMS.

sql_query.png

 

In this query the:

  • 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.nesadmin].[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.nesadmin].[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 the primary key of the Nymi.nesadmin.audit.NymiBand table and the foreign key of Nymi.nesadmin.audit.UserCore table.
  • WHERE clause specifies that only Create (C) rows and the associated data values appear in the query results.

5. On the Toolbar, click Execute.

Articles in this section

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