Invoke System properties in Encoded query Script Include

[Saib1]

hi,

i have the below code to invoke system properties in Encoded query but not working

var LicenseUsers = Class.create();
LicenseUsers.prototype = {
initialize: function() {},

getUSers: function() {
var itilUsers, roleQuery = '',
userArray = [];
roleQuery = gs.getProperty('fulfiller.report.query');
//gs.log('query is ' + roleQuery);
itilUsers = new GlideAggregate("sys_user_has_role");
itilUsers.addAggregate("COUNT");
itilUsers.addEncodedQuery(roleQuery);

itilUsers.groupBy("user");
itilUsers.query();
gs.info('All users count ' + itilUsers.getRowCount());
if (itilUsers.next()) {
userArray.push(itilUsers.user.sys_id.toString());
}
return userArray.join(',');

},
type: 'LicenseUsers'
};

[Manmohan K]

Hi @Saib1 

What have you stored in the system property 

what are you trying to accomplish using the script

[Saib1]

i have stored the few thing below in system properties. Using the Script Include i am using it in Reports

role.nameINadmin,agent_schedule_manager,antivirus_admin,approval_admin,asset,assignment_rule_admin,awa_admin,awa_manager,catalog,catalog_admin,catalog_editor,catalog_lookup_admin,certification_admin,cf_feedback_admin,change_manager,contract_manager,demand_manager,demand_user,flow_designer,guided_tour_admin,ia_admin,idea_admin,impersonator,incident_manager,inventory_admin,it_demand_manager,it_demand_user,it_portfolio_manager,it_project_manager,it_project_portfolio_user,it_project_user,itfm_planner,itil,itil_admin,itom_admin,knowledge,knowledge_admin,knowledge_coach,knowledge_domain_expert,knowledge_group_manager,knowledge_group_member,knowledge_manager,major_incident_manager,personalize_decision_table_input,portfolio_manager,problem_coordinator,problem_manager,problem_task_analyst,project_manager,project_portfolio_user,project_user,rate_model_user,report_admin,resource_user,rm_scrum_task_admin,rota_admin,safe_art_user,safe_product_owner,safe_scrum_master,safe_scrum_user,safe_story_creator,safe_story_editor,scan_admin,scrum_admin,scrum_master,scrum_sprint_planner,scrum_story_creator,scrum_user,search_application_admin,security_admin,skill_admin,sn_align_core.apw_admin,sn_align_core.apw_user,sn_change_cab.cab_manager,sn_change_write,sn_chg_soc.change_soc_admin,sn_cim.improvement_coordinator,sn_cim.improvement_manager,sn_coaching.admin,sn_coaching.coach,sn_comm_management.comm_plan_admin,sn_comm_management.comm_plan_manager,sn_compliance.manager,sn_compliance.user,sn_customerservice_agent,sn_customerservice_manager,sn_customerservice.consumer_agent,sn_customerservice.contact_manager,sn_customerservice.deescalation_requester,sn_customerservice.escalation_requester,sn_esm_agent,sn_grc.manager,sn_hr_sp.admin,sn_hr_sp.esc_admin,sn_incident_write,sn_intel_analyzer.similarity_analyzer_admin,sn_lookup_verify_user,sn_majorissue_mgt.major_issue_manager,sn_portfolio_safe.safe_portfolio_user,sn_problem_write,sn_prod_cat_rel.admin,sn_publications.admin,sn_publications.author,sn_request_write,sn_roadmap_plng.roadmap_admin,sn_walkup.walkup_admin,sn_walkup.walkup_manager,sn_walkup.walkup_technician,survey_admin,ui_notification_admin,workspace_list_admin^user.active=true^user.user_nameISNOTEMPTY^user.sys_class_name=sys_user^user.web_service_access_only=false^user.last_login_time>=javascript&colon;gs.beginningOfOneYearAgo()^user.last_login_time<=javascript&colon;gs.beginningOfLast60Days()

[Jim Coyne]

Your block of code above is obviously not complete, so what exactly is "not working"?  Are you not getting the value of the System Property back?  Is the query not getting the data back (you do not actually query anything above).  It looks like you want to get a list of sys_ids back from the function, but will a GlideAggregate get what you need?

Have you added any logging messages to try to figure out what is working or not?

Your function name is "getUSers": are you actually calling it properly and not "getUsers"?  Typos are a pain to find.

You may also want to consider adding a field on the Role table and use that in your query instead of a hard-to-read list of Roles.  Much easier to manage.

[Manmohan K]

@Saib1 

You can use below corrected code. Please make sure your query returns records

I tested with a smaller query and it worked

var LicenseUsers = Class.create();
LicenseUsers.prototype = {
initialize: function() {},

getUSers: function() {
var itilUsers, roleQuery = '',
userArray = [];
roleQuery = gs.getProperty('fulfiller.report.query');
//gs.log('query is ' + roleQuery);
itilUsers = new GlideAggregate("sys_user_has_role");
itilUsers.addAggregate("COUNT");
itilUsers.addEncodedQuery(roleQuery);

itilUsers.groupBy("user");
itilUsers.query();
gs.info('All users count ' + itilUsers.getRowCount());
while (itilUsers.next()) {
userArray.push(itilUsers.user.sys_id.toString());
}
return userArray.join(',');

},
type: 'LicenseUsers'
};