Check Telephony Server
This is a two in one query. To run only one part, highlight the specific code and then execute the query. This query checks for activity on your telephony notifications. It’s a quick way to make sure your telephony notifications are going out and that the server is working. The first query pulls the current notification queue to look for backups and the second one pulls the notification log to check for successful calls.
/* PULL THE NOTIFICATION QUEUE */
/* This looks for backups in the telephony queue. It's normal to see some calls in the queue, but the queue should
be shrinking most of the time. Calls should not be hanging out here for a long period of time. */
SELECT
nq.ItemRecordID AS "Item Record ID",
ir.Barcode AS "Item Barcode",
br.BrowseTitle AS "Item Title",
nt.[Description] AS "Notification Type",
dopt.DeliveryOption AS "Delivery",
nq.PatronID AS "Patron ID",
pr.PatronFullName AS "Patron Name",
pr.PhoneVoice1 AS "Phone 1",
pr.PhoneVoice2 AS "Phone 2",
pr.PhoneVoice3 AS "Phone 3",
o.Name AS "Library",
nq.CreationDate AS "Creation Date"
FROM
Results.Polaris.NotificationQueue nq WITH (NOLOCK)
JOIN
Polaris.Polaris.ItemRecords ir WITH (NOLOCK) ON ir.ItemRecordID = nq.ItemRecordID
JOIN
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK) ON br.BibliographicRecordID = ir.AssociatedBibRecordID
JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON pr.PatronID = nq.PatronID
JOIN
Polaris.Polaris.Organizations o WITH (NOLOCK) ON o.OrganizationID = nq.ReportingOrgID
JOIN
Polaris.Polaris.NotificationTypes nt WITH (NOLOCK) ON nt.NotificationTypeID = nq.NotificationTypeID
JOIN
Polaris.Polaris.DeliveryOptions dopt WITH (NOLOCK) ON dopt.DeliveryOptionID = nq.DeliveryOptionID
WHERE
nq.DeliveryOptionID IN (3,4,5)
ORDER BY
nq.CreationDate
/* PULL THE NOTIFICATION LOG */
/* This looks for successes and failures in the notification log. In other words, these are calls that have already
been made. If you pull the queue for a given date and get nothing, there could be something wrong with the telephony
server. */
SELECT
nl.NotificationDateTime AS "Notification Date",
nl.PatronID AS "Patron ID",
pr.PatronFullName AS "Patron Name",
o.Name AS "Reporting Org",
nt.[Description] AS "Notification Type",
dopt.DeliveryOption AS "Delivery",
nl.DeliveryString AS "Phone Number",
nl.Details AS "Details",
nl.NotificationLogID AS "Notification Log ID"
FROM
PolarisTransactions.Polaris.NotificationLog nl WITH (NOLOCK)
JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON pr.PatronID = nl.PatronID
JOIN
Polaris.Polaris.NotificationTypes nt WITH (NOLOCK) ON nt.NotificationTypeID = nl.NotificationTypeID
JOIN
Polaris.Polaris.DeliveryOptions dopt WITH (NOLOCK) ON dopt.DeliveryOptionID = nl.DeliveryOptionID
JOIN
Polaris.Polaris.Organizations o WITH (NOLOCK) ON o.OrganizationID = nl.ReportingOrgID
WHERE
nl.DeliveryOptionID IN (3,4,5)
AND
nl.NotificationDateTime BETWEEN '2020-12-23 00:00:00.000' AND '2020-12-26 23:59:59.999' -- Change these dates to suit your needs.
--ORDER BY
--nl.NotificationDateTime DESC
ORDER BY
pr.PatronFullName