Report Query

[AndyColeman]

Good morning,

I run regular reports for weekly, monthly and yearly stats for our service desk team. 

We have had one of the team leave and while they still have an active hornbill account they are no longer set up on our system (de-provisioned) 

They now no longer appear in the Hornbill reports which is concerning me as this person was one of our chief call closers. It is not that his name is not present in the report but his calls are not showing so I now have a big gap of data which when I come to do my yearly report I am concerned I will not be giving the board correct facts of how many tickets we have done. 

I am hoping this chunk of data is not lost as I believe he has 800+ tickets in his name. 

Any help would be greatly appreciated.

Many thanks,

Andy

[Conor]

Hi Andy

 

The data is definitely not lost, it will be stored in the database so it sounds like we need to simply update the filter on the report to accomodate all analysts.

Firstly how are you running this report - via the personal dashboard within the Service Desk view, the reporting suite in the administration portal, or the advanced analytics?

Thanks

Conor

[AndyColeman]

Hi Conor,

Through the reports in the admin portal. 

Thanks,

Andy

[Conor]

Brilliant thanks for confirming, can you please export the report definition file from the report in admin tool so I can import it into my demo system and compare results. If I can see anything obvious I will update and send it back, otherwise I may need to ask for some further info to make sure we are on the same page.

To export a report click on the blue down arrow in the top right of the individual report, and that will download a .xml file, you can drag the file to your post on here.

Thanks

Conor

[AndyColeman]

Hi Conor, please see attached. I notice it has come through as text rather than xml? 

It is James Southall who is missing from the report.

Thanks

total-resolved-between-date-range.report.txt

[Conor]

Hi Andy

Thanks for this, that worked fine I can import it in no problem. 

Just to confirm when you say you have de-provisioned your user, you have archived the user in Service Manager?

The report looks fine, I ran it once without changing anything and then ran it again after archiving a particular user and the tickets against the archived user are still appearing in the report. To be fair it shouldn't make a difference to this report other than if the user was removed from Service Manager then the user ID field will be blank... So you did mention that the user still has an active account in Service Manager, but you cannot see that user against any of the tickets on the report anymore?

Thanks

Conor

[AndyColeman]

Hi Conor,

The report I do is a yearly summary of tickets using the report I sent. I am guessing you will not be able to see anything for James Southall or see any unallocated tickets. 

James still has a licence on the system and has not been archived in HB. His user account on our AD has been deprovisioned. I regard to his account on HB nothing has been touched since he has left. 

Any help in getting me the missing data in the report would be appreciated as I have a presentation on Monday and would like to include accurate stats. 

 

Thanks. 

[Conor]

Hi Andy

Can you log in as administrator on the admin.hornbill.com portal, go to System -> Data -> Database Direct and copy and paste in the below query. Please change the limit (just under the query box, but above the results) to 1000 from 25. 

It could just be that the date that the tickets were resolved are before the start of the date range added into the report. This query will show all the tickets that James has resolved since you started using the system. If you look at the dates in the date resolved field (the most recent will be at the top) do these dates and times correspond to the dates and times provided against the reports? The data will be in the database, but if the last call James resolved was in September, but the report is only looking at calls resolved since the start of October then they will not show on the report. If you then try it the other way around and the last call James resolved was in September but the report is for all calls since August, James' calls will be in the report. 

Give this a try and if you still cannot see the calls in the report let me know and we will set up a remote session to look in more detail and get it sorted once and for all.

SELECT h_pk_reference, h_summary, h_status, h_dateresolved, h_resolvedby_username, h_resolvedby_teamname FROM h_itsm_requests where h_resolvedby_username = 'James Southall' order by h_dateresolved desc

Thanks

Conor

[AndyColeman]

Morning Conor,

Indeed I can see James stats in there using that query. 

The report I am trying to run is from March to November 'Total Resolved between Date Range' and when I run this report it still does not show the data for James. However it does show data for another analyst who has left.The other user who has left is displaying 98 tickets. Nothing is showing for James. 

If I run a report between March-May I can get the information for James and the other user who has left. Just does not seem to like James in the overall report. 

If someone could please log in and assist me that would be appreciated as I really need this data today before I present on Monday. I just want to make sure the data is accurate. 

Many thanks,

Andy

 

[Guest Ehsan]

Hi @AndyColeman,

Thank you for providing us with details regarding your Report and apologies for the inconvenience that you are experiencing.

I have run your Report on my instance, selected March to November when prompted for date range and I can see Requests that were resolved in March - for example. I did notice in your query that there is a filter on "h_reopencount" which I am convinced that you are aware of.

To investigate this further, we will need to access your instance and review your Report and the generated logs when this Report is run. One of my lovely colleagues at Hornbill will be in touch with you.

As a quick workaround for Monday, you can run the following query through Admin Tool > System > Data > Database Direct - and export the results in CSV using the option provided. You can then use Spreadsheet to create a Chart from the results in the generated CSV file. This is only so that you could go ahead with your presentation on Monday while we investigate this for you and help you to resolve it.

SELECT h_itsm_requests.h_pk_reference, h_itsm_requests.h_summary, h_itsm_requests.h_status, h_itsm_requests.h_dateresolved, h_itsm_requests.h_resolvedby_user_id FROM h_itsm_requests 
WHERE h_itsm_requests.h_status NOT IN ('status.cancelled', 'status.open', 'status.new', 'status.on-hold') AND h_itsm_requests.h_dateresolved BETWEEN '2016-03-01 00:00:00' AND '2016-11-12 23:59:59' 
AND h_itsm_requests.h_reopencount < '1' AND h_resolvedby_username = 'James Southall' ORDER BY h_itsm_requests.h_resolvedby_user_id ASC LIMIT 500

While we proactively monitor the Hornbill Forums to assist everybody with their questions, you may be interested in our Platinum Success Plan or Premier Success Plan where you may receive assistance or submit requests through other channels to report any issues that prevent you from using Hornbill. We monitor requests that are received and aim to provide a meaningful response within the defined Service Levels.

Thanks,

Ehsan

[AndyColeman]

Thanks Ehsan.

[Guest]

Hi @AndyColeman

I have investigated this issue for you and there appears to be a limitation with the graph that displays on a standard report. Its seems what is happening is that the visual chart shows the top 20 results alphabetically. When running the report with you timeframes of previous months, e.g. Mar to Nov, there were either fewer than 20 individuals who had resolved calls, or "SouthallJ" was in the top 20 people returned alphabetically. 

However in October, a new user whose surname begun with "A" resolved a call - this caused "southallJ" to fall out of that top 20 and not be displayed in the chart. You may also notice that 3 other users are not displayed on the chart (with surnames of Stevens, Woods, and Yeong). 

This is actually a configuration setting you can change in the Chart Options - simply increase this, which you currently have set to 20, to something higher (maybe 50) and all of the users should appear on your chart correctly:

 

Kind Regards

Bob