Help needed creating SQL query in CMDB to find user objects being related in a form via user picker
Ingrid_Glatz
Customer Adept IT Monkey ✭✭
Hi,
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.
Ingrid
Answers
No, not really. I would like to know whether a person who's about to leave is configured as a reviewer in any of these classes. Release Point class ist just an example, we have some more classes with user picker objects that are used for review activities. I'm not interested in any RAs. We need to know where to replace the user object of a leaver before the user object gets deleted and the value is empty. This causes new RAs to fail.
The goal would be to extend the leaver workflow to either use SQL or PS to check the relationship between leaver person (based on user or display name) and any of these classes. The simple way would be to create a monthly report or just SQL query where all type of reviewer classes (release points, app role approvals, and others) are listed together with their configured reviewers to check for the leaving person. Depending on the type of class, there are 2-4 review user objects based on either AD user or System user object.
It's hard to explain but I hope it's a bit more clear now.