Report Needed To List Users Who Are Not Following Ticketing Policy

[RTI_IT]

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

[RTI_IT]

This works! Thank you!!

[soucyinter]

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]

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]

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]

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]

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]

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