search Searching in Service Manager - differing results

[Jeremy]

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

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:

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'

[Alberto M]

@Jeremy,

Check the "after" date in the filters vs. the "<=" in the SQL?

 

[Jeremy]

@Alberto M if I use <= or < it doesn't seem to make a difference also we were closed on the 1st so there would have been no jobs logged regarding this.

[Alberto M]

@Jeremy, you are getting the requests after 1/Jan in the filter, but you get the requests before 1/Jan in the SQL.

 

[Jeremy]

@alberto thanks so if I update the SQL it changes to 208 requests and the view is 184 so still not right?

[Steve Giller]

@Jeremy the view applies the security model so will not see Requests if you do not support those Services.

[Guest Mary]

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. 

[Jeremy]

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.

[Guest Mary]

@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'

 

[Jeremy]

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

[Guest Mary]

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

[Victor]

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.