Splunk Search

Improving performance on join

andrewwjc
Engager

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?

0 Karma
1 Solution

to4kawa
Ultra Champion
 (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

View solution in original post

0 Karma

to4kawa
Ultra Champion
 (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
0 Karma
Get Updates on the Splunk Community!

hadoop vs splunk

hiIn big data can we replace hadoop by splunk ? and why?do splunk do&nbsp; all hadoop fonctionality

Apply a condition depends on a field or token

Hi teamI need to apply a different condition in one panel of my dashboard depending on a selected column in ...

Index with one sourcetype - search performance / best practices

Hello,I have created a few indexes, each containing data only from one source with one sourcetype.<BR />From a ...