SQL Queries

SQL Query for Monitoring Surveys Returning Errors

The following SQL statement will return the amount of distinct surveys on the system returning errors to respondents in the last five minutes:

select count(distinct(projectid))
from [confirmlog]..[surveymonitor]
with(nolock)
where errorflag<>0
and performed >dateadd(mi,-5,getdate())
and test=0;

The monitoring system could run this at a frequency of 5 minutes or less. If there are many live surveys on the system (more than 100) then warning/error thresholds could probably be set between 5 and 10. Setting a lower value introduces the risk of “over-alerting” on survey errors generated by script code – errors that project owners also receive.

SQL Query for Monitoring Errors from Authoring

Similar to monitoring errors from surveys, the following query may be used to report on the number of errors recently generated by Authoring:

select count (id) as count
from [confirmlog]..[reportmonitor] with (nolock)
where errorflag<>0
and performed > dateadd(mi,-10,getdate())
and context=2

Changing the context value from 2 to 3 will give results for Express while omitting the context altogether will return both Authoring and Express results.

SQL Query for Monitoring Errors from Reportal

The following query may be used to report on the number of errors recently generated from Reportal:

select count(id) as count
from [confirmlog]..[reportalmonitor] with(nolock)
where errorflag<>0
and performed >dateadd(mi,-10,getdate())

SQL Query for Monitoring Errors from Web Services

The following query may be used to report on the amount of errors recently generated from XML Web Services:

select count(id) as count
from [confirmlog]..[webservicemonitor] with(nolock)
where errorflag<>0
and performed > dateadd(mi,-10,getdate())

SQL Query for Monitoring Delays in Task Queue Execution

The following SQL statement returns the number of tasks in queue that are delayed by more than ten minutes:

Select count(id) as QueuedTasks
from [confirm].dbo.taskinstance (nolock)
where status=0
and datediff(mi,schedule,getdate())>10;

If a single project is queued up with many different tasks and this is expected behavior that could be ignored as long as it doesn’t affect other projects, the above query may be modified to return a count of distinct projects having delayed tasks in queue instead of the total number of tasks:

select count(distinct(td.projectid)) as QueuedTasks
from [confirm].dbo.taskinstance ti (nolock)
left join [confirm]..taskdefinition td (nolock)
on td.id = ti.taskdefinitionid
where ti.status=0
and datediff(mi,ti.schedule,getdate())>10;

SQL Query for Monitoring Failing Launch Survey Tasks

If users are unable to launch new or updated surveys, the production status of the system is pretty much at a standstill. The query below will return the number of Launch Survey tasks that have failed recently:

select count(distinct(td.projectid))
from taskinstance ti (nolock)
join taskdefinition td (nolock)
on ti.taskdefinitionid = td.id
where ti.tasktypeid=43
and ti.status not in (0,1,4)
and ti.starttime>dateadd(mi,-15,getdate());

SQL Query for Monitoring the Number of Attached Survey Databases

Forsta Plus installations with many active surveys should utilize the automatic survey database detachment/attachment feature. It is also recommended to monitor how many survey databases are attached at a given time if you suspect your database server is nearing capacity – detaching inactive databases frees up resources for live ones, and reduces time spent for failover if you are running an active-passive SQL cluster. The following query returns the amount of attached survey databases on a particular SQL Server instance:

select count(*)
from confirm_admin.dbo.surveydatabases
where active = 1;

SQL Server Audit for Surveys

This option enables SQL Server auditing at the survey database level. Refer to the Forsta Administrator documentation for details on how to turn it on. When enabled, all surveys created for a company will have database auditing enabled. This audit log is not accessible through the Forsta Plus user interface. To access the audit logs, go to the SQLAudit folder configured in System Configuration. You can also query the SQL Audit logs by using:

SELECT * FROM sys.fn_get_audit_file ((SELECT audit_file_path FROM sys.dm_server_audit_status WHERE name = 'Audit-SQL'),default,default)

SQL Query for Monitoring REST API Server Errors

The REST API servers are the backbone of a lot of the services in Forsta Plus. The logs are IIS based, meaning that all errors in the 500 range are server error messages.

select count(id) as count
from [confirmlog]..[qMetadataApiMonitor] with(nolock)
where statuscode >=500
and timestamp >dateadd(mi,-10,getdate())