Hi I am trying to capture / report / alert on CDC latency initially setting up a simple sql against the system DMVs only reported the response time of the query not the results
SELECT getdate() [CheckTime], latency AS seconds, (latency / 60.0) / 60.0 as [Hours] from [dbname].sys.dm_cdc_log_scan_sessions where session_id =0
this seems to be due to no PK for the DMV's and you cannot add them.

So I set up a capture through an agent job to run it and store it in a database table every 30 seconds
CREATE TABLE [dbo].[cdc_latency](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [CheckTime] [datetime] NOT NULL,
 [seconds] [int] NOT NULL,
 [Hours] [numeric](22, 10) NOT NULL,
 [info] [nchar](10) NOT NULL,
 CONSTRAINT [PK_cdcLatency_Id] PRIMARY KEY CLUSTERED
(
 [ID] ASC
)
ALTER TABLE [dbo].[cdc_latency] ADD  CONSTRAINT [D_cdc_latency_info]  DEFAULT ('Latency') FOR [info]
GO

When I set the monitor to query this table I get seconds value as 1 the query was

SELECT  [ID],[info],[CheckTime],[seconds],[Hours]FROM [dbname].[dbo].[cdc_latency]

it seems to always report the first row value....
I then changed it to get the latest value in the table each poll and now I only ever see one value in APM

SELECT TOP 1 [ID],[info],[CheckTime],[seconds],[Hours]FROM [DBA_Data].[dbo].[cdc_latency] order by id desc

Sample data i added the Info column as another message on this board seemed to imply it needed a fixed column as the PK but I cannot get it to work using the ID (which is an identity) or the Info set as PK
ID         CheckTime                      seconds            Hours       info
1            2019-11-12 17:56:52.700     1              0.0002777666    Latency  
2           2019-11-12 17:57:22.727      1              0.0002777666    Latency  
3           2019-11-12 17:57:52.737      2              0.0005555500    Latency  
4           2019-11-12 18:05:40.680     4              0.0011111000    Latency
Is what I am try to achieve possible? that is
   1. capture the latest latency recorded in the status table (in seconds and in hours)
   2. report on the latency values for the last hour/day/week etc
   3. alert if it seconds goes outside threshold value

Thanks in advance for any insight into this