Time series from Matomo json api

Grafana Configuration
1

Hi I am pretty new to Grafana so maybe someone can help me out here.
I have created a Dashboard using the Matomo API. Works great, counters, bar charts, tables. Looks fantastic. One thing I am having trouble with though is creating a graph.

I want to display the number of visits over a period of 30 days. Pretty straight forward. I can get the data out of Matomo no problem. Just like a do with the other visualizations. However when I choose time series I get an error ’ Data is missing a time field’. I tried many things but I just don’t seem to be able to get the dates on the x-axis.

This is the api output in json

2024-03-17_18-02

2024-03-17_17-45
2024-03-17_17-451278×771 43.7 KB

Seems like the date is not being recognized as a time format? I tried converting it but to no avail. Anyone any suggestions? It shouldn’t be too hard, just a date and a number :slight_smile:

2

Welcome @jjblake

The date also needs to be part of the json returned as key value

3

Thanks @yosiasz Ah so this cannot be used as a time series. Too bad. I did try another Matomo API response where date was indeed a value and that one did work. However I don’t have any use for that particular response . It’s the live traffice API but it only returns the last 100 visits and in my case they all happen within a handful of seconds :slight_smile:

4

Please post the actual json as json value not as a picture
Maybe we can do pivot

5

This is the exact same requests I am doing but from the Matomo demo site.

https://demo.matomo.cloud/?module=API&format=JSON&idSite=1&period=day&date=2024-01-18,today&method=API.get&filter_limit=-1&format_metrics=1&expanded=1&token_auth=anonymous

1 Like
6

ok let’s start with this jsonata appraoch which one can use with infinity plugin and work our way to the final data points as to what it should look like. this might not be the only way to go there might be other ways.

So take a look at this https://try.jsonata.org/Gd_3oQnoT (which shows you the power of jsonata) along with documentation for jsonata Higher order functions · JSONata

And see if you can get what you need. after trying and maybe hitting a wall post back to see where we can go.

:fish: :fishing_pole_and_fish:

7

@yosiasz Thanks! I will read up on jsonata and try tomorrow after work and post my results

1 Like
8

After trying it out you should get the following. Enjoy your journey of learning jsonata

Basically you want to bring in the key root value of the json so that it looks something like this.

image
image1324×627 90.4 KB

[
  {
    "date": "2024-01-18",
    "data": {
      "Referrers_distinctCampaigns": 1,
      "Referrers_distinctKeywords": 80,

or event better something like this

[
  {
    "time": "2024-01-18",
      "Referrers_distinctCampaigns": 1,
      "Referrers_distinctKeywords": 80,
1 Like
9

That is what I want :slight_smile: Will have a go at it! Thanks so much for pointing me into the right direction

10

I think I understand the general idea here but I am not a coder. I tried to figure out how to use jsonata in grafana to bring in the key root value. But I am somewhat embarrassed to say I am overwhelmed by all the options in Grafana. For a beginner not always easy. I just don’t know where to look. Would you mind sharing a screenshot of the config you used to get this result? Your help and patience is much appreciated.

11

cool, so please post what you have tried and then we can try to take it from there.

12

In my mind (which is full of stuff except code) it goes like this… :slight_smile: Okay because in the provided JSON date isn’t a key value pair like

"date": "2024-01-01",

I might be able to use a JSONata query in order to transform the JSON, or bring in ‘date’ as the key root value.

In the JSONata exerciser you do that with this

(
    $datekeys := $keys().{"date": $} ;
)

So then I thought well there must be a way in Grafana to use the above. You said infinity works with JSONata so I figured I would find a query field of some kind that I could use. But didn’t find it. Then I googled a bit an realized I could maybe use UQL to do a JSONata query and apply it over the JSON input. I have absolutely no idea if I am on the right track :slight_smile: But anyhow using the code code from the exerciser in the UQL query field didn’t do anything. But when I look at the example here I get even more confused

2024-03-18_19-00
2024-03-18_19-001294×767 37 KB

13

you are absolutely on the right track!!! Hidden talent buried in back door of brain!

here is docu for infinity and UQL

https://grafana.github.io/grafana-infinity-datasource/docs/uql

1 Like
14

As @yosiasz mentioned, you can achieve this using different ways. Definitely Grafana was not designed to solve Matomo but it gives toolbox such as “infinity plugin”. Also every REST API endpoint is unique in their response format. So there is no out-of-the-box solution for every possible rest api such as Matomo.

If you comfortable in JSONata, you can directly use it infinity backend parser.

If JSONata is hard to write, there is UQL for simple usecase. Example

parse-json
| project kv()
| project "key", "values"=kv("value")
| mv-expand "result"="values"
| project "data"=todatetime("key"), "key"="result.key", "value"="result.value"

image
image1920×827 107 KB

All the best.

1 Like
15

I was playing around in JSONata but was not even close to that

parse-json
| project kv()
| project "key", "values"=kv("value")
| mv-expand "result"="values"
| project "data"=todatetime("key"), "key"="result.key", "value"="result.value"

Works like a charm! And most importantly I did learn some new things. It’s a good starting point for more experiments. Thanks @yosiasz and @yesoreyeram

All in all I am pretty satisfied with my Matomo dashboard in Grafana now. Definitely useful!

1 Like