Value mappings on the calendar plugin?

[grant2]

I am using the Calendar plugin. I tried to use value mappings to change the values displayed, but it does not work (no matter what I try). Can anyone else confirm they are using value mappings on this calendar visualization?

[johans]

Do you have a example of data set you are u are using?

[grant2]

Sample data is:

userID	Start	End
58	2022-06-19T03:44:20.220Z	2022-06-19T13:44:20.220Z
62	2022-06-20T04:44:20.220Z	2022-06-20T14:44:20.220Z
78	2022-06-20T02:04:20.220Z	2022-06-20T17:04:20.220Z

Here is my unsuccessful attempt to use Value Mappings:

[johans]

Are you using mySQL to store the data?

Then you can use your task table and a look up table

Task Table - task
userID Start End
58 2022-06-19T03:44:20.220Z 2022-06-19T13:44:20.220Z
62 2022-06-20T04:44:20.220Z 2022-06-20T14:44:20.220Z
78 2022-06-20T02:04:20.220Z 2022-06-20T17:04:20.220Z

User lookup table - user

userID Name Phone
58 John 555-1555
62 Sally 555- 1235
78 Fred 555-4562

SELECT userID.task, Start.task, End.task, Name.user FROM task INNER JOIN user ON task.userID=user.userID

I notice you have userID 58 as John and Sally

[grant2]

I am getting the actual data from a Rest API that is a JSON datasource. The CSV data I provided was just dummy data, but the result is the same — the value mappings do not seem to work for the Calendar plugin. I could use a lookup table if I was storing the data in a database, but that should not be necessary if I can get the data from the Rest API.

Unless someone can show me otherwise, I believe the value mappings do not work with this plugin.

[yosiasz]

Doable via infinity plugin?

--UQL
parse-json
| extend "userID"=replace_string("userID",'58','Mark')

[tobyvarland]

@grant2: please update here and let us know if @yosiasz’s suggestion works. It looks to me like it should, but I don’t have time to dig in and try it out myself right now.

[grant2]

Will do. I will download & install the Infinity plugin later today after I upgrade our instance of Grafana and some other devices that received firmware upgrades last week. (cough / EPIC / cough)

[yosiasz]

the plugin ignores the grafana convert to string functionality. So had to do the follow

parse-json
| extend "userID"=tostring("userID") 
| extend "userID"=replace_string("userID",'58','ዮስያስ')

But not sure why you would do it as value mapping or the above approach. Sounds tedious and not maintainable to me. I think this should be fixed on the rest api source end, if you have control over it. or if you have another rest api that provides you users list you can marry it with the other rest api.

[grant2]

Thanks to all for your help. So to recap…

1. The Calendar plugin ignores value mappings and any sort of replace string logic. If you agree, maybe you can upvote my Github request?
2. The Rest API datasource that I am using (QuickBooks Time API Reference) is to retrieve start and end times of the days/hours worked for each person. The Rest API returns the timesheet data with user_IDs (instead of names).
3. Separately, there is a Rest API call that returns a list of user_IDs and names.

Can anyone explain how to combine #2 and #3 in Grafana? I have set up the JSON datasource and can use both of the APIs successfully to display the data in Grafana as two separate tables. However, it would be best (as @yosiasz says) to marry the two APIs so that I can view the user names on the Calendar.

PS: I installed the Infinity plugin, but could not get it to work with the Rest API JSON datasource above. Maybe I am missing something obvious.

[yosiasz]

use Merge Transformation with users data as first query.

[
 {
   "userID": 58,
   "Name": "Vader"
 },
 {
   "userID": 62,
   "Name": "Luke"
 },
 {
   "userID": 78,
   "Name": "Ob1KNoB"
 }
]

[grant2]

Thank you @yosiasz I am using Grafana OSS version 8.5.4 and Infinity plugin version 0.8.8

Can you explain how to use the Merge Transformation? The documentation does not really say how to do it.

Here are my two queries (note that I am NOT using the Infinity plugin…do I have to?)

Here is the Transform tab…

Also note that the first Rest API uses the term user_id and the second Rest API uses the term id.

[yosiasz]

It says: Values are mergeable if the shared fields contain the same data.

For the name issue id vs user_id look up infinity UQL documentation. There are ways you can rename/alias fields. Pretty sure same functionality exists in grafana.

If on every post you surprise us with new data details, it will be difficult to provide guidance.

[yosiasz]

a simple node express api

const express = require("express");
const bodyParser = require("body-parser");
const fetch = require('node-fetch');
const app = express();
const port = 3200;

app.use(bodyParser.json());
app.use(express.static("public"));

app.get("/users", (req, res) => {
  return res.json( [
    {
      "id": 58,
      "first_name": "Darth",
      "last_name": "Vader"
    },
    {
      "id": 62,
      "first_name": "Luke",
      "last_name": "Skywalker"
    },
    {
      "id": 78,
      "first_name": "Ohbe",
      "last_name": "1Knobby"
    }
   ]
   )
});

app.get("/timesheets", (req, res) => {
  return res.json( [
    {
      "user_id": 58,
      "Start": "2022-06-19T03:44:20.220Z",
      "End": "2022-06-19T13:44:20.220Z"
    },
    {
      "user_id": 62,
      "Start": "2022-06-20T04:44:20.220Z",
      "End": "2022-06-20T14:44:20.220Z"
    },
    {
      "user_id": 78,
      "Start": "2022-06-20T02:04:20.220Z",
      "End": "2022-06-20T17:04:20.220Z"
    }
  ]
   )
});



app.listen(port, () => {
  console.log(`App listening on port ${port}`);
});

[yosiasz]

If you want to rather use another json plugin make sure to give the users.id column the alias of user_id.

Same can be done with simplejson

[grant2]

Thank you for your help so far.

Taking baby steps…

You can see I have output for each query (users and timesheets). The Merge transformation does not appear to be doing anything. In addition, the output is not a simple table like I was hoping for, indicating I might have something wrong in my settings?

And here is the UQL for the first query:

[yosiasz]

I would encourage you to read UQL Doc on how to parse json results.

You might need something like
$.timesheets and $.users to unravel those json results.

Please post your ssmple json result or point us to the sample url

[grant2]

OK, so I was able to solve it using the Merge transformation and the regular JSON datasource that I had working from the beginning. Am sure the Infinity plugin is more slick in terms of options and transformations, but for what I was trying to do, this works:

[yosiasz]

you have the users in the supplemental_data part of the api, so I don’t think you need another call to the users api?