Create time-series graphs from json data imported using OAuth2 API

Grafana Dashboards
1
  • What Grafana version and what operating system are you using?

Grafana 9.2.4 in docker on Ubuntu

  • What are you trying to achieve?

I would like to create log-style graphs from a json api.
I have previously used loki/promtail datasources and count_over_time to turn log lines into numbers.

I would like to start creating other visualisations based on this data, e.g.

  • time series graph showing (count of entries by ‘ip address’) Vs time
  • time series graph showing (count of entries by ‘type’ ) Vs time
  • How are you trying to achieve it?

My API source requires OAuth2, so I’ve used Infinity Datasource to access this. I’m then trying to create a visualisation similar to as if I’d accessed the datasource via Loki/Promtail.

  • What happened?

I’m unable to create timeseries graphs because I cannot figure out how to create a number value.

  • What did you expect to happen?

Use a transform, or even the count_over_time selector to create numbers from my data.

  • Can you copy/paste the configuration(s) that you are having problems with?

No - I don’t have anything successful. I’d like help creating configurations.

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

No errors.

  • Did you follow any online instructions? If so, what is the URL?

No - I can’t find any tutorials.

I am successfully receiving a stream of .json in the following example format into the Infinity Datasource plugin:

{
    "continuationToken": null,
    "data": [
        {
            "actingUserId": "83cd55a9-95bf-4eb5-a221-af4900c54bf7",
            "collectionId": null,
            "date": "2022-11-10T18:10:20.8466667Z",
            "device": 10,
            "groupId": null,
            "installationId": null,
            "ipAddress": "xxx.xxx.xxx.xxx",
            "itemId": "a8478aa6-5dc7-4973-b8f9-af49012b78fb",
            "memberId": null,
            "object": "event",
            "policyId": null,
            "type": 1100
        },
        {
            "actingUserId": "83cd55a9-95bf-4eb5-a221-af4900c54bf7",
            "collectionId": null,
            "date": "2022-11-10T15:00:29.8533333Z",
            "device": 10,
            "groupId": null,
            "installationId": null,
            "ipAddress": "xxx.xxx.xxx.xxx",
            "itemId": "4bfa6ac8-d26c-48b0-b8a3-af4900f7540b",
            "memberId": null,
            "object": "event",
            "policyId": null,
            "type": 1100
        },
        {
            "actingUserId": "83cd55a9-95bf-4eb5-a221-af4900c54bf7",
            "collectionId": null,
            "date": "2022-11-10T14:58:41.94Z",
            "device": 10,
            "groupId": null,
            "installationId": null,
            "ipAddress": "xxx.xxx.xxx.xxx",
            "itemId": null,
            "memberId": null,
            "object": "event",
            "policyId": null,
            "type": 1600
        }
    ],
    "object": "list"
}
2

try this but I think there might be a bug , the formatting of the date does not make the summarization work properly,

@yesoreyeram

parse-json
| scope "data"
| extend "date"=format_datetime("date",'YYYY-MM-DD') 
| summarize "ipCount"=count() by "date"
| order by "ipCount" desc
3

Thanks for your suggestion.

This gives a flat line of ‘1’. I’ve tried to debug a little, but I’m not exactly super skilled here.

Breaking it down, the second line doesn’t produce any output if I cut there. Is there perhaps a mistake with the output date format?

The time does actually display for timezone data purposes without modification - are you truncating so that multiple e.g. IPs fit within a timezone (because at present the count for each time flag will be 1, because the time recorded is too precise?)

I also can’t see what you’re counting when you refer to “ipCount” - could you let me know how this picks up on say the ipAddress field?

Thank you for your input - I’d love to keep working on this if you have more suggestions!

1 Like
4

while I fiddle with it please check the doco out

5

Something is not jiving here maybe we can use jsonata query,

image
image1384×686 39.4 KB

6

the count is 1 because we are counting per time series which is date+time
Did you want to count per day or time series meaning date with the time ?

image

7

Hi,

Thanks for your suggestions - I’m going to go and have a play.

While I do, I thought it would be useful to show you what I’m trying to achieve. Here’s a screenshot of another part of the dashboard in progress, this time using Loki & Promtail to visualise docker container logs:

Screenshot 2022-11-16 at 12.24.13
Screenshot 2022-11-16 at 12.24.132756×1814 282 KB

And here’s the query that’s generating the visualisation:

count_over_time({job="jobname"}[1m])

As you can see, as I produced a spike in failed login attempts, the numerous log entries are aggregated into a count over time and produce a spike on the graph.

Each .json object is effectively a log entry, and I’m trying to produce similar visualisations from them. I’d like a tracker of how often for example ‘type 1108’ occured (which relates to a particular database action), and then how often ‘type 1100’.

The issue that I’m facing is aggregating them, because I can’t seem to see a direct equivalent to the count_over_time function in infinity. Once I get this, I should be OK to go on and create the visualisations and dashboards that I want.

I’ve got some time now to review your code examples, and I’ll review the docs again and do some more googling. Thank you for the time you’ve put in so far!

1 Like
8

To be clear - the screenshot above shows the result that I’m trying to achieve, but because the datasource is different (server logs scraped by Loki + promtail) I don’t seem to be able to just use those methods.

I’d like to replicate the above using the logs that are being scraped by Infinity’s connection to the API.

I’m also open to changing data source if necessary - the requirement is that it should be able to access the .json stream via OAuth2.

9

I had some time to play today, and tried to understand JSONata as well. I don’t think that it’s going to be helpful, but I may be wrong there.

I don’t think that I need to to anything at all with the timestamp - it just works as is.

I tried multiple ways of summarising the data, but didn’t really achieve anything. I’ll try again tomorrow, and make sure I write up what I find, even if it’s not successful.

One thing that I did note was that the plugin author offers a tutorial which is exactly what I’m aiming for:

However, when replicating the settings that he has there, I’m still given the same error - ‘no numerical data’. In particular, he formats 4 columns - 1x ‘Timestamp’ and 3x ‘String’. I can’t see where the numerical data for the (wonderful!) visualisation that he publishes is coming from.

Thank you again for any more time that you spend on this - I hope it’s an interesting query.

1 Like