Find all tasks associated to users

[shane_davis]

I have an issue with duplicate users in our system.   My goal is to find all the users who do not have any tasks, CIs, Approvals, etc tied to them and delete those users.   Then, I will configure our coalesce to work accurately based on the systems we import from.

I've search the Wiki and Community, but haven't found anything that will tell me how to find these users.   Any help will be much appreciated!

[marcguy]

ouch!! I think you will need to do write a gliderecord query to intially go through sys_user and then for each user, look on those tables, I can think of 3 at least but there are bound to me more places, roles, group memberships, etc.

task - assigned_to

cmdb_ci - assigned_to

sysapproval_approver - approver

do a count where you find the user. an example to get started would be this to look for tasks assigned to the user

The script below would take a users sys_id and return how many tasks assigned to them

var task = new GlideAggregate('task');

task.addQuery('assigned_to', 'users sys_id');

task.addAggregate('COUNT');

task.query()

var tickets = 0;

if (task.next())

tickets = task.getAggregate('COUNT');

gs.print('this user has ' + tickets + ' tickets assigned to them');

[nayeem2]

gs.print() work on only background-scripts

[shane_davis]

mguy - Thank you for your reply.   Yeah, it's proof that imported data is only as good as the source and when you have two sources with multiple data formats, it gets messy!

I added all the related lists at the bottom to the user form in DEV (numbers shown are typed manually) and then I ran the script below to merge users.   This is one at a time though and I have many of these to do.  

doit('user1','user2'); 

function doit(username1,username2) { 

 var usr1 = new GlideRecord('sys_user');
 var usr2 = new GlideRecord('sys_user');
 var num = 0;

 if (usr1.get('sys_id',username1) && usr2.get('sys_id',username2)) {
   var ref;
   var dict = new GlideRecord('sys_dictionary');
   dict.addQuery('reference','sys_user');
   dict.addQuery('internal_type','reference');
   dict.addQuery('sys_class_name','!=','wf_activity_variable');
   dict.query();
   while (dict.next()) {
   num = 0;
   ref = new GlideRecord(dict.name.toString());
   ref.addQuery(dict.element,usr1.sys_id);
   ref.query();
   while (ref.nextRecord()) {
   ref.setValue(dict.element.toString(),usr2.sys_id); 
   ref.setWorkflow(false);
   ref.update();
   num++;
   }
   if (num > 0) {
   gs.print(dict.element + ' changed from ' + usr1.user_name + 
   ' to ' + usr2.user_name + ' in ' + num + ' ' + dict.name + ' records');
   }
   }
 }
}

37 Roles — certification
6 Groups
Delegates
Skills
5 CIs
85 Incident — assigned to
39 Incident — caller
0 Incident — closed by
16 Incident — opened by
70 Incident — resolved by
17 Request — Requested for
Request — opened by
31Reqeust — closed by
85 Request — assigned to
RITM — opened by
RITM — closed by
RITM — assigned to
CTASK — opened by
CTASK — closed by
CTASK — assigned to
6 CHG — testing reviewed by
15 CHG — tested by
39 CHG — submitted by
8 CHG — requested by
38 CHG — opened by
4 CHG — closed by
CHG — BU approver
38 CHG — assigned to
CHG — approved by
52 CHG Task — closed by
69 CHG Task — assigned to
30 CHG Task — opened by
AUD — assigned to
AUD — opened by
ACT — requested by
ACT — opened by
ACT — closed by
ACT — assigned to
OBS — opened by
OBS — closed by
OBS — assigned to
Application — supported by
Application — owned by
Application — managed by
Application — assigned to
Application Software — supported by
Application Software — owned by
Application Software — managed by
Application Software — assigned to
Asset — owned by
Asset — managed by
5 Assets — assigned to

2 Approvals — 1 cancelled and 1 approved

[Mike Allen]

I would switch your intent.   I would get the systems working correctly, then delete the users.   You may chase your tail a bit if you don't.

To find your answer, I would maybe do something akin to this:

Clone prod to subprod

Put 3 temp fields on user (CI, tasks, approvals) to hold yes/no or number

write three count aggregate queries to populate the fields in background scripts

filter out any that have records

list the ones you need to delete and export with sys_id

make a query that deletes all those sys_ids ('sys_id', 'IN', '<comma-separated list of sys_ids>');

test the query in the subprod instance

move the query to prod

I know this looks a bit cumbersome, but you should be really careful deleting data in prod.