Jump to content

Everton1878

Hornbill Users
 View Profile  See their activity

  • Posts

    179

  • Joined

  • Last visited

  • Days Won

    1

 Content Type 

Posts posted by Everton1878

    Closure Categories

    in Reporting

    Posted

    Hi,

    Our closure categories have at least 2 levels and are separated by '>'

    i.e. Hardware > Fault PC or Hardware > Request New Hardware

    For some reason it doesn't always record the category with the correct spacing which makes reporting on the number closed per category a problem

    Sometimes it might record it as Hardware > Fault PC and other times as Hardware>Fault PC

    Pete

    email notifications when a call is assigned to a specified team

    in Service Manager

    Posted

    @Victor @Gary@ADL I've been looking at this again as two of our teams could do with email notifications when a call is assigned to their team

    Our other 3 teams wouldn't really need them but I suppose we could set up rules as Gary suggests

    Does it sent an email to each member of the team or can we set it to send to a distribution group which would be our preferred method?

    I saw another post that said that any out of office messages that came back from the team members would be added to the calls which isn't ideal

    Also, sometimes a customer updates the call when it is just assigned to the team and not an individual and nobody gets a notification about that

    I've seen instances where I've been asked a question about who has the call etc but because I've already reassigned it I don't see the update

    Looking at the settings it looks as though it is possible to set team notifications for updates to calls but we would only want that when it isn't assigned to an individual

    If it has been assigned to a team and then somebody accepts it and then the call is updated we would only want the analyst to be notified and not the whole team as we don't want to bombard the analysts with emails as they wont read them or may even set up a rule to delete all emails from service maanger

    SQL query join

    in Reporting

    Posted

    Hi @Bob Dickinson

    erm....

    I want all 100 results

    The problem is that there might be multiple questions per call but we're only interested in the h_answer field that matches "citrixserver" otherwise we will get multiple lines per call reference

    I've attached a spreadsheet which I hope might make things a bit easier to understand

    The first tab contains the results of the query on h_itsm_requests

    The second tab contains the results of the query on h_itsm_questions where h_question_id = "citrixserver"

    The third tab shows that there might be multiple answers per call reference

    I just want to link the data in the first tab with the data in the second tab showing all of the data in the first tab and the data from the second tab where the reference number matches

     

    I'm not sure if it can be done in one single query, it might be that I have to do 2 queries and maybe a lookup in Excel

    In SQL you might create a view for h_itsm_questions where h_question_id = "citrixserver" and link that to your main query

    I hope the spreadsheet helps it make more sense

     

     

     

     

    SQL join problem.xlsx

    SQL query join

    in Reporting

    Posted

    Hi,

    I'm trying to do a SQL query in the Database Direct with a join but I'm having a bit of a problem

    I want to return all data in h_itsm_requests that matches records in h_itsm_questions where h_question_id = 'citrixserver' but I'm not getting any data

    It's either an issue with the joining or because I have criteria on both tables

    Quote

    SELECT h_pk_reference, h_summary, h_description, h_fk_user_name, h_status, h_dateresolved, h_dateresolved, h_ownername, h_fk_team_name, h_closure_category, h_resolution, h_answer FROM h_itsm_requests INNER JOIN h_itsm_questions on h_pk_reference = h_entity_ref WHERE h_question_id = 'citrixserver' AND h_closure_category like 'Citrix%' AND (h_dateresolved >= LAST_DAY(NOW() - INTERVAL 2 MONTH) + INTERVAL 1 DAY AND h_dateresolved < LAST_DAY(NOW() - INTERVAL 1 MONTH))

    This next one returns 52 records but there should be 134, it's only returning records where the criteria on both tables is matched

    I've tried left join and right join but it hasn't made a difference

    Quote

    SELECT h_pk_reference, h_summary, h_description, h_fk_user_name, h_status, h_dateresolved, h_dateresolved, h_ownername, h_fk_team_name, h_closure_category, h_resolution, h_answer FROM h_itsm_requests inner join h_itsm_questions on h_pk_reference = h_entity_ref WHERE h_question_id = 'citrixserver' AND h_closure_category like 'Citrix%' AND (h_dateresolved >= LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY AND h_dateresolved < LAST_DAY(NOW()))

    In need to return everything that matches the following query

    Quote

    SELECT h_pk_reference, h_summary, h_description, h_fk_user_name, h_status, h_dateresolved, h_dateresolved, h_ownername, h_fk_team_name, h_closure_category, h_resolution FROM h_itsm_requests WHERE h_closure_category like 'Citrix%' AND (h_dateresolved >= LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY AND h_dateresolved < LAST_DAY(NOW()))

    and return h_answer from h_itsm_questions where h_question_id = 'citrixserver'

     

    Can anyone help?

    This level of SQL is a bit beyond me

    Unable to replicate the following reports

    in Reporting

    Posted

    I've been asked if we can do something else as well but I don't think it's possible. I think I'll have to create separate widgets for it

    We currently have some measures that look at the number of opened and closed calls daily and the number of calls outstanding per month

    Those measures will run daily and monthly respectively

    I've been asked if they can show the info for the current day and month respectively and update every 15 minutes

    I'm going to create a separate widget for this

    Capture.JPG

    Unable to replicate the following reports

    in Reporting

    Posted · Edited by Everton1878
    lost my images when posting

    We currently do a CSV export of all calls logged for the last 2 years, import it into MS Access, run various queries and export to Excel to produce our graphs

    I've been able to replicate some of our current graphs in the advanced analytics but I'm stuck with the following ones

    Open Vs Closed Tickets with Queue Impact

    I can get the red and the green bars onto a graph but I can't add the line graphs to the same graph. We also need to be able to add trend and target lines

    Outstanding Tickets by Age

    I might be able to do this, I think I would need to create a measure for each range of days

    We also like to be able to do a pie chart per team i.e. Development, Engineering, Infrastructure and Service Delivery

    Calls Opened by Site

    I couldn't get it to show the sites along the bottom instead of the months

    Opened v Resolved by Team Previous Month

    I can do this one with the exception of combining our two service delivery teams into one as it is grouped by team

    We have a Service Delivery Reception and Service Delivery Escalation team but we tend to report them together as Service Delivery

    Resolved Calls by Resolution Type Previous Month

    We can pull out the closure profiles but we only want to report on the top level

    Calls may be closed as 4CS > Fault or 4CS > Request but we just want to report 4CS

    If we can do that, then I also want to be able to do for the last 3 months, so we'd have one bar for each profile per month

    Closed by Team (In and Out of Fix)

    I think I can probably do the smaller pie charts but not sure how to do the bigger one with the multiple teams and the percentage of calls closed in and out of fix for each

    Percentage of Calls Closed Within Fix

    I've just managed to get close to this but the percentages are a bit out

    In our current reports we just look at closed and resolved calls but when I put that criteria into the measure the percentages are way out

     

    I've not tried the average fix time yet but I think I should be able to do that one

     

    Open Vs Closed Tickets with Queue Impact.JPG

    Outstanding Tickets by Age.JPG

    Calls Opened by Site.JPG

    Opened v Resolved by Team Previous Month.JPG

    Resolved Calls by Resolution Type Previous Month.JPG

    Closed by Team (In and Out of Fix).JPG

    Percentage of Calls Closed Within Fix - All.JPG

    Escalating calls to other teams

    in Reporting

    Posted

    Hi,

    Is it possible to report on how many calls are passed from one team to another per day?

    90% of our calls will initially go into our 1st line queue. If they can't fix the issue then it is escalated to our 2nd line queue

    If 2nd line can't fix it then it is escalated to one of our specialist queues i.e. Development, Infrastructure or Engineering

    We'd like to be able to see how many calls are passed each day to our Development and Infrastructure teams

    I have a couple of measures set up that run daily but the data isn't accurate

    Thanks,

    Pete

     

    Capture.JPG

    Current and previous month

    in Reporting

    Posted

    Thanks @Bob Dickinson that will help me with a few other graphs as well :)

    I finally had some time yesterday to look through what I currently produce using an export from a view and what we have set up in the Advanced Analytics

    Think I managed to get to grips with a measure for a percentage yesterday as well

    Hopefully I'll be posting soon about the ones I don't think we can do

    Current and previous month

    in Reporting

    Posted

    Does anyone know the SQL (or MySQL?) I can use to report on the current month and for the previous month?

    I've been asked to do a graph that would show the rating on calls for the current month and previous month on the same graph

    It would be something along the lines of

    Quote 
    
WHERE DATEPART(m, date_created) = DATEPART(m, DATEADD(m, -1, getdate()))
AND DATEPART(yyyy, date_created) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

    Thanks,

    Pete

    On Holiday - backup person

    in Service Manager

    Posted

    People should set that status when they're away

    We have too many open requests to re-assign them

    Sometimes we get people chasing calls and when we find them we find the delay is that the person is on holiday

    If we could get more people using the portal we could look at putting calls on hold saying that we're on holiday

    request list auto refresh

    in Service Manager

    Posted

    @James Ainsworth it would be great if you could introduce some sort of auto refresh periodically to the list as long as it doesn't refresh when you have a call open and are in the middle of entering some text

    It can be a bit of a shock sometimes when you manually refresh or go into and out of a call and find you suddenly have 5 extra calls :o

    • Like 1

    On Holiday - backup person

    in Service Manager

    Posted

    I was wondering about the possibility of being able to set a backup person for when you're on holiday etc

    If someone is on holiday and a user updates a call that update probably wont be seen until the analyst comes back in

    It could be something urgent and the customer is getting frustrated about the lack of a response

    If you could select a backup person when you change your status to on holiday etc then that person could receive any call updates

×
×
  • Create New...