Need OOTB solution to identify incidents where latest customer comment has no agent reply for dashbo

[yaswanthvem]

Hi Community,

I have a requirement to create a visualization/dashboard filter for incidents where the latest customer comment has not been replied to by the assigned agent.

Requirement:

I need to identify incidents based on the comment sequence:

Scenario 1:
Customer adds a comment → Agent does not reply
Result: Count this incident

Scenario 2:
Customer adds a comment → Agent replies
Result: Do not count this incident

Scenario 3:
Customer adds a comment → Agent replies → Customer adds another comment → Agent does not reply
Result: Count this incident

In short:
I need all incidents where the latest comment in the Additional Comments field is from the customer/caller and the assigned agent has not responded after that.

Details:
- Table: Incident [incident]
- Customer = Caller
- Agent = Assigned to
- Field: Additional comments (Journal field inherited from Task table)
- Need this value to be used in a Self-Service Dashboard visualization/filter.

What I tried:

1. Database View:
- Joined Incident table with sys_journal_field.
- Join condition:
incident.sys_id = sys_journal_field.element_id
AND sys_journal_field.element = 'comments'
- This gives me journal records, but I am not able to identify only the latest customer comment and check whether an agent replied after it.

2. Metric Definition:
- Created a metric on Incident → Comments.
- Metric instances are created, but they only show the metric execution details and do not provide the commenter information needed.

3. Flow Designer:
- Tried creating a Scheduled Flow to evaluate incidents and update a flag field.
- However, I am unable to query sys_journal_field properly from Flow Designer because fields like Element are not available/usable.

Question:

Is there any OOTB platform feature or recommended approach (without Business Rule/script) to achieve this?

Thanks!