Database maintenance requires a two-step approach. First, you configure the actions to take for each maintainable table and second, you configure and schedule the job to carry out the maintenance.

Table 1.0 - Target Tables

Table Name

Description

Potential Growth

dbo.ConnectivityLog

Records Room Screen connectivity events.

Each Room Screen profile creates 1 record every 60 seconds.

To calculate the amount of records generated in a 24 hour period you can multiply the number of room screen devices in your environment by 1440

Panel

bgColor	#DEEBFF

60 Room Screens would be 60 * 1440 = 86,400 records in a 24 hour period.*

dbo.BookingSystemConnectivityLog

Any Room Screen profile integrated to a 3rd party booking system will have a corresponding Booking System record when an event is written to the ConnectivityLog

Each Room Screen profile with an active 3rd party booking system integration creates 1 record every 60 seconds.

The same formula detailed in the ConnectivityLog table can be used to estimate the number of generated records

dbo.QubiActivityLog

Connections to the application from a Qubi 2, Qubi 3 or TD-0350 desk device are stored here.

Each of the listed desk devices will create 1 record every 60 seconds.

The same formula detailed in the ConnectivityLog table can be used to estimate the number of generated records

dbo.Bookings

Local bookings and 3rd party booking system records.

Each booking either created locally in the application or synchronized from a 3rd party booking system will create 1 record per configured profile.

dbo.ResourceMessages

Admin configured resource messages that are broadcast to Room Screens and Kiosks

Resource Messages records still persist in the database even after the configured expiry date has passed. If the message is deleted from the admin console it is also removed from the database.

dbo.EquipmentProblems

Any “Call for Help” reports for faulty equipment or other resources made from Room Screens or Kiosks.

Equipment Problem records persist in the database even when the problem has been closed in the admin console.

dbo.AuditLog

Status

colour	Green
title	v2023.1

Logs the audit information for the admin console

This depends on the changes done in the admin console.

dbo.GraphSubscription

Status

colour	Green
title	v2023.1

Logs Graph subscriptions for every profile. This is used for sending un-subscription request in case if there is a new subscription available for a resource.

This depends on the number of Exchange/Office 365 profiles.

dbo.SchedulerInfo

Status

colour	Green
title	v2023.1

Store the success profiles, failure profiles, total profiles run by scheduler and their updated time.

This depends on the schedule interval. If it is set to 5, then the table has 144 records per day.

dbo.UserLoginHistory

Status

colour	Green
title	v2023.1

Log the user logged-in time, IP address, Device Type, Login Type and OS Version.

This depends on the number of users logged in to Kiosk, Maps, Admin Console, Mobile, Outlook Add-In.

Note
*Records are not generated when the profile is in Energy Saving Mode

...

Deleting will improve performance and reduces reduce the size of the database.

...

Table 1.2 shows the relevant SQL column names and the value values configured by default.

Table 1.1 - SQL Values

Action Description	SQL Value
Delete	0
Archive	Any positive number i.e. 1, 2, 3 etc. The number configured will be the number of days records will be archived
Take No Action	NULL

Table 1.2 - Column Names and Default Values

Column Name

Default Value

ConnectivityLogDaysToKeep

We recommend that at least 1 day of Connectivity Log Records are kept

0

Status

title	Delete all records

BookingSystemConnectivityLogDaysToKeep

We recommend that at least 1 day of Booking System Connectivity Log Records are kept

0

Status

title	Delete all records

QubiActivityLogDaysToKeep

1

Status

title	Archive for 1 day

BookingDaysToKeep

NULL

Status

title	Take no action

ResourceMessagesDaysToKeep

NULL

Status

title	Take no action

EquipmentProblemsDaysToKeep

NULL

Status

title	Take no action

ConnectivityLogArchiveDaysToKeep

0

Status

title	Take no action

BookingSystemConnectivityLogArchiveDaysToKeep

0

Status

title	Take no action

QubiActivityLogArchiveDaysToKeep

1

Status

title	Archive for 1 day

BookingArchiveDaysToKeep

NULL

Status

title	Take no action

ResourceMessagesArchiveDaysToKeep

NULL

Status

title	Take no action

EquipmentProblemsArchiveDaysToKeep

NULL

Status

title	Take no action

GraphSubscriptionDaysToKeep

Status

colour	Green
title	v2023.1

3 = Archive for 3 days

SchedulerInfoDaysToKeep

Status

colour	Green
title	v2023.1

1 = Archive for 1 day

AuditLogDaysToKeep

Status

colour	Green
title	v2023.1

NULL = Take no action

AuditLogArchiveDaysToKeep

Status

colour	Green
title	v2023.1

NULL = Take no action

UserLoginHistoryDaysToKeep

Status

colour	Green
title	v2023.1

NULL = Take no action

UserLoginHistoryArchiveDaysToKeep

Status

colour	Green
title	v2023.1

NULL = Take no action

Note
Please ensure you take a full database backup before executing any SQL queries

...

Code Block

language	sql

update dbo.ArchiveSetting set QubiActivityLogDaysToKeep = NULL where TenantId = 1

Delete all Bookings Booking records

Code Block

language	sql

update dbo.ArchiveSetting set BookingDaysToKeep = 0 where TenantId = 1

...

If your ResourceXpress database is hosted on a dedicated SQL Server instance we recommend to carry carrying out database maintenance as a an SQL Agent Job.

To configure maintenance for a SQL Express hosted database please refer to Option 2 - Windows Task Scheduler & PowerShell - SQL Express Edition further down the page.

...

Code Block

language	sql

exec [dbo].ArchiveConnectivityLog 1
GO
exec [dbo].ArchiveBookingSystemConnectivityLog 1
GO
exec [dbo].ArchiveQubiActivityLog 1
GO
exec [dbo].ArchiveBookings 1
GO
exec [dbo].ArchiveResourceMessages 1
GO
exec [dbo].ArchiveEquipmentProblems 1
GO
exec [dbo].ArchiveGraphSubscription 1 
GO 
exec [dbo].ArchiveSchedulerInfo 1 
GO
exec [dbo].ArchiveAuditLog 1 
GO 
exec [dbo].ArchiveUserLoginHistory 1 
GO 
exec [dbo].DeleteBookingsArchive 1 
GO 
exec [dbo].DeleteAuditLogArchive 1 
GO 
exec [dbo].DeleteUserLoginHistoryArchive 1 
GO 
exec [dbo].DeleteGraphSubscription 1 
GO 
exec [dbo].DeleteSchedulerInfo 1 
GO

...

On the Advanced page drop down the option On success action and select Go to the next step then click OK to save the step

...

<SQL-SERVER\INSTANCE> = Your SQL Server instance name e.g. SQLSRV01\SQLEXPRESS
<database_name> = your ResourceXpress database name e.g. rxpressexpress

Code Block

language	powershell

# Initial values, replace the {name} with proper values
$connectionString = "Data Source=<SQL-SERVER\INSTANCE-NAME>;Initial Catalog=<database_name>;Integrated Security=False; User ID=dbsupport1;Password=D8Supp0rt!;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"
$timeout = 300 # in seconds
$tenantId = 1

Write-Output('DB Maintenance - v0.1 started')
# Instantiate the connection to the SQL Database
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $connectionString

# Define the SQL command to run
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = $timeout
$sqlCommand.Connection = $sqlConnection
$sqlCommand.Parameters.AddWithValue("@TenantId",$tenantId)

# Opening the SQL connection
try{
    $sqlConnection.Open()
}
catch{
    Write-Output('An error occurred in opening connection'+ $_.Exception.ToString())
    Exit 1
}

# Executing the stored procedures
$spList = 'ArchiveConnectivityLog','ArchiveBookingSystemConnectivityLog','ArchiveQubiActivityLog','ArchiveBookings','ArchiveResourceMessages','ArchiveEquipmentProblems','DeleteConnectivityLogArchive','DeleteBookingSystemConnectivityLogArchive','DeleteQubiActivityLogArchive','DeleteBookingsArchive','DeleteResourceMessagesArchive','DeleteEquipmentProblemsArchive','DeleteArchiveLog',‘ArchiveGraphSubscription’,’ArchiveSchedulerInfo’,‘ArchiveAuditLog’,’ArchiveUserLoginHistory’,’DeleteGraphSubscription’,‘DeleteSchedulerinfo’,‘DeleteAuditLogArchive’,‘DeleteUserLoginHistoryArchive’

foreach($sp in $spList){
     try{
        Write-Output('About to execute the sp ' + $sp)
        $sqlCommand.CommandText= 'exec [dbo].[' + $sp + '] @TenantId'
		$result = $sqlCommand.ExecuteNonQuery()
        Write-Output($sp + ' executed successfully')
    }
    catch{
        Write-Output('An error occurred in executing the sp ' + $sp + ' ' + $_.Exception.ToString())
    }
}

# Close the SQL connection
$sqlConnection.Close()
Write-Output('DB Maintenance is completed successfully')

...

The number of records deleted and or archived are recorded in the database in the ArchiveLog table, to view the logs execute the below SQL query against the ResourceXpress database.

Code Block

language	sql

select * from dbo.ArchiveLog

...

Document Updates

V2023.3

Two values have been removed from the $spList.

Status
colour Red
title Removed
DeleteGraphSubscription
Status
colour Red
title Removed
DeleteSchedulerInfo

Versions Compared

Old Version 25

New Version Current

Key

Table 1.0 - Target Tables

Table 1.1 - SQL Values

Table 1.2 - Column Names and Default Values

Document Updates

V2023.3

Page Comparison

Versions Compared

Old Version 25

New Version Current

Key

Table 1.0 - Target Tables

Table 1.1 - SQL Values

Table 1.2 - Column Names and Default Values

Document Updates

V2023.3