# Holes in the CSV log

**URL:** https://community.ntppool.org/t/holes-in-the-csv-log/3135
**Category:** Server operators
**Tags:** monitoring
**Created:** [November 22, 2023, 4:58pm UTC](https://community.ntppool.org/t/holes-in-the-csv-log/3135 "2023-11-22T16:58:36Z")
**Posts on this page:** 1
**Showing post:** 4

<div class="post-metadata">

### Author: ![ask](https://sea2.discourse-cdn.com/flex016/user_avatar/community.ntppool.org/ask/32/907_2.png) [@ask](https://community.ntppool.org/u/ask)
#### Post date: [December 15, 2023, 8:41pm UTC](https://community.ntppool.org/t/holes-in-the-csv-log/3135/4 "2023-12-15T20:41:48Z")

</div>

Apologies I didn’t get to this sooner where it might have been more relevant.

The JSON API was really just meant to power the graphs, hence it shows a bit different data (and the CSV logs more as “logs”, I suppose).

You can get your server ID next to the CSV link on the scores page and then query the full data set in Google BigQuery:

```auto
SELECT
  EXTRACT(year from ts),
  EXTRACT(month from ts),
  AVG(score),
  AVG(offset),
  count(*)
from ntppool.ntppool.log_scores
where server_id = 57259
  group by 1,2
  order by 1,2;

```

To include data from before 2020, use

```
from `ntppool.ntppool.log_scores*`

```

(I can’t figure out how to link to a working example; I think [opening the “console”](https://console.cloud.google.com/bigquery?project=ntppool) from that link and then clicking “create sql query” and using the example above might work).

---

_[View the full topic](https://community.ntppool.org/t/holes-in-the-csv-log/3135)._