Get incident category , state and priority of incidents that have work notes with specific keywords

[Snehal13]

I need incident category , state and priority of incidents that have work notes updated as either of "Executed script" or "Deployed script"

Currently, I am querying Table - sys_journal_field and filters as Element = work notes, name is incident

value is 'executed script' OR value is 'Deployed script' but it gives element id and total records but is half of my expectation.

[Bert_c1]

hmm, you use "Executed script" in you first sentence, and use "executed script" in you second sentence. And  you determine what incidents contain those work_notes by using the 'Element ID'.

var sjf = new GlideRecord('sys_journal_field');
sjf.addEncodedQuery('GOTOname=incident^element=work_notes^value=Executed script^ORvalue=Deployed script');
sjf.query();
//gs.info("Found " + sjf.getRowCount() + " records");
while (sjf.next()) {
	var inc = new GlideRecord('incident');
	inc.addQuery('sys_id', sjf.element_id);
	inc.query();
	if (inc.next()) {
		gs.info('inc Catagory = ' + inc.category.getDisplayValue() + ', state = ' + inc.state + ', priority = ' + inc.priority);
	}
}

[Sid_Takali]

Hi @Snehal13 You can use a server-side script to query these details. Here’s how you can achieve this using a GlideRecord script: 

var incidentIds = [];

var journalGr = new GlideRecord('sys_journal_field');
journalGr.addQuery('element', 'work_notes');
journalGr.addQuery('name', 'incident');
journalGr.addQuery('value', 'EXECUTED SCRIPT'); // Use uppercase to match exact case if needed
journalGr.addOrCondition('value', 'DEPLOYED SCRIPT'); // Use uppercase to match exact case if needed
journalGr.query();

while (journalGr.next()) {
    if (journalGr.element_id) {
        incidentIds.push(journalGr.element_id.toString());
    }
}

incidentIds = [...new Set(incidentIds)];
var incidentGr = new GlideRecord('incident');
incidentGr.addQuery('sys_id', 'IN', incidentIds.join(','));
incidentGr.query();

while (incidentGr.next()) {
    gs.info('Incident ID: ' + incidentGr.sys_id +
            ', Category: ' + incidentGr.category +
            ', State: ' + incidentGr.state +
            ', Priority: ' + incidentGr.priority);
}

[Bert_c1]

@Snehal13 

Besides the script I posted, you can define a Database View as shown below

The where clause for the sys_journal_field table is

sjf_element='work_notes' && (sjf_value='Executed script' || sjf_value='Deployed script') && sjf_name='incident'

You can then select the desired columns you want to see.

[Snehal13]

I tried above approach but getting no results. How to write where clause in database join to check if work notes CONTAIN 'executed the script' or work notes contain 'deployed the script'