Jump to content

Lyonel

Hornbill Users
 View Profile  See their activity

  • Posts

    683

  • Joined

  • Last visited

  • Days Won

    25

 Content Type 

Posts posted by Lyonel

    % requests within resolution SLA

    in Reporting

    Posted

    Hi all,

    In my organisation, we are facing a curious situation where we are struggling to identify the best formula to calculate the percentage of requests resolved within the SLA on a monthly basis.

    What would be the best practice in your view? Which formula would you use?

    Option 1: (Total requests logged this month - Total requests breached this month) / Total requests logged this month

    Option 2: Total requests resolved this month with breach / Total requests resolved this month

    Tags not linked to documents?

    in Document Manager

    Posted

    Hi, I was testing something with the doc manager app the other day. Following my tests, I decided to do some cleaning and I noticed that tags associated to documents are not removed from the "Tags" section when the document is removed... Is this the intended behaviour? Because at the moment it is quite confusing.

    Use same table multiple times in single report?

    in Reporting

    Posted

    Hi,

    I am trying to build the following query using the report builder:

    SQL script: 

    SELECT 
SR.h_custom_a as responsible_team,
h_requesttype,
h_pk_reference, 
h_summary,
h_fk_user_name,
REQ.h_status,
h_fk_servicename,
h_catalog,
h_fk_priorityname,
h_fk_team_name,
h_ownername,
h_site,
h_source_type,
h_source_id,
h_datelogged,
h_dateresolved,
h_dateclosed,
h_reopencount,
h_isanalystunread,
h_withinresponse,
h_withinfix,
FirstTask.h_created_on as priority_confirm_created_on,
FirstTask.h_completed_on as priority_confirm_completed_on,
MSG.h_msg_date as email_date_received,
RESP.h_target_start as response_target_start,
RESP.h_target_completed_time as response_completed_time,
RESOL.h_target_start as resolution_target_start,
RESOL.h_target_completed_time as resolution_completed_time
FROM h_itsm_requests REQ
inner join h_itsm_services SR
    on h_fk_serviceid = h_pk_serviceid
left join (SELECT replace(h_obj_ref_urn,'urn:sys:entity:com.hornbill.servicemanager:Requests:','') as ref, h_created_on, h_completed_on FROM h_sys_tasks where h_title like '%Confirm Priority%'
and not h_completed_on is null) as FirstTask
    on FirstTask.ref = REQ.h_pk_reference
left join (SELECT h_msg_id, h_msg_date FROM h_msg_messages) MSG
    on REQ.h_source_id = MSG.h_msg_id
left join h_itsm_request_slm_targets RESP
    on REQ.h_pk_reference = RESP.h_request_id
    and RESP.h_type = 'Response'
left join h_itsm_request_slm_targets RESOL
    on REQ.h_pk_reference = RESOL.h_request_id
    and RESOL.h_type = 'Resolution'
where REQ.h_requesttype in ('incident','service request')

    Definition of my report:

    monitor-slas.report.txt

    Output of my report (based on log files): 

    SELECT h_itsm_requests.h_pk_reference,h_itsm_requests.h_catalog,h_itsm_requests.h_dateclosed,h_itsm_requests.h_datelastmodified,h_itsm_requests.h_datelogged,h_itsm_requests.h_dateplacedonhold,h_itsm_requests.h_dateresolved,h_itsm_requests.h_fk_priorityname,h_itsm_requests.h_fk_servicename,h_itsm_requests.h_fk_team_name,h_itsm_requests.h_fk_user_name,h_itsm_requests.h_isanalystunread,h_itsm_requests.h_iscustomerunread,h_itsm_requests.h_onholduntil,h_itsm_requests.h_ownername,h_itsm_requests.h_reopencount,h_itsm_requests.h_requesttype,h_itsm_requests.h_site,h_itsm_requests.h_status,h_itsm_requests.h_summary,h_itsm_requests.h_withinfix,h_itsm_requests.h_withinresponse,h_sys_tasks.h_created_on,h_sys_tasks.h_completed_on,h_msg_messages.h_msg_date,h_itsm_request_slm_targets.h_target_completed_time,h_itsm_request_slm_targets.h_target_start,h_itsm_services.h_custom_a  
FROM h_itsm_requests 
LEFT JOIN h_sys_tasks ON h_sys_tasks.h_obj_ref_urn LIKE h_itsm_requests.h_pk_reference 
LEFT JOIN h_msg_messages ON h_itsm_requests.h_source_id = h_msg_messages.h_msg_id 
LEFT JOIN h_itsm_request_slm_targets ON h_itsm_requests.h_pk_reference = h_itsm_request_slm_targets.h_request_id AND h_itsm_request_slm_targets.h_type = 'Response'  
LEFT JOIN h_itsm_request_slm_targets ON h_itsm_requests.h_pk_reference = h_itsm_request_slm_targets.h_request_id AND h_itsm_services. = 'Resolution' 
JOIN h_itsm_services ON h_itsm_requests.h_fk_serviceid = h_itsm_services.h_pk_serviceid 
WHERE ( h_itsm_requests.h_requesttype = 'incident'  OR h_itsm_requests.h_requesttype = 'service request' )  AND h_itsm_services.h_custom_a = 'Finance & HR'

    As you can see, I am really not far off! Only problem: I need to reference the same table twice (h_itsm_request_slm_targets) and this is where the engine fails I think. Indeed, if I just alias the tables (and tweak a little bit the select) it works just fine: 

    SELECT h_itsm_requests.h_pk_reference,
h_itsm_requests.h_catalog,
h_itsm_requests.h_dateclosed,
h_itsm_requests.h_datelastmodified,
h_itsm_requests.h_datelogged,
h_itsm_requests.h_dateplacedonhold,
h_itsm_requests.h_dateresolved,
h_itsm_requests.h_fk_priorityname,
h_itsm_requests.h_fk_servicename,
h_itsm_requests.h_fk_team_name,
h_itsm_requests.h_fk_user_name,
h_itsm_requests.h_isanalystunread,
h_itsm_requests.h_iscustomerunread,
h_itsm_requests.h_onholduntil,
h_itsm_requests.h_ownername,
h_itsm_requests.h_reopencount,
h_itsm_requests.h_requesttype,
h_itsm_requests.h_site,
h_itsm_requests.h_status,
h_itsm_requests.h_summary,
h_itsm_requests.h_withinfix,
h_itsm_requests.h_withinresponse,
h_sys_tasks.h_created_on,
h_sys_tasks.h_completed_on,
h_msg_messages.h_msg_date,
A.h_target_completed_time,
A.h_target_start,
B.h_target_completed_time,
B.h_target_start,
h_itsm_services.h_custom_a  
FROM h_itsm_requests 
LEFT JOIN h_sys_tasks ON h_sys_tasks.h_obj_ref_urn LIKE h_itsm_requests.h_pk_reference 
LEFT JOIN h_msg_messages ON h_itsm_requests.h_source_id = h_msg_messages.h_msg_id 
LEFT JOIN h_itsm_request_slm_targets AS A ON h_itsm_requests.h_pk_reference = A.h_request_id AND A.h_type = 'Response'  
LEFT JOIN h_itsm_request_slm_targets AS B ON h_itsm_requests.h_pk_reference = B.h_request_id AND B.h_type = 'Resolution' 
JOIN h_itsm_services ON h_itsm_requests.h_fk_serviceid = h_itsm_services.h_pk_serviceid 
WHERE ( h_itsm_requests.h_requesttype = 'incident'  OR h_itsm_requests.h_requesttype = 'service request' )  AND h_itsm_services.h_custom_a = 'Finance & HR'

    Is there something anybody can suggest? Or should I just give up and build 2 separate reports? One for response and one for resolution?

     

    Licenses for archived users?

    in Service Manager

    Posted

    Hi @TrevorKillick, we have a scheduled job running every night to sync our AD to Hornbill (using your LDAP import utility :) ).

    The job ran successfully and did the following:

    - Archived 2 accounts setup as "user" with rights on Service Desk app (2 guys left)

    - Created 2 new accounts setup as "user" with rights on Service Desk app (2 new guys started - replacing the 2 guys who left so exact same rights)

    - When the 2 new guys tried to log into Hornbill, they got the screenshot I attached earlier.

    During that period, I did not touch anything. However, this morning I demoted all archived users to "basic" as a workaround (I was not aware of the possibility to remove users from the subscription screen).

    Slideshow timeout?

    in Reporting

    Posted

    @Gerry, thanks for your reply. I checked the setting you mentioned (that's a cool one by the way, I like it!). I believe everything is ok:

    multilogon.png

    Also, the account we use for Dashboards display on TV is unique and that is his sole purpose. No other rights but display dashboards and slideshows.

    • Like 1

    assignment role for activities

    in System Administration

    Posted

    @Daniel Dekel, the allow our analysts to assign activities / tasks to "Roles", I have to give them the system right "Accounts: manage roles". But this is little bit too strong to my liking as they will also be able to create assignment roles themselves! Is there another way you can think of? Something where analysts can still assign activities to assignment roles BUT not have access to the admin side of things? 

    assignment role for activities

    in System Administration

    Posted

    Hi, I am looking at setting up a new assignment role to overcome a "problem" that we have in our organisation. Basically, we have teams (so far so good) amongst which some individuals have a particular skill set that allows them to perform certain specific tasks. The "problem" we have is when a request comes in and we need these people to help out, we want to be able to assign an activity to this group of people. But they belong to multiple teams... 

    So I tried to create a new assignment role and added 2 members (for testing purposes). I then created an activity and it worked beautifully. Only key problem: users are not able to select "Role" in the drop down when assigning an activity. They only get "User" and "Group".

    role activity.png

    Note that I am the super admin. Am I missing something? Is there a particular setting I missed? Any help would be appreciated.

×
×
  • Create New...