→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
dfountain
Engaged Sweeper
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.
1 REPLY 1
Nick_VDB
Champion Sweeper III
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