Scott, What information do you want in the email? The hsi.parsedqueue table has the batch informaiton for DIP & COLD. Query the table for batches with a status of 2...
select pq.batchnum, pq.parsefilename, pq.datestarted, pq.dateended
from hsi.parsedqueue pq
where pq.status = '2'
For delivery (SQL Server approach) if the DBA has not disabled it there is the ability to send emails from a scheduled stored procedure.
Here is a similar, but more complex report for batch scanning that could provide a model to give you more granular details if needed.
SELECT
CASE
WHEN (GROUPING(X.[Hours OLD]) = 1)
THEN 'Total'
ELSE ISNULL(X.[Hours OLD],'Unknown')
END AS [Hours OLD],
CASE
WHEN (GROUPING(X.[Queue Name]) = 1)
THEN 'Total'
ELSE ISNULL(X.[Queue Name],'Unknow')
END AS [Queue Name],
SUM(X.[COUNT OF Batches Awaiting INDEX]) AS [COUNT OF Batches Awaiting INDEX]
FROM
(SELECT
--This Case statement divides the datediff into the desired time increments. In this statement it is breaking them in to 1 hour intervals.
CASE
WHEN DATEDIFF(mi, acq.datestarted, GETDATE()) < 60
THEN '0 - 1 hour'
WHEN DATEDIFF(mi, acq.datestarted, GETDATE()) >= 60
AND DATEDIFF(mi, acq.datestarted, GETDATE()) < 120
THEN '1 - 2 hours'
WHEN DATEDIFF(mi, acq.datestarted, GETDATE()) >= 120
AND DATEDIFF(mi, acq.datestarted, GETDATE()) < 180
THEN '2 - 3 hours'
WHEN DATEDIFF(mi, acq.datestarted, GETDATE()) >= 180
AND DATEDIFF(mi, acq.datestarted, GETDATE()) < 240
THEN '3 - 4 hours'
ELSE 'Over 4 hours'
END AS [Hours OLD],
-- Here we are getting the scan queue names and counting the batches that are awaiting index - status = 0
-- if other statuses are needed - Index in progress = 1 and awaiting commit = 2
sq.queuename AS [Queue Name],
1 AS [COUNT OF Batches Awaiting INDEX]
FROM (hsi.archivedqueue AS acq
INNER JOIN hsi.scanqueue AS sq
ON sq.queuenum = acq.queuenum)
WHERE acq.status = '0'
--enter the scan queue numbers you want to report on
--And sq.queuenum in (153, 154, 155)
) X
GROUP BY X.[Hours OLD],
X.[Queue Name]
WITH ROLLUP ORDER BY X.[Hours OLD],X.[Queue Name]
Good luck
KP
