search cancel

Query for ITAM Users and Roles

book

Article ID: 229900

calendar_today

Updated On:

Products

CA Service Management - Asset Portfolio Management

Issue/Introduction

What database query can be used to retrieve a list of IT Asset Manager (ITAM) users and their respective roles?

Environment

IT Asset Manager (ITAM) 17.x

All Supported SQL Database Management Systems (DBMS)

Resolution

The following queries will return a list of ITAM users and their respective roles:

1.  ITAM Users

select c.userid
from al_authorized_user a, ca_contact c
where a.authorized_contact_uuid = c.contact_uuid

2.  ITAM Users and their Roles

select first_name, last_name, c.userid, r.role_name
from al_authorized_user a, ca_contact c, al_role_def r, al_link_role_contact l
where a.authorized_contact_uuid = c.contact_uuid and a.authorized_contact_uuid = l.contact_uuid and r.role_id = l.role_id

The above queries can be altered to return additional information as required