Skip to content

Useful Diagnostic Tips

SQL

Last Seen

SELECT
    t.id,
    t.name,
    MAX(lr.timestamp) as last_apple_report, 'Never seen'
    COUNT(lr.id) as total_reports,
    CASE
        WHEN MAX(lr.timestamp) IS NULL THEN 'Never seen'
        WHEN MAX(lr.timestamp) > NOW() - INTERVAL '1 day' THEN 'Recent (< 1 day)'
        WHEN MAX(lr.timestamp) > NOW() - INTERVAL '7 days' THEN 'Within week'
        ELSE 'Old (> 1 week)'
    END as last_seen_status
FROM trackers t
INNER JOIN production_runs pr ON t.production_run_id = pr.id
LEFT JOIN location_reports lr ON t.hashed_advertisement_key = lr.hashed_adv_key
WHERE pr.start_date <= CURRENT_DATE AND pr.end_date >= CURRENT_DATE
GROUP BY t.id, t.name
ORDER BY MAX(lr.timestamp) DESC NULLS LAST;
id name last_apple_report total_reports last_seen_status
2989 uWt7hw0 2025-08-17 09:39:55+00 378 Recent (< 1 day)
1818 j9S2cnJ 2025-08-17 09:36:48+00 223 Recent (< 1 day)
2538 GTZrQCe - Mislabelled C02F34B14DEC 2025-08-17 09:10:39+00 251 Recent (< 1 day)
2059 TADc9zo 2025-08-17 09:10:12+00 205 Recent (< 1 day)
5176 Paul Bargewell 2025-08-17 08:04:15+00 246 Recent (< 1 day)
3061 igwls+4 2025-08-16 20:21:54+00 461 Recent (< 1 day)
2397 iN83gs8 2025-08-15 16:54:38+00 239 Within week
2895 7AuQDoP 2025-08-07 05:46:42+00 21 Old (> 1 week)
2920 TZMilcI 2025-07-31 01:19:02+00 54 Old (> 1 week)
3106 +o2uQX8 2025-07-26 15:00:52+00 1 Old (> 1 week)
2049 37aEnKD 2025-07-22 20:32:21+00 6 Old (> 1 week)
2207 B0vbar8 2025-07-21 12:58:25+00 3 Old (> 1 week)
2756 idTg3Kj 0 Never seen
2200 5mCO+Lw 0 Never seen
2811 CVBra3j 0 Never seen
2893 dGprmVs 0 Never seen

Last Processed

SELECT
    t.id,
    t.name,
    t.last_processed_at,
    COUNT(lr.id) as total_reports
FROM trackers t
INNER JOIN production_runs pr ON t.production_run_id = pr.id
LEFT JOIN location_reports lr ON t.hashed_advertisement_key = lr.hashed_adv_key
WHERE pr.start_date <= CURRENT_DATE AND pr.end_date >= CURRENT_DATE
GROUP BY t.id, t.name, t.last_processed_at
ORDER BY t.last_processed_at DESC NULLS LAST
LIMIT 15;
id name last_processed_at total_reports
5176 Paul Bargewell 2025-08-17 09:45:11.465259+00 246
3061 igwls+4 2025-08-17 09:45:11.463414+00 461
2397 iN83gs8 2025-08-17 09:45:11.46164+00 239
3111 bcyj5LU 2025-08-17 09:45:11.45948+00 0
3106 +o2uQX8 2025-08-17 09:45:11.456633+00 1
3046 TSWuutF 2025-08-17 09:45:11.454663+00 0
3019 whBhSTW 2025-08-17 09:45:11.449021+00 0
2997 HgrMCPo 2025-08-17 09:45:11.44705+00 0
2987 6O++g2Z 2025-08-17 09:45:11.445056+00 0
2920 TZMilcI 2025-08-17 09:45:11.442721+00 54
2918 Va0eVPJ 2025-08-17 09:45:11.440939+00 0
2895 7AuQDoP 2025-08-17 09:45:11.43959+00 21
2893 dGprmVs 2025-08-17 09:45:11.438033+00 0
2855 4UgKFmW 2025-08-17 09:45:11.436264+00 0
2830 6iRnR89 2025-08-17 09:45:11.434407+00 0

Latest Reports

SELECT
    t.id,
    t.name,
    t.last_processed_at,
    t.last_report_received,
    MAX(lr.timestamp) as latest_report,
    COUNT(lr.id) as total_reports,
    pr.description as production_run
FROM trackers t
INNER JOIN production_runs pr ON t.production_run_id = pr.id
LEFT JOIN location_reports lr ON t.hashed_advertisement_key = lr.hashed_adv_key
WHERE pr.start_date <= CURRENT_DATE AND pr.end_date >= CURRENT_DATE
GROUP BY t.id, t.name, t.last_processed_at, t.last_report_received, pr.description
ORDER BY t.last_processed_at DESC NULLS LAST, t.id
LIMIT 20;
id name last_processed_at last_report_received latest_report total_reports production_run
5176 Paul Bargewell 2025-08-17 09:45:11.465259+00 2025-08-07 15:11:00 2025-08-17 08:04:15+00 246 JJ Test
3061 igwls+4 2025-08-17 09:45:11.463414+00 2025-08-07 19:26:26 2025-08-16 20:21:54+00 461 JJ Test
2397 iN83gs8 2025-08-17 09:45:11.46164+00 2025-08-04 16:29:25 2025-08-15 16:54:38+00 239 JJ Test
3111 bcyj5LU 2025-08-17 09:45:11.45948+00 0 Walmart - Jurassic World
3106 +o2uQX8 2025-08-17 09:45:11.456633+00 2025-07-26 15:00:52 2025-07-26 15:00:52+00 1 Walmart - Jurassic World
3046 TSWuutF 2025-08-17 09:45:11.454663+00 0 Walmart - Jurassic World
3019 whBhSTW 2025-08-17 09:45:11.449021+00 0 Walmart - Jurassic World
2997 HgrMCPo 2025-08-17 09:45:11.44705+00 0 Walmart - Jurassic World
2987 6O++g2Z 2025-08-17 09:45:11.445056+00 0 Walmart - Jurassic World
2920 TZMilcI 2025-08-17 09:45:11.442721+00 2025-07-31 01:19:02 2025-07-31 01:19:02+00 54 Walmart - Jurassic World
2918 Va0eVPJ 2025-08-17 09:45:11.440939+00 0 Walmart - Jurassic World
2895 7AuQDoP 2025-08-17 09:45:11.43959+00 2025-08-07 05:46:42 2025-08-07 05:46:42+00 21 JJ Test
2893 dGprmVs 2025-08-17 09:45:11.438033+00 0 Walmart - Jurassic World
2855 4UgKFmW 2025-08-17 09:45:11.436264+00 0 Walmart - Jurassic World
2830 6iRnR89 2025-08-17 09:45:11.434407+00 0 Walmart - Jurassic World
2811 CVBra3j 2025-08-17 09:45:11.432855+00 0 Walmart - Jurassic World
2803 4wlJ+Ei 2025-08-17 09:45:11.431223+00 0 Walmart - Jurassic World
2790 0k8myTP 2025-08-17 09:45:11.428847+00 0 Walmart - Jurassic World
2756 idTg3Kj 2025-08-17 09:45:11.427351+00 0 Walmart - Jurassic World
2635 LJo34vB 2025-08-17 09:45:11.425672+00 0 Walmart - Jurassic World

Active Trackers

SELECT
    COUNT(*) as total_trackers,
    COUNT(CASE WHEN private_key IS NOT NULL AND private_key != '' THEN 1 END) as trackers_with_keys,
    COUNT(CASE WHEN pr.start_date <= CURRENT_DATE AND pr.end_date >= CURRENT_DATE THEN 1 END) as active_trackers
FROM trackers t
LEFT JOIN production_runs pr ON t.production_run_id = pr.id;
total_trackers trackers_with_keys active_trackers
1401 1401 33

Hourly / Daily Reports

SELECT
    t.id,
    t.name,
    COUNT(lr.id) as total_reports,
    MAX(lr.timestamp) as latest_report,
    COUNT(CASE WHEN lr.timestamp > NOW() - INTERVAL '1 hour' THEN 1 END) as reports_last_hour,
    COUNT(CASE WHEN lr.timestamp > NOW() - INTERVAL '1 day' THEN 1 END) as reports_last_day
FROM trackers t
INNER JOIN production_runs pr ON t.production_run_id = pr.id
LEFT JOIN location_reports lr ON t.hashed_advertisement_key = lr.hashed_adv_key
WHERE pr.start_date <= CURRENT_DATE AND pr.end_date >= CURRENT_DATE
GROUP BY t.id, t.name
ORDER BY t.id;
id name total_reports latest_report reports_last_hour reports_last_day
1818 j9S2cnJ 223 2025-08-17 09:36:48+00 11 20
2034 z2xbJFs 0 0 0
2049 37aEnKD 6 2025-07-22 20:32:21+00 0 0
2059 TADc9zo 205 2025-08-17 09:10:12+00 1 20
2200 5mCO+Lw 0 0 0
2207 B0vbar8 3 2025-07-21 12:58:25+00 0 0
2271 8z35PoD 0 0 0
2316 vrttRRE 0 0 0
2352 qYjNB7Y 0 0 0
2397 iN83gs8 239 2025-08-15 16:54:38+00 0 0
2468 BGfhYJA 0 0 0
2538 GTZrQCe - Mislabelled C02F34B14DEC 251 2025-08-17 09:10:39+00 2 19
2553 QVU7tXn 0 0 0
2635 LJo34vB 0 0 0
2756 idTg3Kj 0 0 0

Call Aggregates and Refresh Materialized View

-- Script to refresh location aggregates and fix the materialized view issue
-- This should be run periodically to keep the location_history view up to date

-- First refresh the continuous aggregates
CALL refresh_continuous_aggregate('location_history_hourly', NOW() - INTERVAL '7 days', NOW() + INTERVAL '1 hour');
CALL refresh_continuous_aggregate('location_history_daily', NOW() - INTERVAL '30 days', NOW() + INTERVAL '1 day');

-- Then refresh the materialized view
REFRESH MATERIALIZED VIEW CONCURRENTLY location_history;

-- Show the latest entries to verify
SELECT
    'Latest location_history entries:' as status,
    COUNT(*) as total_entries,
    MAX(timestamp) as latest_timestamp,
    MIN(timestamp) as earliest_timestamp
FROM location_history;

SELECT timestamp, nearest_city
FROM location_history
ORDER BY timestamp DESC
LIMIT 5;

Aggregate / View Age

SELECT
    'location_history' as view_name,
    MAX(timestamp) as latest_data,
    NOW() - MAX(timestamp) as age
FROM location_history
UNION ALL
    SELECT
        'location_history_hourly',
        MAX(bucket),
        NOW() - MAX(bucket) FROM location_history_hourly
    UNION ALL
        SELECT
            'location_history_daily',
            MAX(day),
            NOW() - MAX(day) FROM location_history_daily;
view_name latest_data age
location_history 2025-08-17 10:00:00+00 01:11:58.666613
location_history_hourly 2025-08-17 10:00:00+00 01:11:58.666613
location_history_daily 2025-08-17 00:00:00+00 11:11:58.666613

Check Aggregation

SELECT
    application_name,
    hypertable_name,
    config
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_refresh_continuous_aggregate';
application_name hypertable_name config
Refresh Continuous Aggregate Policy [1008] _materialized_hypertable_34 {"end_offset": "01:00:00", "start_offset": "1 mon", "mat_hypertable_id": 34}
Refresh Continuous Aggregate Policy [1009] _materialized_hypertable_35 {"end_offset": "1 day", "start_offset": "1 year", "mat_hypertable_id": 35} 
SELECT
    job_id,
    application_name,
    schedule_interval,
    proc_name
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_refresh_continuous_aggregate';
job_id application_name schedule_interval proc_name
1008 Refresh Continuous Aggregate Policy [1008] 01:00:00 policy_refresh_continuous_aggregate
1009 Refresh Continuous Aggregate Policy [1009] 1 day policy_refresh_continuous_aggregate 
SELECT
    job_id,
    application_name,
    schedule_interval,
    next_start
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_refresh_continuous_aggregate';
job_id application_name schedule_interval next_start
1008 Refresh Continuous Aggregate Policy [1008] 01:00:00 2025-08-17 12:28:56.169672+00
1009 Refresh Continuous Aggregate Policy [1009] 1 day 2025-08-18 11:28:56.170187+00

Location Reports by Production Run

SELECT *
FROM location_reports lr
WHERE lr.hashed_adv_key IN
    (SELECT t.hashed_advertisement_key
     FROM trackers t
     JOIN production_runs pr ON t.production_run_id=pr.id
     WHERE pr.id=6)
ORDER BY lr.timestamp DESC
LIMIT 5;
id hashed_adv_key timestamp location confidence horizontal_accuracy nearest_city
42640 5X7wOMWtbHYRjjYVpmIKgyxmvEvaC+xqhwvBMw== 2025-08-22 07:43:06+00 0101000020E61000004044B467E011F4BF7D19D69EA3314A40 3 82 Rugby
42619 5X7wOMWtbHYRjjYVpmIKgyxmvEvaC+xqhwvBMw== 2025-08-22 07:42:46+00 0101000020E610000020DC5328C111F4BF5F1AD01CA3314A40 3 86 Rugby
42598 5X7wOMWtbHYRjjYVpmIKgyxmvEvaC+xqhwvBMw== 2025-08-22 07:38:28+00 0101000020E6100000226BB2EB8311F4BFD1A79F81A2314A40 3 98 Rugby
42597 5X7wOMWtbHYRjjYVpmIKgyxmvEvaC+xqhwvBMw== 2025-08-22 07:32:57+00 0101000020E61000008597E0D40712F4BF83948E28A3314A40 3 38 Rugby
42660 bbuhhpm8/l6RZrb8Qyp9tHwqCGNTs+uSpPz76Q== 2025-08-22 07:30:22+00 0101000020E6100000380DF6CBCC11F4BF1E876643A3314A40 3 82 Rugby