Hello all,
This is something that I know is is possible and I am just missing something obvious. I am attempting to create dashboards that rely heavily on custom pollers and getting the latest status(polling every 5 minutes). I'm running into an issue where getting the last poll consistently for multiple values is failing. The issue being the polls are far enough from each other in a 5 minute interval that the query below leaves one off every couple of minutes or so.
SELECT cps.CustomPollerAssignmentID, cp.Description, cps.RawStatus,
CASE When cps.Status = 'Normal'
Then '<b><font color="#7CC364">' + cps.[Status]
Else '<b><font color="#DC3938">' + cps.[Status] End as Status
,cps.DateTime
,GETUTCDATE() as currentTime
FROM [NetPerfMon].[dbo].[CustomPollerStatistics] as cps
inner join dbo.CustomPollerAssignment as cpa on cpa.CustomPollerAssignmentID = cps.CustomPollerAssignmentID
inner join dbo.CustomPollers as cp on cp.CustomPollerID = cpa.CustomPollerID
where ((cps.DateTime >= DATEADD(Minute, -6, GETUTCDATE())) and (cps.CustomPollerAssignmentID = '0FF681ED-F7A2-452C-9EC6-C1A59B77A42F' or cps.CustomPollerAssignmentID = 'A415DB37-ABAE-4E51-B6F5-499E665D20B8'))
order by cps.DateTime desc
The reason I am using SQL for this is because the amount of information on the dashboard would be useful to have the color changes to alert those that need to know to give this a good look over. The main part I am attempting to use to get the latest poll is
(cps.DateTime >= DATEADD(Minute, -6, GETUTCDATE())
I have a feeling this has been figured out before and I may be going up the wrong tree. Any help at all would be greatly appreciated and if I figure it out I will be sure to let you all know. Thanks!
