Querying closed_at Field for Tickets in Power BI via Invantive Cloud OData Feed

Questions
, , ,
1

I am currently integrating Freshdesk data into Power BI using the Invantive Cloud OData-feed. The integration process has been smooth, and I am able to access a broad range of data. However, I am facing a challenge with accessing the closed_at field for tickets.

Despite following the standard procedures outlined in the Invantive Cloud documentation for connecting to Power BI and loading data, the closed_at timestamp crucial for our service performance analysis appears to be missing. This field is not visible in the default dataset provided through the OData-feed.

I understand that Invantive Cloud allows for real-time data processing from Freshdesk using the interactive SQL editor and that recent updates have improved handling of large ticket volumes through tables like tickets_incremental. But it’s not clear if these methods would enable access to specific fields such as closed_at.

Could you please provide guidance or a method to customize the API calls or the OData-feed to include the closed_at field? If there is an alternative approach or a specific query that I need to use, that information would be highly beneficial.

Based on my research the closed_at can be accessed via “View a Ticket” API with the optional include stats.

Reference:

https://developers.freshdesk.com/api/#view_a_ticket

I appreciate any advice or instructions you can offer and look forward to your response.

2

Thanks for pointing out this issue.

In the current release, the field closed_at is available as stats_closed_at in tickets.

However, a bug was identified in tickets_incremental where this column is missing. This bug has been fixed. Starting release 24.0.44, the column will be included too in tickets_incremental. This new release will be available for on-premises use and testing such as with Invantive Query Tool on February 15, 2024. On Invantive Cloud, the release will be available this week. However, on Bridge Online and App Online it can take approximately 2 weeks since these are currently being upgraded from release 23.0 to 24.0.

Sample queries:

select id
,      stats_closed_at
from   tickets

select id
,      stats_closed_at /* Works only on 24.0.44 and newer. */
from   tickets_incremental
1 like
3

This question was automatically closed after at least 2 weeks of inactivity after a possible solution was provided. The last answer given has been marked as a solution.

Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.