REST API Explorer - query joining two tables

[suprabha]

Hi Team,

We have to prepare a table API query joining two tables incident and journal_entry_field to fetch some incidents having entry in journal_entry_field by some specific user on particular date range.

I can able to get the same by database view but that would not work for me as the target system is not accessible to me this time and we can't create any view on same, we can fire the table query from browser/postman only to get expected result.

Can anybody help me on this please?

[suprabha]

I am very much new to this.And could not get completely how it could be achieved.

I want to filter out some incidents on the basis of worknote entries by specific user on specific time in a single query.

Could you please explain your solution in detail so that I can understand and implement.

Thanks,

Suprabha

[Ankur Bawiskar]

Hi Suprabha,

In the scripted rest api you can query the sys_journal_field with the sys_ids of the incidents and exclude those values in the response

Regards
Ankur

[suprabha]

Thanks Ankur! that I know but it is not a matter of single incident and will not resolve my purpose.

I have to fetch all the incidents with a worknote entry  by specific user on specific date by a single api query.

Please let know if you can help me with a solution or similar sample query

[Ankur Bawiskar]

Hi,

Refer sample script for scripted rest resource

If 3rd party will be sending the USER ID then you need to parse the incoming JSON request or fetch the value from URL and then use in query below

(function process(/*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {

	// implement resource here

	var incArr = [];
	var rec = new GlideRecord('sys_journal_field');
	rec.addQuery('element', 'work_notes');
	rec.addQuery('name', 'incident');
	rec.addQuery('sys_created_by', 'YOUR USER ID HERE');
	rec.query();
	while(rec.next()){
		incArr.push(rec.element_id.toString()); // this will store INC sys_id
	}

	// now query INC table with the above array to get the INC numbers

	var numberArr = [];
	var gr = new GlideRecord('incident');
	gr.addQuery('sys_id', 'IN', incArr);
	gr.query();
	while(gr.next()){
		numberArr.push(gr.number.toString());
	}

	var detail = {};
	detail.status = 'Success';
	detail.incidentDetails = numberArr.toString();

	response.setStatus(200);
	response.setBody(detail);

})(request, response);

Regards
Ankur

[suprabha]

Thanks Ankur!

But is there anything we can write an api query be executed from browser/postman to get the expected result joining these two table?

This was the query we are working initially but that has a loophole so we have filter on worknotes with userid and timestamp directly

https://advancetest.service-now.com/api/now/table/incident?sysparm_exclude_reference_link=true&sysparm_display_value=true&sysparm_query=sys_updated_by=digitalworker.svc^sys_updated_onBETWEENjavascript:gs.dateGenerate('2020-09-10','00:00:00')@javascript:gs.dateGenerate('2020-09-11','00:00:00')^work_notesLIKEDWSERVC^ORDERBYDESCsys_created_on&sysparm_fields=sys_id,number,short_description,description,sys_created_by,sys_created_on,work_notes,assignment_group,state

So I need a similar query joining two table incident and sys_journal_field.

Can anybody help on this please?

Thanks,

Suprabha