How to combine Data from a lookup with a query to Pull latest values?

[amedina]

Hello,

Background story:
I have a data set that is being ingested by Splunk by the HTTP event collector, when this connector was added to Splunk, the connector started ingesting current logs from the appliance and no historical logs prior to that day it was connected.

Fast forward, in order to account for those missing logs a lookup was created. The dataset contains "Issue IDs" with its corresponding "status"

What I am trying to do is, combine the data from the lookup with the index to look pull back the latest value for "status" for the "Issue ID".
However, when the query is ran, the value from the lookup always trumps the recent data from the index, even though the lookup is 1 week older then the log comparing it.

Without the lookup, the query works fine, it is able to pull the latest values for "status".


This is how I have formulated the query:

|inputlookup OpenIssues
|fields "Issue ID",Status,Severity
|rename Status AS issue.status
|rename "Issue ID" as issue.id
|rename Severity as issue.severity
|append [search index="dataset A" sourcetype=_json
|fields  issue.id issue.status
|stats latest(issue.status)

[richgalloway]

If newer events have to trump older events then the lookup file must contain timestamps.  After the indexed data is appended to the lookup data, sort by timestamp then take the first (newest) event.

|inputlookup OpenIssues
|fields "Issue ID",Status,Severity, timestamp
|rename Status AS issue.status
|rename "Issue ID" as issue.id
|rename Severity as issue.severity
|append [search index="dataset A" sourcetype=_json
|fields  issue.id issue.status timestamp
```Put the newest events first```
|sort 0 - timestamp
|stats first(issue.status)