Date group by not working as expected

Grafana Dashboards
, ,
1

Date group by not working as expected when date range is from 1-nov the group by feature working correctly but when the date range set to after 1 nov date group by not working see the screenshots below

Grafana version: 9.3.1
Data Source: infinity

perfect time range
perfect time range1027Ă—511 20.2 KB

wrong time range
wrong time range1031Ă—522 22.1 KB

2

Hi @usamaaltaf420,

I think I understand this case from a previous post where the Transformation were not working. As an alternative, you can use Grafana’s Override function to achieve this.

I had replied to a post where I attached a .gif file which contains the steps as how to do it (in your case you may need some more modification with the “group_by” but I think should be possible).

Let us know if this helps. :slight_smile:

3

Hey thanks you for your help but the thing is I know about the Override function of Grafana and I’ve tried it already with group by but got no success.
I don’t know if I am doing it right or wrong please let me know I am attaching the screenshot please see this

@usmanahmad @yosiasz can you help me with this issue?

groupby
groupby1366Ă—462 30 KB

4

Please post sample of your data and the result you expect tobsee from the sample data?

5

So @yosiasz when my data is like below, the date group by feature is working when date is from 1 nov

{
   "value": [
        {
            "startDateTime": "2022-11-01T11:03:11.811Z",
            "inviteDateTime": "2022-11-01T11:03:06.68Z",
            "failureDateTime": null,
            "endDateTime": "2022-11-01T11:03:15.811Z",
            "duration": 4,
            "callType": "ByotOut",
            "successfulCall": true,
            "finalSipCode": 0,
            "callEndSubReason": 560000
        },
        {
            "startDateTime": null,
            "inviteDateTime": "2022-11-01T11:03:07.514Z",
            "failureDateTime": "2022-11-01T11:03:07.514Z",
            "endDateTime": null,
            "duration": 0,
            "callType": "ByotIn",
            "successfulCall": false,
            "finalSipCode": 404,
            "callEndSubReason": 511404
        }
   ]
}

And when data is like this starting from 2 nov it is not grouping.
I know this can be a issue of date format but don’t know how to do it

{
   "value": [
        {
            "startDateTime": null,
            "inviteDateTime": "2022-11-02T03:52:57.994Z",
            "failureDateTime": "2022-11-02T03:53:06.652Z",
            "endDateTime": null,
            "duration": 0,
            "callType": "ByotIn",
            "finalSipCode": 487,
            "callEndSubReason": 540487
        },
        {
            "startDateTime": null,
            "inviteDateTime": "2022-11-02T03:53:07.503Z",
            "failureDateTime": "2022-11-02T03:53:16.776Z",
            "endDateTime": null,
            "duration": 0,
            "callType": "ByotIn",
            "successfulCall": false,
            "finalSipCode": 487,
            "callEndSubReason": 540487
        }
   ]
}

I want to group inviteDateTime

6

Show us a screen shot of the transformation you are using with 11-02 and show us what is “not working”. Is it the total or the count? And what should the correct/working result?

7

you can see the above screenshot where I am using group by but date is not grouping and it is also showing time with it

here it is

1366Ă—462 30 KB

1 Like
8

I am not seeing time in your tranformation. Try UQL with jsonata

parse-json
| jsonata "( $.value{ inviteDateTime: { 'inviteDateTime': $substring(inviteDateTime, 0, 10) } } ~> $each(function($v){$v}) )"
| summarize "TicketCount"=count() by "inviteDateTime"

image
image1019Ă—701 35.8 KB

9

Thank you so much you saved my whole day

one little help please if you don’t mind I want to see successfulCall like how many true that day and how many false can I use this with your code

parse-json 
| jsonata "value[successfulCall=true]"
| summarize count() by "inviteDateTime" , "successfulCall"

value[successfulCall=true] and false wants to see both :slight_smile:

tried like this but not working

parse-json
| jsonata "( $.value{ inviteDateTime: { 'inviteDateTime': $substring(inviteDateTime, 0, 10) } } ~> $each(function($v){$v}) )"
| jsonata "value[successfulCall=true]"
| summarize count() by "inviteDateTime" , "successfulCall"
1 Like
10

It is no longer my code, you own it now :smile:

Time to learn jsonata,

https://try.jsonata.org/

11

Sorry :smile: but I mean that I want to see how many calls are successful and unsuccessful each day

like on 2022-11-01 total calls: 90, successfulCalls: 40, unsuccessfulCalls 50,

I tried to modify your code like this

parse-json 
| jsonata "( $.value{ inviteDateTime: { 'inviteDateTime': $substring(inviteDateTime, 0, 10) }, successfulCall: true } ~> $each(function($v){$v}) )"
| summarize count() by "inviteDateTime", "successfulCall"

but no success hoping I will get it :wink:

I also tried this

parse-json
| jsonata "( $.value{ inviteDateTime: { 'inviteDateTime': $substring(inviteDateTime, 0, 10) } } ~> $each(function($v){$v & {'successfulCall': false} })) | ( $.value{ inviteDateTime: { 'inviteDateTime': $substring(inviteDateTime, 0, 10) } } ~> $each(function($v){$v & {'successfulCall': true} }))"
| summarize count() by "inviteDateTime", "successfulCall"
12

Did you read the documentation? And used https://try.jsonata.org/ to test your jsonata outside of grafana?

I was able to solve your problem by reading the documentation. No reason why you cant do that

2 Likes
13

yes I tried I am a noob on jsonata but don’t worry I’ll get it.
by the way thanks for your help :slight_smile:

1 Like