Imitating the User Roles Field Type

[JosephW1]

The User Roles Field Type

Take a look at the sys_user.roles field definition and you'll notice its type is "User Roles".
Perform a "Roles = report_admin" query on the sys_user table and you'll notice that that field imposes a "Sys ID > IN > [sys_users who have said role in the sys_user_has_role table]" condition to the query.

My Goal
My customer has a business need to be able to query for cmdb_ci_computer records based on historical hostnames. They want convenient cross-referencing fueled by ServiceNow's CMDB. Thankfully, we already have the historical hostnames in the sys_audit table since we already have that table audited.

I'm trying to avoid both A) exposing the sys_audit table and B) creating new records - such as creating metric_instances via a metric_definition for cmdb_ci_computer.name.

I'm wondering if I can create a "Previous Names" column that, when a value is entered into it during a query, it does a lookup in the sys_audit table for said oldValue & returns all of the related document IDs into a "Sys ID > IN > [returnedValues]" condition. This is all similar to the behavior of the aforementioned "User Roles" field type when it is utilized as a query condition.

I took a look at the User Roles field type but did not see a way to imitate it.


Is it possible to reproduce custom variants of this behavior type into our own custom fields? If that is unknown, do you have any other suggestions? Thanks!

[JosephW1]

I found a workaround in the method shown in the below GIF.

HOWEVER, I'd still prefer a User Roles field type imitation, since it would be a more centralized and flexible solution that could be utilized from any condition builder; list view, report builder, business rules, ui policies, etc, etc. Though half of those examples aren't relevant in my current use case, it'd still be a nice option to have available in my future solutioning. So, if you know how to imitate it, please advise and I'll happily mark your answer as correct and helpful and give you lots of points. 🙂

This below workaround is rigid in that it only works from its own UI page. It does seem to have a bit of its own finesse, though. Its concept is simple - a GlideAjax facilitated GlideAggregate of the sys_audit table that returns the sysIDs of the relevant computers which is then used to load a list view query in an iframe - so I won't lengthen this answer with the nitty gritty details of those relatively simple but lengthy details, especially since it's not the ultimate answer that I'm wishing for, as explained in the first paragraph of this post.

(Of course, one user unfriendly solution is to have them manually write out "Sys ID > IN > javascript:getSysIdsOfHistoricNames('name1')" conditions, but I definitely want to avoid that route when I can.)