Adding indices to old Identify instance
Introduction
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 | New name since 5.15 |
|---|---|---|---|---|
| AuditEvent | Idx_AuditEvent_UtcTimestamp_Id_EventType | 5.1.1 | No | IX_AuditEvent__UtcTimestamp_Id_EventType |
| AuditAdminSiteAuthentication | Idx_AuditAdminSiteAuthentication_AuditEventId | 5.1.1 | No | IX_AuditAdminSiteAuthentication__AuditEventId |
| AuditAttributeServiceConnection | Idx_AuditAttributeServiceConnection_AuditEventId | 5.1.1 | No | IX_AuditAttributeServiceConnection__AuditEventId |
| AuditAuthenticationConnection | Idx_AuditAuthenticationConnection_AuditEventId | 5.1.1 | No | IX_AuditAuthenticationConnection__AuditEventId |
| AuditAuthenticationContextMethodClass | Idx_AuditAuthenticationContextMethodClass_AuditEventId | 5.1.1 | No | IX_AuditAuthenticationContextMethodClass__AuditEventId |
| AuditClaimDefinition | Idx_AuditClaimDefinition_AuditEventId | 5.1.1 | No | IX_AuditClaimDefinition__AuditEventId |
| AuditClaimSet | Idx_AuditClaimSet_AuditEventId | 5.1.1 | No | IX_AuditClaimSet__AuditEventId |
| AuditClaimsIssuance | Idx_AuditClaimsIssuance_AuditEventId | 5.1.1 | No | IX_AuditClaimsIssuance__AuditEventId |
| AuditClaimTransformation | Idx_AuditClaimTransformation_AuditEventId | 5.1.1 | No | IX_AuditClaimTransformation__AuditEventId |
| AuditCorrelationError | Idx_AuditCorrelationError_AuditEventId | 5.1.1 | No | IX_AuditCorrelationError__AuditEventId |
| AuditDataStore | Idx_AuditDataStore_AuditEventId | 5.1.1 | No | IX_AuditDataStore__AuditEventId |
| AuditDiscreteClaimValueSpace | Idx_AuditDiscreteClaimValueSpace_AuditEventId | 5.1.1 | No | IX_AuditDiscreteClaimValueSpace__AuditEventId |
| AuditGroup | Idx_AuditGroup_AuditEventId | 5.1.1 | No | IX_AuditGroup__AuditEventId |
| AuditIdentityProviderConfiguration | Idx_AuditIdentityProviderConfiguration_AuditEventId | 5.1.1 | No | IX_AuditIdentityProviderConfiguration__AuditEventId |
| AuditIncomingAssertion | Idx_AuditIncomingAssertion_AuditEventId | 5.1.1 | No | IX_AuditIncomingAssertion__AuditEventId |
| AuditIssuedArtifact | Idx_AuditIssuedArtifact_AuditEventId | 5.1.1 | No | IX_AuditIssuedArtifact__AuditEventId |
| AuditIssuedClaim | Idx_AuditIssuedClaim-ClaimsIssuanceId | 5.1.1 | No | IX_AuditIssuedClaim__ClaimsIssuanceId |
| AuditLdapAttributeDefinition | Idx_AuditLdapAttributeDefinition_AuditEventId | 5.1.1 | No | IX_AuditLdapAttributeDefinition__AuditEventId |
| AuditMassUpdateUserClaimValue | Idx_AuditMassUpdateUserClaimValue_AuditEventId | 5.1.1 | No | IX_AuditMassUpdateUserClaimValue__AuditEventId |
| AuditOAuthAccessToken | Idx_AuditOAuthAccessToken_AuditEventId | 5.1.1 | No | IX_AuditOAuthAccessToken__AuditEventId |
| AuditOrganization | Idx_AuditOrganization_AuditEventId | 5.1.1 | No | IX_AuditOrganization__AuditEventId |
| AuditPersistentPseudonym | Idx_AuditPersistentPseudonym_AuditEventId | 5.1.1 | No | IX_AuditPersistentPseudonym__AuditEventId |
| AuditPlugIn | Idx_AuditPlugIn_AuditEventId | 5.1.1 | No | IX_AuditPlugIn__AuditEventId |
| AuditProtocolConnection | Idx_AuditProtocolConnection_AuditEventId | 5.1.1 | No | IX_AuditProtocolConnection__AuditEventId |
| AuditSharedConfigurableSetting | Idx_AuditSharedConfigurableSetting_AuditEventId | 5.1.1 | No | IX_AuditSharedConfigurableSetting__AuditEventId |
| AuditTombstone | Idx_AuditTombstone_AuditEventId | 5.1.1 | No | IX_AuditTombstone__AuditEventId |
| AuditUser | Idx_AuditUser_AuditEventId | 5.1.1 | No | IX_AuditUser__AuditEventId |
| AuditUserClaim | Idx_AuditUserClaim-UserId | 5.1.1 | No | IX_AuditUserClaim__UserId |
| AuditUserDeviceCookie | Idx_AuditUserDeviceCookie_AuditEventId | 5.1.1 | No | IX_AuditUserDeviceCookie__AuditEventId |
| AuditUserRequest | Idx_AuditUserRequest_AuditEventId | 5.1.1 | No | IX_AuditUserRequest__AuditEventId |
| Logs | IX_Logs__UserId | 5.3 | Yes | Unchanged |
| AuditConnectionClaimSets | IX_AuditConnectionClaimSets__ConnectionId | 5.3 | Yes | Unchanged |
| AuditConnectionUserConsent | IX_AuditConnectionUserConsent__ConnectionId | 5.3 | Yes | Unchanged |
| AuditConnectionUserConsent | IX_AuditConnectionUserConsent__UserId | 5.3 | Yes | Unchanged |
| AuditConnectionUserConsent | IX_AuditConnectionUserConsent__NonUserUniqueKey | 5.3 | Yes | Unchanged |
| Logs | Idx_Logs_Timestamp_LogLevel_LogType_EventId_Id | 5.11 | Yes | IX_Logs__Timestamp_LogLevel_LogType_EventId_Id |
| Logs | IX_Logs__EventId | 5.12 | Yes | Unchanged |
| Logs | IX_Logs__RequestId | 5.12 | Yes | Unchanged |
| Logs | IXD_Logs__TimeStamp__Type__LogLevel | 5.14 | Yes | IX_Logs__TimeStamp_Type_LogLevel |
| AuditUserSecondFactorCode | IXD_AuditUserSecondFactorCode__AuditEventId | 5.14 | No | IX_AuditUserSecondFactorCode__AuditEventId |
| AuditUserSecondFactorCode | IX_AuditUserSecondFactorCode__UserId | 5.14 | No | Unchanged |
| AuditUserSecondFactorCode | IX_AuditUserSecondFactorCode__OtpConnectionId | 5.14 | No | Unchanged |
| AuditUserRestrictedActionAttempt | IXD_AuditUserRestrictedActionAttempt__AuditEventId | 5.14 | No | IX_AuditUserRestrictedActionAttempt__AuditEventId |
| AuditUserRestrictedActionAttempt | IXD_AuditUserRestrictedActionAttempt__AuthenticationConnectionId_UserId__ActionType | 5.14 | No | IX_AuditUserRestrictedActionAttempt__AuthenticationConnectionId_UserId_ActionType |
| AuditNonUserRestrictedActionAttempt | IXD_AuditNonUserRestrictedActionAttempt__AuditEventId | 5.14 | No | IX_AuditNonUserRestrictedActionAttempt__AuditEventId |
| AuditNonUserRestrictedActionAttempt | IXD_AuditNonUserRestrictedActionAttempt__AuthenticationConnectionId__NonUserUniqueKey__ActionType | 5.14 | No | IX_AuditNonUserRestrictedActionAttempt__AuthenticationConnectionId_NonUserUniqueKey_ActionType |
| AuditDeviceAuthorizationResponse | IXD_AuditDeviceAuthorizationResponse__AuditEventId | 5.14 | No | IX_AuditDeviceAuthorizationResponse__AuditEventId |
| AuditCryptoKey | IXD_AuditCryptoKey__AuditEventId | 5.14 | No | IX_AuditCryptoKey__AuditEventId |
| AuditDeviceAuthorizationResponse | IX_AuditDeviceAuthorizationResponse__UserCode | 5.15 | No | Unchanged |
| AuditDeviceAuthorizationResponse | IX_AuditDeviceAuthorizationResponse__DeviceCode | 5.15 | No | Unchanged |
| AuditOAuthAccessToken | IX_AuditOAuthAccessToken__ExpirationDate_RefreshTokenExpirationDate | 5.15 | Yes | Unchanged |
How to read the table above:
- If your instance was created before version 5.1.1 and is upgraded to version 5.15, 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.15, 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 whose current version is 5.14 or lower
If you need to use any of the features that require querying log data or need to clean up old log data using SQL scripts, and you find that querying log data is running too slowly, it could be due to missing indices. You can create them using the following steps on your Identify instance whose current version is 5.14 or lower:
Download SQL script - indices.
Replace all [#SCHEMA] with your Identify schema name.
Execute the script against the IdentifyAudit database, e.g. using SQL Server Management Studio (SSMS).
Add missing indices to Identify instance whose current version is 5.15 or above
If you need to use any of the features that require querying log data or need to clean up old log data using SQL scripts, and you find that querying log data is running too slowly, it could be due to missing indices. You can create them using the following steps on your Identify instance whose current version is 5.15 or above:
Download SQL script - indices.
Replace all [#SCHEMA] with your Identify schema name.
Execute the script against the IdentifyAudit database, e.g. using SQL Server Management Studio (SSMS).