Email Notification to all users of assets assigned specifically to them

[Moedeb]

I am wanting to do something very similar to the solution listed in the following article

https://community.servicenow.com/community?id=community_question&sys_id=f4f4c72ddbd8dbc01dcaf3231f96...

What I want to do is send an email to all users that lists all assets that are assigned to them.

I'm guessing I could trigger a email notification via a business rule or an event (this is something that could be a one off or at least used only very occasionally)

I want it to go through each user, add their assets, send the email, move onto the next user, once all have been emailed stop!

The link above does not work for me as it pretty much generates an email with every single asset listed on it, nothing specific to an individual user.

Does anyone know how I can do this?

[Ankur Bawiskar]

@Moedeb 

Sharing the steps below

1) Create event on alm_asset table

2) Create Notification on alm_asset table and link the above event created

3) Use the script shared below in scheduled job to trigger email per user

4) Create email Script and in that you can print the list of Assets Assigned to that particular user for which email triggered.

1) Event

Name: send.email.assignedToUser.asset

Table: alm_asset

2) Email Script:

Name: assets_assigned

(function runMailScript(current, template, email, email_action, event) {
	
var user = event.parm1;

var arr = [];

var gr = new GlideRecord('alm_asset');
gr.addQuery('assigned_to', user);
gr.query();
while(gr.next()){

arr.push(gr.display_name.toString());

}

template.print('Assets assigned to you are: ' + arr.toString());
	
})(current, template, email, email_action, event);

3) Job Script:

sendEmail();

function sendEmail(){

	var gr = new GlideAggregate("alm_asset");
	gr.addAggregate("COUNT");
	gr.addEncodedQuery('assigned_toISNOTEMPTY');
	gr.groupBy("assigned_to");
	gr.query();
	while(gr.next()) {
		var user = gr.assigned_to;

		var assetRec = new GlideRecord('alm_asset');
		assetRec.get('assigned_to', user);

		gs.eventQueue("send.email.assignedToUser.asset", assetRec, user, "");
	}
}

4) Notification:

Body:

${mail_script:assets_assigned}

Regards
Ankur

[Moedeb]

@Ankur Bawiskar  thank you for the above response, It pretty much does what I want, 2 main questions however if I may.

1. When it gets the assets it just types them all in one after the other, how could I get it to add one to each line
ie: rather than this: 

Assets assigned to you are: P1000177 - Apple MacBook Pro 15",Logitech Desktop Optical Wireless Mouse,Apple iPhone X,P1000104 - Apple iMac 27"

Have it like this:

Assets assigned to you are:

• P1000177 - Apple MacBook Pro 15"
• Logitech Desktop Optical Wireless Mouse
• Apple iPhone X
• P1000104 - Apple iMac 27"

2. How do I actually make it send the emails? 

Thank you for your help, it is very much appreciated.

[Ankur Bawiskar]

@Moedeb 

the way how it is getting displayed can be changed

Refer below updated script

(function runMailScript(current, template, email, email_action, event) {

var user = event.parm1;

var arr = [];

var gr = new GlideRecord('alm_asset');
gr.addQuery('assigned_to', user);
gr.query();
while(gr.next()){

arr.push(gr.display_name.toString());

}

template.print('Assets assigned to you are: ' + arr.toString());
template.print('<ul>');
for(var i=0;i<arr.length;i++){
template.print('<li>' + arr[i] + '</li>')
}
template.print('<ul>');

})(current, template, email, email_action, event);

For the 2nd point

2. How do I actually make it send the emails? 

- ensure that assigned to user is having valid email, is active and not locked out and has notification preference enabled

Once this is done the email will get triggered. if you wish the email to be received to their inbox then enabled the outgoing email in properties

Regards
Ankur

[Moedeb]

@Ankur Bawiskar thanks for that, unfortunately I am running into some issues still.

When I use the updated script you just gave me it adds the same thing twice, once like before and again in the dot format eg:

Also for some reason the only assets it's picking up is the computers, we also have assets under accessories and communication devices that are not being shown. Any ideas?

So as an example of what I mean is that the following are actually assigned to this user, but only their computer shows up in the email notification

[Ankur Bawiskar]

Hi,

ok we are doing the query on alm_asset table as this holds assets held by particular user.

In that case you will have to query on cmdb_ci table

(function runMailScript(current, template, email, email_action, event) {

var user = event.parm1;

var arr = [];

var gr = new GlideRecord('cmdb_ci'); // table name changed
gr.addQuery('assigned_to', user);
gr.query();
while(gr.next()){

arr.push(gr.name.toString()); // field changed here

}

template.print('Assets assigned to you are: ' + '<br/>');
template.print('<ul>');
for(var i=0;i<arr.length;i++){
template.print('<li>' + arr[i] + '</li>')
}
template.print('<ul>');

})(current, template, email, email_action, event);

Regards
Ankur