SQL to extract views for a URL?

General questions
1

I am trying to take a 3.14 Matomo database and extract visits of a URL, via SQL.

I’ve looked at the SQL triggered by an API call but nowhere does it seem to reference the URL.

Our API calls are not responding.

C

2

I had the same problem with 3.14.1 - the UI was just too slow and did not respond.

It seems the URLs are stored in the table log_action - find your URL in there and note the idaction number:

SELECT * FROM `log_action` WHERE `name` LIKE '%the-file-i-am-looking-for.apk' LIMIT 50
| 2342 | http://example.org/foo/bar/the-file-i-am-looking-for.apk |

The log_link_visit_action table seems to contain information when those URLs have been requested. Use the idaction_url column with the idaction from your previous sql result.
Here is a query to get the number of downloads for 4 URLs grouped by month:

SELECT SUBSTRING(server_time, 1, 7) as date, COUNT(*) as count
FROM `log_link_visit_action`
WHERE `idaction_url` IN (1078305,1103532,1431512,1432793)
GROUP BY date
ORDER BY date

date    count
2021-12    12
2022-01    21
2022-02    26
2022-03    8
2022-04    9
2022-05    15