SDM: How To Find All Users Who Are Not A Member of a Group
book
Article ID: 185818
calendar_today
Updated On:
Products
CA Service Desk ManagerSUPPORT AUTOMATION- SERVERCA Service Desk Manager - Unified Self ServiceCA Service Desk Manager - Mobile ApplicationCA Service Desk Manager - Xtraction
Issue/Introduction
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.
Environment
Release : 17.1
Component : SERVICE DESK MANAGER
Resolution
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
Additional Information
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.