Jump to content

Searching in Service Manager - differing results


Jeremy
 Share

Recommended Posts

So when we create a view to search for data verses the database we get different results which is confusing us as to which is the correct information, can anyone shed any light as to what we are doing wrong?

View

image.thumb.png.3d85e03386c9bcdcdbf4d73a99854136.png

The resolution text in the box is 'After clicking on the option Login into the Applicant View you will be able to login with the below credentials:', this gives184 results

Database

When we do the same in the database we get 153 results:

image.png.f07b9ef2fe923360417704ad0f37e195.png

This is the database search:

SELECT * FROM h_itsm_requests where h_resolution like '%After clicking on the option Login into the Applicant View you will be able to login with the below credentials:%' and h_datelogged <= '2021-01-01 00:00:00' and h_requesttype = 'Incident'

Link to comment
Share on other sites

The views also do not include the cancelled requests which are included in the SQL results. 

The cancelled requests need to be explicitly included in the view criteria. 

Link to comment
Share on other sites

There are no security models that would prevent us from seeing these other requests against these services and i have checked there are no cancelled requests.

I have updated the sql to this:

SELECT * FROM h_itsm_requests where h_resolution like '%After clicking on the option Login into the Applicant View you will be able to login with the below credentials:%' and h_datelogged >= '2021-01-01 00:00:00' and h_requesttype = 'Incident' and h_status in ('status.closed', 'status.open', 'status.onhold')

and now there are 192 records.

Link to comment
Share on other sites

@Jeremy

The SQL includes cancelled requests, therefore the query you need on the SQL to match what you have in your view is 

SELECT count(h_pk_reference) FROM h_itsm_requests where h_resolution like '%After clicking on the option Login into the Applicant View you will be able to login with the below credentials:%'  AND h_datelogged >= '2021-01-01 00:00:00' AND  h_requesttype = 'Incident' AND h_status != 'status.cancelled'

 

Link to comment
Share on other sites

@Mary thanks I have updated and this shows 206 results, I have now been through all the requests that are not in the view and there is no reason why they shouldn't show in the view.

I have exported the data and compared it and there are no differences to other requests that are returned that I can see, these 22 requests seem to be missing when creating this view.

Link to comment
Share on other sites

@Jeremy first you need to confirm the same criteria is being used when comparing the results on the views with the SQL results.

Could you please provide a screenshot of the view you have created and a screenshot of the SQL query you ran which shows a difference of 22 requests? 

I can confirm this works correctly on my instance which suggests you might not be using the same criteria for the comparison or you don't have the security permission to view the 22 requests.

Link to comment
Share on other sites

As @Mary suggested, there is the visibility (security) aspect that one needs to keep in mind when looking at the request list:

"Along with any conditions you specify, the views automatically take into account your team membership and the Services that your team supports. If you are not a member of a team, or not part of a team that supports the service against which the request is raised, then that request will not be visible to you." (https://wiki.hornbill.com/index.php?title=Request_List_Views)

Therefore comparing request list view with SQL results might not always be accurate as the SQL might not take into account that visibility aspect.

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...