Count of users by type and deleted status.

Version 1
    Question

    User licence count breached:

    This script will identify users of all licence user type. including a count of soft-deleted and active.

    Answer

    -- ITBM Users grouped and counted by USER TYPE & SOFT DELETED Status(1=yes).

    -- Written by CJH 17/11/2006

     

    SELECT (b.tps_name) as , a.tps_name AS , a.tps_title as , a.tps_deleted as

    FROM         tps_user a right outer JOIN

                          tps_user_type b ON a.tps_user_type_guid = b.tps_guid

    group by b.tps_name, a.tps_name, a.tps_title, a.tps_deleted

    order by  4, 1, 3

    COMPUTE count(b.tps_name) by a.tps_deleted, b.tps_name

    Environment

    servicedesk v7.1.2 onwards.