1. Nymi
  2. Tutorials
  3. How to Articles

NES SQL Database Overview

Updated
Debbie Redmond

NES SQL Database Overview

Connected Worker Platform records configuration information about the Connected Worker Platform components in the NES database. When configuration changes are made, the system records information in the appropriate SQL tables.

The NES database name is Nymi.instance_name, where instance_name is the instance name that was specified in the NES Setup wizard. For example, Nymi.NES. If an instance name was not specified, the default database name is Nymi.NESg2.admin.

The NES SQL database contains several schemas that are named and grouped according to the type of stored data.

Date and time values appear in UTC (Coordinated Universal Time) timezone.

The following figure shows the structure of the NES database, including the relationship between each schema, the primary keys, and foreign keys.

Figure 1. NES Database Structure

adm and nub Schemas

Transactional tables that contain the current record of the information for each Connected Worker Platform component.

Table 1. adm and nub Schemas
Table Name Purpose
adm.ApplicationSettings Contains a entry for each NES policy and the values that are currently assigned to each settings in the policy.
adm.AuditColumnValue Legacy table.
adm.AuditKeyEvent Legacy table.
nub.ExternalAuthenticator Contains an entry for Nymi Band that contains an external authenticator.
nub.NymiBand Contains current information about each Nymi Band that has been enrolled on the NES server.
nub.PrivateKeyStore Contains a entry for each private key that is stored in the Microsoft keystore.
nub.UserCore Contains an entry for each user and the current value of each user property.
nub.UserOtp Contains a entry for each private key that is stored in the Microsoft keystore.

dbo.__MigrationHistory

Transactional table that stores information about SQL database migrations that occur during an NES upgrade.

lku Schema

Lookup tables that contain a list of acceptable values settings that appear in the adm.ApplicationSettings table, and are selected by an NES Administrator in the properties page of the policy in the NES Administrator Console.

Table 2. lku schema
Table Name Purpose
lku.AuditEventsOfInterest Legacy option.
lku.EnrollmentDestination Contains a list of acceptable values for the Enrollment Destintation setting.
lku.OtpSubject Legacy option.
lku.Requirement Contains a list of acceptable values for the NfcUIDCapture setting.
lku.AuditLogoutTimeout Contains a list of acceptable values for the Auto Logout Timeout setting.
lku.EventType Contains a list of acceptable values for events.

xrf.UserOtp Schema

Legacy transactional table that contains information about each OTP that is created for a user.

audit Schemas

Log tables that record each event that occurs as a result of a change in a transaction table. The audit schemas contain the same columns as each corresponding transactional table as well as 4 additional columns that identify the time of the event, the type of event, the system user, and the schema entry identifier. Stores information about changes (creation, updates and deletions) that result in changes to the nub and adm table objects. These changes are tracked as events. There is one row for each event type and a single change can results in several recorded events types. Accessing the data in the audit tables enables users to gather useful information for audit and compliance purposes. The following sections provide detailed information about the contents of each audit table.

audit.Events SQL Schema

This table contains enrollment information that pertains to NES users. Each attribute name that is listed in the Column Name is prefaced with Identity. For example, identity.EventTime.

Table 3. audit.Events SQL Schema
Column Name Description
EventTypeID ID that denotes the type of event. There are several types of events:
  • 1—The Nymi Band used to perform the first tap during the enrollment process is not the same Nymi Band that was used to perform the second tap during the enrollment process.
  • 2—The username or password that was provided to log into the Nymi Band Application was not correct.
  • 3—Enrollment completed.
  • 4—The Nymi Band that the user used to perform the tap operation is assigned to a different user.
  • 5—The False Acceptance Rate value of 1:50000K was applied to the Nymi Band of the user.
  • 6—Presence Authentication Code (PAC) Verification Failed for the Nymi Band of the user.
  • 7—PAC verification succeeded but the Nymi Band has lost track of time.
  • 8—The system encountered an error during PAC verification.
  • 9—PAC verification initialization failed as a result of a bad request.
  • 10—PAC verification initialization failed because the advertising key was not found.
  • 11—Could not generate the advertising key because of an internal server error.
  • 12—Could not generate the advertising key because the CMAC that was supplied does not match the CMAC that was created.
  • 13—PAC verification succeeded
  • 14—Time on the Nymi Band is out of bounds with the time on the NES server.
  • 15—Time on the Nymi Band Application is out of bounds with the time on the NES server.
  • 16—Re-enrollment attempt failed because the SEOS-enabled Nymi Band is assigned to a different user.
  • 17—Re-enrollment attempt failed because the Nymi Band is assigned to a different user and the policy settings only allow re-enrollment to the same Nymi Band.
  • 18—Re-enrollment attempt to the Nymi Band of a different user succeeded.
  • 19—Re-enrollment attempt by a user for their own Nymi Bandsucceeded.
  • 21—Re-enrollment attempt failed because the Nymi Band is assigned to a different user and the NES policy settings only allow re-enrollment to the same Nymi Band.
  • 22—Re-enrollment attempt failed for the because NES policy does not allow re-enrollment.
EventTypeID (continued)
  • 23—Nymi Band registration attempt succeeded.
  • 24—Nymi Band registration attempt failed.
UserCoreID ID of the user that is associated with the Nymi Band, as it appears in the audit.UserCore table. When an NES Administrator disassociates a Nymi Band from a user in the NES Administrator Console, the UserCoreId value is as NULL for the associated Update and Delete Event Type entries in the table.
Username Active Directory account that logged in to the Nymi Band Application to perform the enrollment.
InitialTapNymiBandId The NFC UID for the first Nymi Band tap in the Nymi Band Application.
ConfirmTapNymiBandId The NFC UID for the second Nymi Band tap in the Nymi Band Application.
CreatedAt Date and time that the object entry was created in the table.
ModifiedAt Date and time that the object entry was modified in the table.
ModifiedBy User account that modified the object entry in the table. For example, when the user performs an enrollment, the AD user account for the user appears.
DisconnectUserCoreId The ID of the previously assigned Nymi Band user.
HardwareID The serial number of the Nymi Band.
audit.UserCore SQL Schema

This table contains information that pertains to NES users. Each attribute name that is listed in the Column Name is prefaced with Identity. For example, identity.EventTime.

Table 4. audit.UserCore SQL Schema
Column Name Description
Identity Unique identifier for the schema entry.
EventTime Date and time associated with the event that is defined by EventType.
EventType Type of event, denoted by a single character. There are three event types:
  • C—when the user is enrolled or for an unenrolled user, the first time that an NES Administrator performs a search for the user in the NES Administrator Console.
  • U—when the properties of the user is updated.
SystemUser Account that is specified as the Application Pool Identity for the NES application pool.
ID ID of the user in the audit.UserCore table.
Domain Domain of the user.
Username Login name of the user.
MiscNote Displays the value that appears in the Notes field in the properties of the user account. Values that can appear:
  • NULL when the Notes field is empty. For example, when the user entry was initially created in the database as a result of an enrollment, or when an NES ADminsitrator removes the text that appears in the Notes field.
  • Text specified by the NES Administrator in the Notes field for the properties of the Nymi Band in the NES Administrator Console.
  • The value Created from an AD search result, which is the text that appears in the Notes field when the user entry is created in the database as a results of an NES Administrator searching for a user in the NES Administrator Console for which a Nymi Band enrollment has never occurred.
CreatedAt Date and time that the object entry was created in the table.
ModifiedAt Date and time that the object entry was modified in the table.
ModifiedBy User account that modified the object entry in the table. For example, when the user performs an enrollment, the AD user account for the user appears. When an NES Administrator modifies the Notes field for the properties of the user in the NES Administrator Console, then the AD user account for the NES Administrator appears.
audit.NymiBand SQL Schema

This table contains audit log data pertaining to Nymi Band events. Each attribute name that is listed in the Column Name is prefaced with Identity.

Table 5. audit.NymiBand SQL Schema
Column Name Description
Identity Unique identifier for the schema entry.
EventTime Date and time associated with the event that is defined by EventType.
EventType Type of event, denoted by a single character. There are three event types:
  • C—when the Nymi Band is enrolled.
  • U—when the properties of the Nymi Band is updated.
  • D—when the Nymi Band to user association is deleted.
SystemUser Account that is specified as the Application Pool Identity for the NES application pool.
ID ID of the Nymi Band in the audit.NymiBand table.
UserCoreId ID of the user that is associated with the Nymi Band, as it appears in the audit.UserCore table. When an NES Administrator disassociates a Nymi Band from a user in the NES Administrator Console, the UserCoreId value is as NULL for the associated Update and Delete Event Type entries in the table.
NymiBandID MAC address of the Nymi Band. The NymiBandID is randomly generated at the time of enrollment, and changes on re-enrollment.
NfcUID NFC address of the Nymi Band.
AuthorisationID N/A. The value appears as NULL.
HardwareID Nymi Band serial number.
SymmetricKeyID SymmetricKey ID that was created on the Nymi Band. Values that can appear:
  • An encrypted key sequence when Corporate Credentials Authenticator is enabled in the policy or the Enrollment Destination is set to NES and Evidian.
  • NULL when in the policy the Corporate Credentials Authenticator is not enabled and the Enrollment Destination value is NES only at the time of enrollment.
EncryptionIV Encryption Initialization Vector that is used to support encrypting the password for a user. A value appears in this field when the Nymi Lock Control option is enabled in the default policy at the time that user enrolled the Nymi Band.
EncryptedPassword Encrypted password for a user. A value appears in this field when the Nymi Lock Control option is enabled in the default policy at the time that the user enrolled the Nymi Band.
IsActive Status of the Nymi Band as set in the NES Administrator Console. Values that can appear:
  • 0 when the Nymi Band inactive.
  • 1 when the Nymi Band is active.
IsPrimary Status of the Nymi Band as set in the NES Administrator Console. Values that can appear:
  • 0 when the Nymi Band not the primary Nymi Band.
  • 1 when the Nymi Band is primary.
HasFingerprint Status of the fingerprint enrollment for the Nymi Band. Values that can appear:
  • 0 when a fingerprint enrollment has completed.
  • 1 when a fingerprint enrollment has not been completed.
EnrollmentStatus N/A. The value appears as NULL.
MiscNote Displays the value that appears in the Notes field in the properties of the Nymi Band.
BandSubordinateCaCert N/A. The value appears as NULL.
BandCert N/A. The value appears as NULL.
UserCert N/A. The value appears as NULL.
BandLabel The Band Label name given to the Nymi Band during enrollment, when the Display Band Label on Nymi Bands option is enabled. The value is NULL when the Display Band Label on Nymi Bands option was disabled at the time of enrollment.
FirmwareVersion Firmware version on the Nymi Band at time of enrollment.
CreatedAt Date and time that the object entry was created in the table.
ModifiedAt Date and time when the object entry was modified in the table.
ModifiedBy The user who modified the object.
EvidianEnrollmentCompleted Status of the enrollment of Nymi Band on an Evidian EAM Controller. Values that can appear:
  • 0 when enrollment completed.
  • 1 when enrollment did not complete or occur.
IsSynced Status of application of the latest individual user policy settings to the associate user. Values that can appear:
  • 0 when the properties of a user do not include changes in the settings of an applicable individual user policy. The user must log into the Nymi Band Application while wearing their authenticated Nymi Band to receive updated individual policy settings.
  • 1 when the properties of a user includes the current settings of an applicable individual user policy.
IndividualUserPolicyId ID of the individual user policy that is assigned to the user or NULL if the user does not have an assigned individual user policy.
SeosId SEOS ID of the Nymi Band, which Nymi assigns to the Nymi Band during the manufacturing process or NULL if the Nymi Band is not SEOS-enabled.
StaticMacAddress Default Nymi Band ID, which Nymi assigns to the Nymi Band during manufacturing.
TSetTime Time when the Nymi Band Application performed the set_time operation on the Nymi Band, during the enrollment process or when the user logged into the Nymi Band Application. Authenticated Bluetooth taps use this time during the process of validating the user that is associated with the Nymi Band.
IsBandEnrolledHere Status of the Nymi Band enrollment on this NES. Values that can appear:
  • 0—The user completed the Nymi Band enrollment on another NES and registered the Nymi Band on this NES.
  • 1—The user completed the Nymi Band enrollment on this NES.
audit.ApplicationsSetting SQL Schema

This table contains audit log data pertaining to NES application settings that are defined in the each NES policy. Each attribute name that is listed in the Column Name is prefaced with Identity.

Table 6. audit.ApplicationsSetting SQL Schema
Column Name Description
Identity Unique identifier for the schema entry.
EventTime Date and time associated with the event that is defined by EventType.
EventType Type of event, denoted by a single character. There are three event types:
  • C—when a new policy is created or change to an existing policy is created.
  • U—when a setting in a policy is modified.
  • D—when a policy is deleted.
SystemUser Account that is specified as the Application Pool Identity for the NES application pool.
ID The database ID of application settings on audit.ApplicationSettings table.
IsActive Status of the policy as set in the NES Administrator Console. Values that can appear:
  • 0 when the policy is not the active policy.
  • 1 when the policy is the active policy.
Description Name of the policy that contains the setting.
AutoLogoutTimeoutSeconds Length of time after which the Nymi Band Application automatically disconnects an idle user.
NfcUIDCaptureRequirement Status of the requirement to capture the NFC UID of the Nymi Band during enrollment. The value is always M (Mandatory).
FingerprintRequirement Legacy option that defines the status of the requirement to capture the fingerprint of the user during enrollment. The value is always M (Mandatory).
PassworthAuthOption Status of the option to allow authentication by corporate credentials. Values that can appear:
  • 0 when the setting is disabled.
  • 1 when the setting is enabled.
FingerprintOption Legacy option that defines the status of the fingerprint capture option. The value is always 1 (enabled).
LockControlSupportOption Status of the option to allow Nymi Lock Control. Values that can appear:
  • 0—when the setting is disabled.
  • 1—when the setting is enabled.
DoorSecurityOption N/A
AdCheckUserStatus Status of the Check User Status setting. Values that can appear:
  • 0—when the setting is disabled.
  • 1—when the setting is enabled.
AdCacheUserStatus Status of the Cache User Status setting. Values that can appear:
  • 0—when the setting is disabled.
  • 1—when the setting is enabled.
AdCacheExpiryTimeSeconds Expiry time of user status cache in seconds. When the Cache User Status setting is disabled, NULL appears.
ManualOtpOption Legacy option.
ManualNeaOtpOption Legacy option.
LockWhenAway Status of the Lock When Away setting for Nymi Lock Control. Values that can appear:
  • 0 when the setting is disabled.
  • 1 when the setting is enabled.
MonitorProximity Legacy option.
KeepUnlockedWhenPresent Status of the Keep Unlocked When Present setting for Nymi Lock Control. Values that can appear:
  • 0 when the setting is disabled.
  • 1 when the setting is enabled.
CheckProximityForUnlock Legacy option.
LockProximitySphera Proximity distance for Nymi Lock Control that is defined in the adm.ApplicationSettings table. Nymi recommends that you leave the default value of 3.
UnlockProximitySphera Proximity distance for Nymi Lock Control that is defined in the adm.ApplicationSettings table. Nymi recommends that you leave the default value of 2.
ProximityLockCountdown Starting time for the countdown timer in seconds, that Nymi Lock Control displays to the user when the Nymi Band moves out of close proximity to the Bluetooth adapter.
BandLabelOnBandEnabled Status of the Display Band Label on Nymi Bands setting. Values that can appear:
  • 0 when the setting is disabled.
  • 1 when the setting is enabled.
BandLabelOnBandCustomizationEnabled Status of the Allow Band Label Customization setting. Values that can appear:
  • 0 when the setting is disabled.
  • 1 when the setting is enabled.
CreatedAt Date and time that the object entry was created in the table.
ModifiedAt Date and time when the object entry was modified in the table.
ModifiedBy User who modified the object entry in the table.
EnrollmentDestination Status of the Enrollment Destination setting. Values that can appear:
  • 1 when enrollment data is sent to NES only.
  • 2 when enrollment data is sent to NES and Evidian.
SDCTEnabled Legacy option.
SDRemindersEnabled Legacy option.
UnlockWhenPresent Status of the Unlock When Present setting for Nymi Lock Control. Values that can appear:
  • 0 when the setting is disabled.
  • 1 when the setting is enabled.
LivenessDetectionEnabled Status of the LivenessDetectionEnabled setting. Values that can appear:
  • NULL when the setting was not available at the time that the entry was created.
  • 0 when the setting is disabled.
  • 1 when the setting is enabled.
HealthAttestation Legacy option.
TemperatureReporting Legacy option.
SeosTimeout Legacy option.
HapticFeedbackOnBandEnabled Status of the HapticFeedbackonBandEnabled setting. Values that can appear:
  • NULL when the setting was not available at the time that the entry was created.
  • 0 when the setting is disabled.
  • 1 when the setting is enabled.
FAR Status of the FAR setting. Values that can appear:
  • 0 when the setting is 1:20,000 (default).
  • 1 when the setting is 1:50,000.
TInterval Used for authenticated Bluetooth tap operations. The time interval for PAC message rotation. Default: 1 second
DmNBANESTime Used for authenticated Bluetooth tap operations. Midpoint time difference between the Nymi Band Application and NES. For a domain-joined enrollment terminal this time is the estimated time accuracy inherent in the Network Time Protocol (NTP, IETF RFC 1305) used by Windows to synchronize time between computers.
DvNBANESTime Used for authenticated Bluetooth tap operations. Variation time difference between the Nymi Band Application and NES. For a domain-joined enrollment terminal this time is the estimated time accuracy inherent in the Network Time Protocol (NTP, IETF RFC 1305) used by Windows to synchronize time between computers.
DmNBANBTime Used during PAC verification for authenticated Bluetooth tap operations. Midpoint time difference between the Nymi Band and the Nymi Band Application, which results from the inherent delay in sending the set_time message to the Nymi Band.
DvNBANBTime Used during PAC verification for authenticated Bluetooth tap operations. Variation time difference between the Nymi Band and the Nymi Band Application, which results from the inherent delay in sending the set_time message to the Nymi Band.
DmClockDrift Used during PAC verification for authenticated Bluetooth tap operations. Clock drift value for the Nymi Band.
DvClockDrift Used during PAC verification for authenticated Bluetooth tap operations. Clock drift value for the Nymi Band.
DmIntent Intent delay time that is applied to PAC verification activities for authenticated Bluetooth tap operations.
DvIntent Intent delay time that is applied to PAC verification activities for authenticated Bluetooth tap operations.
AllowReEnrollment Status of the AllowReEnrollment setting. Values that can appear:
  • 0 when the user cannot perform re-enrollment of their Nymi Band until an CWP Administrator disassociates the Nymi Band for the user.
  • 1 when the user can perform re-enrollment of their Nymi Band without the need for the CWP Administrator to first disassociate the Nymi Band for the user.
AllowReenrollementOfActiveNymiBand Status of the AllowReenrollementOfActiveNymiBand setting. Values that can appear:
  • 0 when the user cannot perform an enrollment of a Nymi Band that is associated with another user until an CWP Administrator disassociates the Nymi Band for the user.
  • 1 when the user can perform an enrollment of a Nymi Band that is associated with another user without the need for the CWP Administrator to first disassociate the Nymi Band for the user.
audit.ExternalAuthenticator SQL Schema

This table contains audit log data pertaining to external user authentication events. Each attribute name that is listed in the Column Name is prefaced with Identity.

Table 7. audit.ExternalAuthenticator SQL Schema
Column Name Description
Identity Unique identifier for the schema entry.
EventTime Date and time associated with the event that is defined by EventType.
EventType Type of event, denoted by a single character. There are three event types:
  • C—when the external authenticator is created on the Nymi Band.
  • U—when the properties of external authenticator on the Nymi Band is updated.
  • D—when external authenticator is deleted on the Nymi Band.
SystemUser Account that is specified as the Application Pool Identity for the NES application pool.
ID ID of the object entry in the audit.ExternalAuthenticator table.
PublicKey Base-64 pem encoded public key on the Nymi Band.
BandExternalAuthenticatorid ID of the external authenticator.
NymiBandId ID of the associated Nymi Band in the audit.NymiBand table.
Name Name of the application that created the External Authenticator. Values that can appear:
  • NEM—Nymi Band Application, when the Corporate Credentials Authenticator setting is enabled in the policy and the Enrollment Destination setting is set to NES only.
  • Evidian—EAM controller when the Enrollment Destination setting is set to NES and Evidian.
MiscNote Additional information.
CreatedAt Date and time that the object entry was created in the table.
PrivateKeyWO N/A.
PrivateKeyStoreID UUID and the key ID of the private key in the Microsoft keystore.
HapticFeedbackonBandEnabled Status of the HapticFeedbackonBandEnabled setting. Values that can appear:
  • 0 when the setting is disabled.
  • 1 when the setting is enabled.
ModifiedAt Date and time when the object was modified.
ModifiedBy The user who modified the object, which is the account that was logged into the Nymi Band Application at the time the external authenticator was created or removed on the Nymi Band.
audit.Certificate SQL Schema

Stores information about all the NEA certificate creation events, when a certificate is issued to the Nymi Band Application and all other NEAs. Each attribute name that is listed in the Column Name is prefaced with Identity.

Table 8. audit.Certificate SQL Schema
Column Name Description
ID Unique identifier for the schema entry.
NotBefore Date before which the certificate is not valid.
NotAfter Date after which the certificate is not valid.
SerialNumber Serial number of the certificate.
RequesterTime Date and time that the application requested the certificate.
RequesterDomain Domain of the user that was logged into the application at the time of the certificate request.
RequesterUserName User name of the user that was logged into the application at the time of the certificate request.
RequesterIp IP address of the machine from which the request originated.
audit.IndividualUserPolicy Schema

Stores information about all the configured individual user policies. Each attribute name that is listed in the Column Name is prefaced with Identity.

Table 9. audit.IndividualUserPolicy Schema
Column Name Description
Identity Unique identifier for the schema entry.
EventTime Date and time associated with the event that is defined by EventType.
EventType Type of event, denoted by a single character. There are three event types:
  • C—when the external authenticator is created on the Nymi Band.
  • U—when the properties of external authenticator on the Nymi Band is updated.
  • D—when external authenticator is deleted on the Nymi Band.
SystemUser Account that is specified as the Application Pool Identity for the NES application pool.
ID ID of the object entry in the audit.ExternalAuthenticator table.
IndividualUserPolicyTypeID ID that denotes the type of the Individual User Policy. There are two types:
  • 1—Predefined user policy
  • 2-User—created user policy.
Name Name that the policy creator assigned to the individual policy.
Note Text that appears in the Note field for the individual policy.
LivenessDetectionEnabled Status of the LivenessDetectionEnabled setting. Values that can appear:
  • NULL when the setting was not available at the time that the entry was created.
  • 0 when the setting is disabled.
  • 1 when the setting is enabled.
PassworthAuthOption Status of the option to allow authentication by corporate credentials. Values that can appear:
  • 0 when the setting is disabled.
  • 1 when the setting is enabled.
CreatedAt Date and time that the object entry was created in the table.
ModifiedAt Date and time when the object was modified.
ModifiedBy The user who modified the object, which is the account that was logged into the Nymi Band Application at the time the external authenticator was created or removed on the Nymi Band.
HapticFeedbackonBandEnabled Status of the HapticFeedbackonBandEnabled setting. Values that can appear:
  • 0 when the setting is disabled.
  • 1 when the setting is enabled.
FAR Status of the FAR setting. Values that can appear:
  • 0 when the setting is 1:20,000 (default).
  • 1 when the setting is 1:50,000.
AllowReEnrollment Status of the AllowReEnrollment setting. Values that can appear:
  • 0 when the user cannot perform re-enrollment of their Nymi Band until an CWP Administrator disassociates the Nymi Band for the user.
  • 1 when the user can perform re-enrollment of their Nymi Band without the need for the CWP Administrator to first disassociate the Nymi Band for the user.
AllowReenrollementOfActiveNymiBand Status of the AllowReenrollementOfActiveNymiBand setting. Values that can appear:
  • 0 when the user cannot perform an enrollment of a Nymi Band that is associated with another user until an CWP Administrator disassociates the Nymi Band for the user.
  • 1 when the user can perform an enrollment of a Nymi Band that is associated with another user without the need for the CWP Administrator to first disassociate the Nymi Band for the user.
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.