Would like to know which analysts and customers have not logged in to Helpdesk Internet edition or have not had a call logged for them within the last n days. This is useful to determine which analysts and users can be deleted due to inactivity.
This script will setup auditing on the analyst and customer login tables. It will create a couple of stored procedures which will allow you to see which analysts and customers have not logged in for a specified number of days.
Run the attached script to create the tables, triggers and stored procedures. The script will run the stored procedures but the data returned will not be valid as the new login audit tables will not have been populated yet.
The stored procedures can be run in query analyzer using the following:
Run the stored procedure sp_CustomerInactivityInLast with the parameter set to 90 to show all customers who have not logged in or had a call logged in the last 90 days e.g.
Run the stored procedure sp_AnalystInactivityInLast with the parameter set to 90 to show all Analysts who have not logged in the last 90 days. Analysts who have not logged in since the audit tables were created will also be displayed e.g.
It is advisable to run the stored procedures out of hours as they can be quite resource intensive. After running the initial script, you will need to wait the number of days you specify as a parameter for the stored procedure for the data to be accumulated. e.g if you want to see analysts or customers who have not logged in or had calls logged for them in the last 5 days then you will need to wait 5 days before the stored procedures return complete data.
Helpdesk/Supportcenter All Versions