Function to find user titles in sys_user table that do not exist in another table

[ABouland]

Our firm is getting ready to roll out a large number of new user titles in Active Directory, where our SN user data comes from.  I want to be able to easily find user records that have a title that is not on the published list of acceptable titles (around 300 unique titles).  I've created a new table: Title List (u_title_list) with a single field 'u_title'  and I have imported all of the acceptable titles to this table.

I've managed to create script includes to do similar sorts of things to find users with duplicate employee numbers and then referenced the script include in a report but in trying to construct something similar here, don't seem to be getting there. 

Currently just attempting to do this in a background script to see if I am returning anything.

Current script:

function checkTitle() {
var grUSER = new GlideRecord('sys_user'); 
var grTITLE = new GlideRecord('u_title_list');

grTITLE.query();

while(grTITLE.next()){    

      // grUSER.initialize();
       grUSER.addQuery('title', grTITLE.u_title);   

       grUSER.query();

      if(!grUSER.next()){
                       gs.print("Did not Find: " + grUSER.title);
}          
}
}

Not getting any results though.  Have tried a few variations of the above but no results with any of them.

Script include was simply the method I've used to do something similar before but am fine with any efficient method to get a list of outliers.  Any suggestions are much appreciated.

Thanks,

Andrew

[HarshTimes]

Hey Andrew

Try below code and check what result you are getting.

function checkTitle() {
	var grUSER = new GlideRecord('sys_user');
	var grTITLE = new GlideRecord('u_title_list');
	grTITLE.query();
	
	while(grTITLE.next()){
		// grUSER.initialize();
		grUSER.addQuery('title', grTITLE.u_title);
		grUSER.query();
		
		if(!grUSER.next()){
			gs.print("IN if condition");// you were priting title from user table. If no record found then how system will print.
		}
		else
			gs.print("###HC :  In else condition."); // if this is print
	}
}

[Jon Barnes · Accepted solution]

I would use a database view with a left outer join for this. Basic steps for this: Create a new dB view.

Add sys_user table first and give it an order of 100. Set the prefix as ”usr”.

Add u_title_list table second with an order of 200. Make this one a left join by checking the left join box. If it isn’t on the form you can set it from the list view. Set the prefix on this table as “ttl”.

Set the where clause on the u_title_list table record as. ttl_u_title = usr_title.

Now, once that is added, you have created your functioning DB view. If you go to the new view and click Try It in the related links, you will see the tables joined together.

Finally you can use the filters to add a query like this: Title (ttl_u_title) Is Empty AND Title (usr_title) Is Not Empty. Let me know if that works for you.

[ABouland]

Jon,

I had tried to create a database view but had been missing at least one step.  Have tried to do this again.  When I go to Try It, I am presented with the table view with 6722 records but no actual information is visible - all fields are either blank or display "(empty)".  My User table has 6715 records.  Adding columns re: fields from User table such as First Name and Last Name does not seem to make any difference - all fields still blank. 

If I set the filter re: : Title (ttl_u_title) Is Empty AND Title (usr_title) Is Not Empty AND Active is TRUE it reduces the number of records to 570 but still nothing actually visible in the list.

Have confirmed that my table U_Title does indeed have all of the data I imported.  Simply a single column, column property Display is set to True.  Quite possible that it's something simple that I've missed with the table I created but if so, not seeing it.  All ACL's on the table are set with Admin override is true and all work above performed under my admin account. 

[ABouland]

read this article https://community.servicenow.com/community?id=community_question&sys_id=2e4b4321db9cdbc01dcaf3231f961987

Recreated my database view with prefixes in lowercase and now I get visible data.  Had tried renaming in the original but no change.  Complete fresh recreate with lowercase and it's fine.  Go figure.

Still curious to try to fix my script but this got me what I needed.

Thanks,

Andrew