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.

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.EnrollmentEventType Contains a list of acceptable values for enrollment 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.EnrollmentEvent 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.EnrollmentEvent SQL Schema
Column Name Description
EnrollmentEventTypeID ID that denotes the type of enrollment event. There are 4 types of enrollment 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 was used to perform the tap operation is assigned to a different user.
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.
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.
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 the Nymi Band is enrolled.
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 Nymi Band is enrolled.
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 EAM Controller. Values that can appear:
  • 0 when enrollment completed.
  • 1 when enrollment did not complete or occur.
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 and the NES Administrator Console 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 BLE 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.
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.
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.