Jump to content

Enhancement request to smGetRequests API


Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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
image.thumb.png.bde6bd9ffefe2efb46da03ab18553f79.png
I think eventually we'll restrict the number of years to 5 years prior or something to that effect.

image.thumb.png.fafc169db8e1ca6574bfa0988aaadf2c.png

 

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.

image.png

Link to comment
Share on other sites

  • 2 weeks later...

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):
image.png.88fb064255c0d70634033558dc4141c4.png

image.png.3b18697a3e845bb7861b32db55882cf2.png

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...