Help needed creating SQL query in CMDB to find user objects being related in a form via user picker
we have different classes (approx.7-8) using TypeProjections where the user picker is used in the form of this class to fill in either an AD User or System User object.
Example: we have a class for release points, the view in the console displays name and ID of the release points and the persons who are responsible for them. These release points are used for review activities. Some have only 2 reviewers, others have 4 reviewers, based on their usage. Other classes contain 3 different objects for chief, deputy and implementer.
Now, when a user leaves the company, nobody knows whether this person is used as a reviewer person somewhere or not. Is there a possibility to query the database for the relationship between this person (username) and an entry as a reviewer in one of those classes? Would it be possible to query the views in the console being created with Cireson view builder?
I've searched through the tables and views in CMDB. The DB tables of the classes contain their values like ID, name, code, whatever else is used to fill out the form. The users are not listed in this table, neither with name nor an ID. The corresponding DB views (e.g. ciclassreleasepointdeputyhead) where I would except to find the user names only have a RelationshipId, SourceTypeId and TargetTypeId. I didn't succeed in any way to figure out where to find proper information regarding the classes in combination with the selected users. In case of the release point class, SourceTypeId and TargetTypeId are always the same for all 4 different DB views, only the RelationshipID is different.
Which views/tables are required for a join statement to get proper data? Maybe it's not possible to create a SQL query with this information.
I hope it's clear what I mean? Thanks.