How to create and schedule report for all managers

[Michael M1]

I need to run a weekly report for all managers that have users whose accounts will expire next week. 

What is the best mechanism to get this done? 

I know how to extract the data with glide query - I can get a list of users who will expire next week and group by manager field - but do I use a mail script? This allows me to add the data to the notification but not iterate through each manager. Should it be a scheduled report? I don't want the other managers to see anyone but their own users. 

Best approach appreciated. 

[sachin_namjoshi]

You don't need to develop script for this requirement.

You can utilize flow designer based on schedule to send email to managers for upcoming expiring users.

https://docs.servicenow.com/bundle/london-servicenow-platform/page/administer/flow-designer/concept/flow-designer.html

Regards,

Sachin

[dvp · Accepted solution]

Recently i had provided a solution on how to send reports managers dynamically

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

[Michael M1]

Getting errors on insert into sys_auto

FAILED TRYING TO EXECUTE ON CONNECTION 14: INSERT INTO sysauto_report (`page_height_in_pixels`,`sys_id`,`page_size`,`zip`,`output_type`,`omit_if_no_records`,`include_detail`,`user_list`,`page_width_in_pixels`) VALUES(612,'b33e9ce4dbf7e340b7b329da4b9619b8','Letter',0,'PDF-landscape',0,0,'f2fad081dbf5a700ce59a15505961969',792),INSERT INTO sysauto (`conditional`,`run_as`,`run_type`,`sys_id`,`run_start`,`run_dayofmonth`,`upgrade_safe`,`active`) VALUES(0,'f2fad081dbf5a700ce59a15505961969','daily','b33e9ce4dbf7e340b7b329da4b9619b8','2019-02-19 21:13:35',1,0,1),INSERT INTO sys_metadata (`sys_updated_on`,`sys_class_name`,`sys_id`,`sys_updated_by`,`sys_created_on`,`sys_name`,`sys_scope`,`sys_created_by`,`sys_mod_count`,`sys_package`,`sys_update_name`) VALUES('2019-02-19 21:13:35','sysauto_report','b33e9ce4dbf7e340b7b329da4b9619b8','admin','2019-02-19 21:13:35',NULL,'global','admin',0,'global','sysauto_report_b33e9ce4dbf7e340b7b329da4b9619b8')
java.sql.BatchUpdateException: Duplicate entry 'b33e9ce4dbf7e340b7b329da4b9619b8' for key 'PRIMARY'
: java.sql.SQLException: java.sql.BatchUpdateException: Duplicate entry 'b33e9ce4dbf7e340b7b329da4b9619b8' for key 'PRIMARY': com.glide.db.StatementBatcher.getSQLException(StatementBatcher.java:494)
com.glide.db.StatementBatcher.commitBatch(StatementBatcher.java:449)
com.glide.db.StatementBatcher.commitAllOrNothing(StatementBatcher.java:330)
com.glide.db.DBCompositeAction.executeAsBatch(DBCompositeAction.java:193)
com.glide.db.DBCompositeAction.executeChunk(DBCompositeAction.java:136)

[sachin_namjoshi]

You should not be maintaining code for functionality which is possible OOB with flow designer.

As i said earlier, You can utilize flow designer based on schedule to send email to managers for upcoming expiring users.

https://docs.servicenow.com/bundle/london-servicenow-platform/page/administer/flow-designer/concept/flow-designer.html

Regards,

Sachin