SDM: How To Find All Users Who Are Not A Member of a Group
Article ID: 185818
CA Service Desk ManagerSUPPORT AUTOMATION- SERVERCA Service Desk Manager - Unified Self ServiceKNOWLEDGE TOOLSCA Service Desk Manager - Mobile ApplicationCA Service Desk Manager - Xtraction
This article discusses a SQL Query that can be used to detect which users (including Help Desk users and Analysts) who are not members of any groups within Service Desk.
Release : 17.1
Component : SERVICE DESK MANAGER
The following query returns a list of all contacts of type User, Help Desk, and Analyst that are not members of a given group:
select [userid], [last_name], [first_name], [contact_uuid] FROM [mdb].[dbo].[ca_contact] where inactive = 0 and [contact_type] IN (2302, 2306, 2307) and [contact_uuid] NOT IN (SELECT [member] FROM [mdb].[dbo].[grpmem])
Note that for Contact Type id correlation: User: 2302 Help Desk: 2306 Analyst: 2307
The above query would be executed direct on the SQL Server MDB database via SQL Server Management Studio.
Please be cautious when running any queries direct to the MDB database. Improper execution may cause a lockout of the database for the Service Desk application.