nd_poller_perf.sql

gistfile1.txt

 $ cat /data/postgres/bin/nd_poller_perf.sql
with cte as (
select
    (select count(*) from device where last_macsuck > (current_timestamp - interval '1 minute')) as macsuck_last1min,
    (select count(*) from device where last_arpnip > (current_timestamp - interval '1 minute')) as arpnip_last1min,
    (select count(*) from device where last_macsuck > (current_timestamp - interval '5 minutes')) as macsuck_last5min,
    (select count(*) from device where last_arpnip > (current_timestamp - interval '5 minutes')) as arpnip_last5min
)
select
    c.macsuck_last1min,
    c.arpnip_last1min,
        case (c.macsuck_last1min + c.arpnip_last1min)
        when 0 then -1
        else ((select count(*) from device where last_discover > current_timestamp - interval '72 hours' ) / ((c.macsuck_last1min + c.arpnip_last1min) / 2.0))::int
    end as estim_cycle_1min,
    c.macsuck_last5min,
    c.arpnip_last5min,
        case (c.macsuck_last5min + c.arpnip_last5min)
        when 0 then -1
        else ((select count(*) from device where last_discover > current_timestamp - interval '72 hours' ) / ((c.macsuck_last5min + c.arpnip_last5min) / 2.0) * 5)::int
    end as estim_cycle_5min
    from cte c