Adding indices to old Identify instance


The IdentifyAudit database can contain a large amount of data and having neccessary indices is essential for querying data efficiently. One feature that needs to query log data all the time is the log viewers. However, Identify instances before version 5.1.1 did not have all the necessary indices. From version 5.1.1, we began adding indices for newly created Identify instances. Because adding indices to tables that have much data can take so much time (sometimes it takes hours to do so), having the Configurator to add indices during an upgrade process is not a viable option. Instead, we opted to provide scripts that our customers can run manually if they have the needs related to log querying.

The indices that we have added throughout the years are:

Table Index name Added since version Added at upgrading
AuditEvent Idx_AuditEvent_UtcTimestamp_Id_EventType 5.1.1 No
AuditAdminSiteAuthentication Idx_AuditAdminSiteAuthentication_AuditEventId 5.1.1 No
AuditAttributeServiceConnection Idx_AuditAttributeServiceConnection_AuditEventId 5.1.1 No
AuditAuthenticationConnection Idx_AuditAuthenticationConnection_AuditEventId 5.1.1 No
AuditAuthenticationContextMethodClass Idx_AuditAuthenticationContextMethodClass_AuditEventId 5.1.1 No
AuditClaimDefinition Idx_AuditClaimDefinition_AuditEventId 5.1.1 No
AuditClaimSet Idx_AuditClaimSet_AuditEventId 5.1.1 No
AuditClaimsIssuance Idx_AuditClaimsIssuance_AuditEventId 5.1.1 No
AuditClaimTransformation Idx_AuditClaimTransformation_AuditEventId 5.1.1 No
AuditCorrelationError Idx_AuditCorrelationError_AuditEventId 5.1.1 No
AuditDataStore Idx_AuditDataStore_AuditEventId 5.1.1 No
AuditDiscreteClaimValueSpace Idx_AuditDiscreteClaimValueSpace_AuditEventId 5.1.1 No
AuditGroup Idx_AuditGroup_AuditEventId 5.1.1 No
AuditIdentityProviderConfiguration Idx_AuditIdentityProviderConfiguration_AuditEventId 5.1.1 No
AuditIncomingAssertion Idx_AuditIncomingAssertion_AuditEventId 5.1.1 No
AuditIssuedArtifact Idx_AuditIssuedArtifact_AuditEventId 5.1.1 No
AuditIssuedClaim Idx_AuditIssuedClaim-ClaimsIssuanceId 5.1.1 No
AuditLdapAttributeDefinition Idx_AuditLdapAttributeDefinition_AuditEventId 5.1.1 No
AuditMassUpdateUserClaimValue Idx_AuditMassUpdateUserClaimValue_AuditEventId 5.1.1 No
AuditOAuthAccessToken Idx_AuditOAuthAccessToken_AuditEventId 5.1.1 No
AuditOrganization Idx_AuditOrganization_AuditEventId 5.1.1 No
AuditPersistentPseudonym Idx_AuditPersistentPseudonym_AuditEventId 5.1.1 No
AuditPlugIn Idx_AuditPlugIn_AuditEventId 5.1.1 No
AuditProtocolConnection Idx_AuditProtocolConnection_AuditEventId 5.1.1 No
AuditSharedConfigurableSetting Idx_AuditSharedConfigurableSetting_AuditEventId 5.1.1 No
AuditTombstone Idx_AuditTombstone_AuditEventId 5.1.1 No
AuditUser Idx_AuditUser_AuditEventId 5.1.1 No
AuditUserClaim Idx_AuditUserClaim-UserId 5.1.1 No
AuditUserDeviceCookie Idx_AuditUserDeviceCookie_AuditEventId 5.1.1 No
AuditUserRequest Idx_AuditUserRequest_AuditEventId 5.1.1 No
Logs IX_Logs__UserId 5.3 Yes
AuditConnectionClaimSets IX_AuditConnectionClaimSets__ConnectionId 5.3 Yes
AuditConnectionUserConsent IX_AuditConnectionUserConsent__ConnectionId 5.3 Yes
AuditConnectionUserConsent IX_AuditConnectionUserConsent__UserId 5.3 Yes
AuditConnectionUserConsent IX_AuditConnectionUserConsent__NonUserUniqueKey 5.3 Yes
Logs Idx_Logs_Timestamp_LogLevel_LogType_EventId_Id 5.11 Yes
Logs IX_Logs__EventId 5.12 Yes
Logs IX_Logs__RequestId 5.12 Yes
Logs IXDLogs_TimeStamp__Type__LogLevel 5.14 Yes
AuditUserSecondFactorCode IXD_AuditUserSecondFactorCode__AuditEventId 5.14 No
AuditUserSecondFactorCode IX_AuditUserSecondFactorCode__UserId 5.14 No
AuditUserSecondFactorCode IX_AuditUserSecondFactorCode__OtpConnectionId 5.14 No
AuditUserRestrictedActionAttempt IXD_AuditUserRestrictedActionAttempt__AuditEventId 5.14 No
AuditUserRestrictedActionAttempt IXD_AuditUserRestrictedActionAttempt__AuthenticationConnectionId_UserId__ActionType 5.14 No
AuditNonUserRestrictedActionAttempt IXD_AuditNonUserRestrictedActionAttempt__AuditEventId 5.14 No
AuditNonUserRestrictedActionAttempt IXDAuditNonUserRestrictedActionAttempt_AuthenticationConnectionId__NonUserUniqueKey__ActionType 5.14 No
AuditDeviceAuthorizationResponse IXD_AuditDeviceAuthorizationResponse__AuditEventId 5.14 No
AuditCryptoKey IXD_AuditCryptoKey__AuditEventId 5.14 No

How to read the table above:

  • If your instance was created before version 5.1.1 and is upgraded to version 5.14, it will not have all indices that have "Added at upgrading" marked as "No".
  • If your instance was created using version 5.1.1 and is upgraded to version 5.14, it will have all indices added to version 5.1.1 but will not have indices that were added after 5.1.1 and were not added automatically at upgrading time.

Note: As you can see from the table above, the rule about when an index is added by the Configurator automatically is inconsistent. We will improve index handling in a future version.

Some features that need to query logs are:

  • Audit log viewers
  • System log viewer
  • Background log cleanup jobs
  • REST API endpoints that are used to get log data

Add missing indices to Identify instance

If you need to use any of the features that need to query log data or need to clean up old log data using SQL script, and you find that querying log data is just too slow, it could be that some indices are missing. You can create them using the following steps:

  1. Download SQL script - indices.

  2. Replace all [#SCHEMA] with your Identify schema name.

  3. Execute the script against the IdentifyAudit database, e.g. using SQL Server Management Studio (SSMS).