cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
RTI_IT
Champion Sweeper
Good morning and Happy New Year!!

It has come to my attention that a lot of my users have stopped creating tickets and instead have resorted back to phoning or e-mailing our agents directly. I'd like to work with our HR dept to curve this behavior in 2017. I need a report that shows which users have tickets (in any state) that were created by someone other than themselves.

For example if a user calls an agent and the agent creates a ticket for them, that should be on the report.

In all, I would like it to show;
User, Ticket #, Ticket creator, Ticket Date.

Your assistance in this matter is very much appreciated!

Thanks you,
-Ed
7 REPLIES 7
RTI_IT
Champion Sweeper
This works! Thank you!!
soucyinter
Engaged Sweeper III
The reason why it doesn't work is way beyond my SQL knowledge but...
I do know that if you remove the "OR", it works.. but then you only get either the TypeID 31 or 13.

At least, it does give you the tickets after 2017-01-01.

Select Top 1000000 htblusers1.name As [Ticket User],
htblticket.ticketid As [Ticket ID],
htblusers.name As [Ticket Creator],
htblticket.date As [Creation Date],
htblhistorytypes.name As [Creation Type]
From htblhistorytypes
Inner Join htblhistory On htblhistorytypes.typeid = htblhistory.typeid
Inner Join htblticket On htblticket.ticketid = htblhistory.ticketid
Inner Join htblusers On htblusers.userid = htblhistory.userid
Inner Join htblusers htblusers1 On htblticket.fromuserid = htblusers1.userid
And htblhistory.userid != htblusers1.userid
Where htblticket.date > '2017-01-01 00:00:00.000' And htblhistorytypes.typeid = 13
RTI_IT
Champion Sweeper
Hmmm, this seems to to return the same results (900+ tickets from 2016). I'm at a loss as to how to filter this down.
RTI_IT
Champion Sweeper
Thank you, this is very close to what I was looking for! What would need to be done to limit the date ranges? When I run this as is it's showing tickets dating back to when we first started the Beta, really we are only interested in tracking the current year (2017).

Thank you so much,
-Ed
ProfileNL
Engaged Sweeper III
RTI_IT wrote:
Thank you, this is very close to what I was looking for! What would need to be done to limit the date ranges? When I run this as is it's showing tickets dating back to when we first started the Beta, really we are only interested in tracking the current year (2017).

Thank you so much,
-Ed


Select Top 1000000 htblusers1.name As [Ticket User],
htblticket.ticketid As [Ticket ID],
htblusers.name As [Ticket Creator],
htblticket.date As [Creation Date],
htblhistorytypes.name As [Creation Type]
From htblhistorytypes
Inner Join htblhistory On htblhistorytypes.typeid = htblhistory.typeid
Inner Join htblticket On htblticket.ticketid = htblhistory.ticketid
Inner Join htblusers On htblusers.userid = htblhistory.userid
Inner Join htblusers htblusers1 On htblticket.fromuserid = htblusers1.userid
And htblhistory.userid != htblusers1.userid
Where htblticket.date > '2017-01-01 00:00:00.000' And (htblhistorytypes.typeid = 13) Or
(htblhistorytypes.typeid = 31)

If someone has a better solution for this, please post it!
Nick_VDB
Champion Sweeper III
We have added an example report that should give back the information that you are after. The tests in our helpdesk did give back the wanted results.

Instructions for adding this report 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.

Select Top 1000000 htblusers1.name As [Ticket User],
htblticket.ticketid As [Ticket ID],
htblusers.name As [Ticket Creator],
htblticket.date As [Creation Date],
htblhistorytypes.name As [Creation Type]
From htblhistorytypes
Inner Join htblhistory On htblhistorytypes.typeid = htblhistory.typeid
Inner Join htblticket On htblticket.ticketid = htblhistory.ticketid
Inner Join htblusers On htblusers.userid = htblhistory.userid
Inner Join htblusers htblusers1 On htblticket.fromuserid = htblusers1.userid
And htblhistory.userid != htblusers1.userid
Where (htblhistorytypes.typeid = 13) Or
(htblhistorytypes.typeid = 31)
dpoll7428
Engaged Sweeper II
Nick.VDB wrote:
We have added an example report that should give back the information that you are after. The tests in our helpdesk did give back the wanted results.

Instructions for adding this report 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.

Select Top 1000000 htblusers1.name As [Ticket User],
htblticket.ticketid As [Ticket ID],
htblusers.name As [Ticket Creator],
htblticket.date As [Creation Date],
htblhistorytypes.name As [Creation Type]
From htblhistorytypes
Inner Join htblhistory On htblhistorytypes.typeid = htblhistory.typeid
Inner Join htblticket On htblticket.ticketid = htblhistory.ticketid
Inner Join htblusers On htblusers.userid = htblhistory.userid
Inner Join htblusers htblusers1 On htblticket.fromuserid = htblusers1.userid
And htblhistory.userid != htblusers1.userid
Where (htblhistorytypes.typeid = 13) Or
(htblhistorytypes.typeid = 31)



This report is very helpful, however, I am only getting 600 tickets returned out of 798 that have been created. Also, if there is a way to pull up a certain date range, that would be helpful. I would like to do this by week and month.

Thanks,
Dave