...
Here is the Docker image of the data freshness emailer: https://hub.docker.com/r/mcarans/hdx-data-freshness-emailer/. The code for the implementation is here: https://github.com/OCHA-DAP/hdx-data-freshness-emailer. It has tests with a high level of coverage.
29/11/2017 (60 days of runs since overdue emails sent)
SELECT DISTINCT a.id, c.title FROM dbdatasets a, dbdatasets b, dbinfodatasets c where a.id = b.id AND a.run_number > b.run_number and b.run_number > 149 AND a.fresh = 0 and b.fresh = 2 AND a.id = c.id;
137 datasets updated after becoming overdue. Of these:
HOTOSM: 36 (probably failed exports so a valid alert of being overdue)
IDMC: 47 (wrong update frequency, now changed to annual)
InterAction: 36 (wrong update frequency, now changed to live and hence no more overdue emails will be sent)
18 others:
0a2bae2a-9c09-4aa8-bdf5-f66e71512cb1 CBPF Allocations and contributions
283503e7-13de-4528-9e57-a804196eb57a Afghan Voluntary Repatriation 2017
29d1cc01-cc72-4249-ab15-a01b6d10bfe9 CERF Allocations
2f429840-4def-422f-8586-fb321ccbd832 Topline Colombia Figures
342140f2-0d95-4130-a916-9b75868b0b49 Somalia NGO Consortium Topline Figures
56f3ba93-1765-4a59-a8e0-45088a69b728 International migrant stock
5c963b06-2af1-4fd8-8fef-5f702685a191 Percentage of children less than 5 stunted (Female)
6804f230-c2d4-4482-97a1-568dce069051 Total number of people targeted and reached per region and per cluster
830b908d-0ef3-48b1-bb98-4d8495ded760 Zambia-healthsites
a2150ad9-2b87-49f5-a6b2-c85dff366b75 Rohingya Displacement Topline Figures
ab91c8ec-d6e4-4931-8e45-b42e41bf2ec8 Mount Agung Update - Bali, Indonesia
b6fed87c-9e2b-49e4-8bea-cbfea54238fb Lesotho-healthsites
c12b27ea-5be4-4064-b87c-624cbbb7b1e1 Yemen: Cholera Outbreak Epidemiology Update Data
d1cd6842-dfe1-44c7-a1f5-3769000073af Location of Rohingya Refugees in Cox's Bazar
dce2c3e6-1cbb-4361-8875-b43995fbdf44 Somalia - Water Price Monitoring , August 2017
edb07f53-1023-4bf3-aeb4-d3789fc00642 Total number of children with fever who received treatment with antimalarial
f35ffbfe-428e-4b1f-a41e-1cd31b77e8cc Caribbean Hurricanes: Regional Who is Doing What Where (3W)
fdad1a97-5ffe-42ef-8d60-24e9398f89a8 Total number of reported cases of cholera
In the previous 60 days (before overdue email):
SELECT DISTINCT a.id, c.title FROM dbdatasets a, dbdatasets b, dbinfodatasets c where a.id = b.id AND a.run_number > b.run_number and b.run_number > 89 and b.run_number <= 149 AND a.fresh = 0 and b.fresh = 2 AND a.id = c.id;
94 datasets updated after becoming overdue.