Help Desk Reports

[dfountain]

Hi,

I'm not familiar with SQL queries and I'm needing a few reports for help desk metrics.

All reports below are based on our fiscal year (July 1 - June 30).

Also, we have an SLA created in the help desk with 2 hour initial response and 1 day for resolve time.

First report - % calls responded to within two hours (business hours M-F 8-5).

Second report - % calls resolved within 1 day of receipt (exclude weekends).

Final report - % calls reopened.

Thanks to anyone who can help me with the syntax/reporting.

[Nick_VDB]

This forum post was answered to by mail.

You can use the reports below to get the information that you are after. These reports are more advanced and not fully refined so the data may not always be exactly as you would want.

As for the third report, there is currently no field that will give back if a ticket was first closed and then opened again. This can be done with the use of the htblhistory table and checking when a ticket was closed, then if another action occurred in the history of the ticket after the initial close and then if the ticket was closed again.

This would require a complex implementation to get the information that you are after as well as advanced techniques in SQL. Because of this complexity we cannot provide support in creating this report.

Instructions for adding these reports to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

% calls responded to within two hours

Select Top 1000000 TicketsInSLA.TicketsInSLA,
  TotalFiscalTickets.[Total Tickets Fiscal Year],
  Cast((Cast(TicketsInSLA.TicketsInSLA As Float) /
  Cast(TotalFiscalTickets.[Total Tickets Fiscal Year] As Float)) *
  100 As NVARCHAR) + '%' As Percentage
From (Select Top 1000000 Count(htblticket.ticketid) As TicketsInSLA
  From htblticket
    Inner Join (Select Top 1000000 htblticket.ticketid As ID,
      Min(htblnotes.date) As [Initial Response Date]
    From htblticket
      Inner Join htblnotes On htblticket.ticketid = htblnotes.ticketid
      Inner Join htblagents htblagents1 On htblnotes.userid = htblagents1.userid
    Where (htblnotes.date <= htblticket.slainitial And htblnotes.notetype = 1
      And DatePart(mm, htblticket.date) >= 6 And DatePart(yy, htblticket.date) =
      2016) Or
      (DatePart(mm, htblticket.date) <= 7 And DatePart(yy, htblticket.date) =
      2017)
    Group By htblticket.ticketid
    Order By ID) As TicketInSLACount On htblticket.ticketid =
      TicketInSLACount.ID) As TicketsInSLA,
  (Select Top 1000000 Count(htblticket.ticketid) As [Total Tickets Fiscal Year]
  From htblticket
  Where (DatePart(mm, htblticket.date) >= 6 And DatePart(yy, htblticket.date) =
    2016) Or
    (DatePart(mm, htblticket.date) <= 7 And DatePart(yy, htblticket.date) =
    2017)) TotalFiscalTickets
 Go to top

% calls resolved within 1 day of receipt

Select Top 1000000 TicketsInSLA.TicketsInSLA,
  TotalFiscalTickets.[Total Tickets Fiscal Year],
  Cast((Cast(TicketsInSLA.TicketsInSLA As Float) /
  Cast(TotalFiscalTickets.[Total Tickets Fiscal Year] As Float)) *
  100 As NVARCHAR) + '%' As Percentage
From (Select Top 1000000 Count(htblticket.ticketid) As TicketsInSLA
  From htblticket
    Inner Join (Select Top 1000000 htblticket.ticketid As ID
    From htblticket
      Inner Join htblnotes On htblticket.ticketid = htblnotes.ticketid
      Inner Join htblagents htblagents1 On htblnotes.userid = htblagents1.userid
      Inner Join htblticketstates On htblticketstates.ticketstateid =
        htblticket.ticketstateid
    Where (htblticket.updated <= htblticket.slaresolved And
      DatePart(mm, htblticket.date) >= 6 And DatePart(yy, htblticket.date) =
      2016 And (htblticket.slaresolved != '' Or htblticket.slaresolved
        Is Not Null) And htblticketstates.statename = 'Closed') Or
      (DatePart(mm, htblticket.date) <= 7 And DatePart(yy, htblticket.date) =
      2017)
    Group By htblticket.ticketid
    Order By ID) As TicketInSLACount On htblticket.ticketid =
      TicketInSLACount.ID) As TicketsInSLA,
  (Select Top 1000000 Count(htblticket.ticketid) As [Total Tickets Fiscal Year]
  From htblticket
  Where (DatePart(mm, htblticket.date) >= 6 And DatePart(yy, htblticket.date) =
    2016) Or
    (DatePart(mm, htblticket.date) <= 7 And DatePart(yy, htblticket.date) =
    2017)) TotalFiscalTickets