Improving performance on join

[andrewwjc]

I have a data feed to Splunk that contains number, state and service name.
This comes in to Splunk continuously as the state/service name changes. (number would stay the same as it is the key field)
Regardless of the current service name at time of query, I would like to retrieve the latest "state" on data where the "number" has/had dv_u_service as "ODD CBJ PROD".

Here's what I'm trying to achieve:-
[ All data with dv_u_service="ODD CBJ PROD" ] + [ All data ]
This is joined by the field "number" common in both searches.
The index and the sourcetype for the two searches above are similar.

I've achieved this using join but it's painstakingly slow. Is there a better way?

(index=gbs_its_pds_infra_servicenow) (dv_u_service="ODD CBJ PROD")  | eventstats latest(state) as latest_state by number | dedup number | table dv_u_service,assignment_group_name,latest_state,number | join left=L right=R type=inner max=1 where L.number=R.number [search (index=gbs_its_pds_infra_servicenow)  | eventstats latest(state) as latest_state by number ] | table L.dv_u_service,L.assignment_group_name,L.latest_state,L.number,R.dv_u_service,R.latest_state,R.assignment_group_name

Also, at the end of the query, how do I only show the results where L.dv_u_service<>R.dv_u_service? Would it be through eval?

[to4kawa · Accepted solution]

 (index=gbs_its_pds_infra_servicenow) 
| stats latest(state) as L.latest_state 
,latest(eval(if(dv_u_service="ODD CBJ PROD",state,NULL))) as R.latest_state
,values(dv_u_service) as L.dv_u_service
,values(eval(if(dv_u_service="ODD CBJ PROD",dv_u_service,NULL))) as R.dv_u_service
,values(assignment_group_name) as L.assignment_group_name 
,values(eval(if(dv_u_service="ODD CBJ PROD",assignment_group_name,NULL))) as R.assignment_group_name by number 
| table L.dv_u_service,L.assignment_group_name,L.latest_state,number,R.dv_u_service,R.latest_state,R.assignment_group_name