Query that checks if field values have changed to exclude events

[Splunkie]

Hi Friends,

I am working a query that checks if the value of a field has changed to a state of resolved to exclude it from the results of active cases.

The field I am trying to use to check if a case has been resold is the status field.  I need help with a query that looks at all cases with the status of Active and removes cases whose status has now changed to Resolved from the results.

Thank you.

[splunkmarroko]

try this:
base search   ``` index=xyz sourcetype=abc```
| where status!=resolved  ```if you already have the "resolved field", if not consider extracting that field.

[Splunkie]

Thanks @splunkmarroko,

Thanks. I tried that, however going about it that way returns the initial events with an "Active" status and does not take into consideration that the status has changed from "Active" to "Resolved".

[livehybrid]

Hi @Splunkie 

To exclude cases that have transitioned to "Resolved" and only show currently active cases, find the latest status per case and filter where that status is "Active".

| your_search_here
| stats latest(status) as latest_status by case_id
| where latest_status="Active"

stats latest(status) by case_id groups events by case and finds the most recent status update per case

where latest_status="Active" filters to only cases whose latest status is still "Active"

This effectively excludes cases that have been resolved or closed later

Replace case_id with your actual case identifier field

🌟Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[Splunkie]

Thanks @livehybrid,

This works but returns inaccurate results when the search is run using the real time search time filter. This is an example of what I have;

| base_search here

| stats latest(status) as latest_status by incidentId
| where latest_status!="Resolved"
| stats count as total

The output is to count the number of active incidents to be displayed on a dashboard. Any pointer or tips on how to better achieve this will be appreciated.

Cheers.