Analyst and User login auditing script for Helpdesk/Supportcenter Classic

Version 3
    Question

    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.

    Answer

    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.

     

    sp_CustomerInactivityInLast 90

    GO

     

    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.

     

    sp_AnalystInactivityInLast 90

    GO

     

    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.

     

    Environment

    Helpdesk/Supportcenter All Versions