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 Manager SUPPORT AUTOMATION- SERVER CA Service Desk Manager - Unified Self Service KNOWLEDGE TOOLS 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

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.