Enhancement request to smGetRequests API

[samwoo]

https://api.hornbill.com/apps/com.hornbill.servicemanager/Requests?op=smGetRequests

Please can I request an enhancement to this API, where visibleColumns includes resolvedByColumns, is to have it also return the resolved_by_team_id. It has the team name, but not the ID.

Thanks,

Samuel

[Victor]

@samwoo ummm.... why?  ... what "worries" me more is this post is in Integrations section... which might indicate you are using this for integrations... which would be... ermm... not good

[samwoo]

I only wanted to get the information for reporting purposes to extract the data using API and connect to Power BI. I can't extract all "status.closed" requests using entityBrowseRecords2 since it is well over the limit, and we don't want to use R Script since we want to publish it, so the alternative is to use API to populate the data.

Have I overstepped somewhere? If so, I'll stop what I'm doing.

edit: And apologies if this is the integrations section, I posted in here because it has API in the name so I may have gotten confused and put the post in the wrong place?

[Victor]

13 hours ago, samwoo said:

extract the data using API

@samwoo that's what I thought... don't use smGetRequests for this. Or for anything. I'll try and explain. Why. This API is built for one purpose only: to work in the context of the request list view in SM app. This is, let's say, an optimised method to allow requests list to perform (nicely) when displaying requests in the list. It is built solely to use specific front and and backend context provided by SM app. If we take this API out of its designated context, there is a decent chance it will perform badly and might even not work as expected, not return all the results one would expect, specifically because it is taken out of it's context. As you suggested, the API that should be used to retrieve records is entityBrowseRecords2. This is a designated API for this specific purpose to retrieve records and it's designed to (also) work outside app context (unlike smGetRequests).

What exactly is over the limit if you would use entityBrowseRecords2? What records you need for PowerBI?

[samwoo]

Thanks @Victor now that makes a lot more sense and will cease the use of smGetRequests going forward. I know at Hornbill there is work being done around the API and it's documentation, is it possible to either:

1. Restrict access to this API outside of Hornbill and not have it displayed in the documentation?
2. Add a note or something advising what you said?

I think this would be good for any other API's designed for a specific purpose such as this one.

Going back to your question, I use entityBrowseRecords2 for the "Active" requests table (status.open, status.new and status.onHold) and this works fine.

I then needed another table for "Resolved" requests (status.resolved, status.closed) but I get an error (though nothing in the error logs). If I just use "status.resolved" it works, but if I switch it to "status.closed" the error occurs again.

I then did a database direct count of closed requests, and we have a count of over 142600 closed tickets which I am assuming is the reason it's failing so I'm not quite sure what else I can do to get this all out.

I am planning to use the information from these record retrievals to display trends in our use of Hornbill since we started using the platform. I also plan to connect to the RequestFeedback entity and other tables, such as the user accounts table for customers and the duplicate table for owners and resolved by users, as well as the teams table, in order to create multiple dashboard pages with drill-down visuals.

Here are the ones that work using R Script, but won't work because R Script cannot be published to Power BI online without manual intervention required each time to update the visuals

I think eventually we'll restrict the number of years to 5 years prior or something to that effect.

 

There are some other pages as well, and most of it can be drilled down when selecting certain elements making the table(s) update accordingly.

This would then be published to a small group of managers in the IT Dept and eventually will be combined with other stats from other systems such as our Telephony system etc. to create a single overall view for the IT Dept.

[TrevorKillick]

@samwoo sorry this is no help to you what so ever just wanted to say great work on those PowerBi pages they look amazing.

[samwoo]

Thanks @TrevorKillick, it's been quite the learning curve for sure but extremely powerful and compliments Hornbill extremely well  

[samwoo]

Hi @Steve G and @SamS,

I'm sorry to pick on you both like this, but I'm wondering if you or someone else can assist...

Please could we have Requests entity using EntityBrowseRecords2 to return the missing columns I mentioned below?

These are the columns that I need:

h_rating 
h_customer_type
h_ownertype 
h_ownername
h_fk_serviceid 
h_fk_servicename 
h_catalog_id
h_catalog 
h_resolvedby_user_id
h_resolvedby_username 
h_resolvedby_team_id
h_resolvedby_teamname
h_reopencount 
h_reopendate 
h_reopenedby_team_id 
h_reopenedby_teamname 
h_reopenedby_user_id 
h_reopenedby_username 
h_dateplacedonhold 

I think it is missing other columns as well for sure...

Basically, I have found a way to use to return prior years of "Closed" tickets (by using searchFilter and dynamically determining the year and it works well, allowing me to set up 6 queries to reflect the last 6 years (including the current year):

I am just missing some of the extra columns that are in the Entity Browser, but not being returned in the results of EntityBrowseRecords2 that would aid the Power BI Reports.

Thanks,

Samuel

[samwoo]

Good morning,

Has the request to enhance EntityBrowseRecords2 with extra columns for the Requests entity, been picked up internally?

Please can someone tag this request as an enhancement (and rename if required).

We would like to start reporting on the above columns using API (that only runs once a week) and use it in Power BI but have been unable to.

Thanks,

Samuel

[Steve Giller]

@samwoo The entity APIs are designed to be transactional and not for bulk data exports (they don't properly support pagination for a start, and are not optimised for use in this way...), so I expect that changes won't be made here as they serve their designed purpose.

The recommended implementation of this should be to not use the data::entityXYZ APIs for this, but to write the appropriate reports in Hornbill to return the required data, then use the reporting service APIs to run the report and download & import the content into Power BI when the report run is complete.

[samwoo]

Hi @Steve Giller,

Thank you for the clarification, it is a shame it's not designed for that purpose, BUT I completely understand why the need to not use this but using reports.

With that information you provided me since yesterday, I managed to use Microsoft PowerAutomate to run a report in Hornbill and download the .XLSX file directly into a folder within a SharePoint Document Library so the information can now easily be picked up in Power BI without having to do anything on premise or on a server.

I have made sure to reduce the number of requests over HTTP, especially when the flow checks the status of the running report (it will check every 20 seconds), and the flow will terminate if the running report fails as well for any reason. This is now scheduled to run a 1am every morning.

Thanks,

Samuel