SDM: How To Find All Users Who Are Not A Member of a Group
Article ID: 185818
Updated On:
Products
CA Service Desk Manager
SUPPORT AUTOMATION- SERVER
CA Service Desk Manager - Unified Self Service
CA Service Desk Manager - Mobile Application
CA 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
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.
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.
Feedback
Yes
No
Powered by
