The below query is the original one, it was working fine in the lower environments, once moved to the production with the over head, i m facing very bad performance and jobs are queuing.
I am trying to optimize the query to use join instead of OR between 2 different indexes but it is not extracting the messages i m looking for .
My original Query
(index=A source=test message="PUBLISH message recieved" ) OR (index=B sourcetype=test2 "Message Successfully Processed to x")
| eval Vin=if(isNull(clientId),Vin,clientId)
| eval activityId=if(isNull(activityId),ActivityID,activityId)
| eval Entry= if(match(message,"PUBLISH message recieved"),_time,NULL)
| eval Exit=if(match(Message,"Message Successfully Processed to x"),_time,NULL)
|stats min(Entry) as Entry,max(Exit) as Exit, values(Vin) as Vin, values(deviceid) as ESN by activityId
| rename activityId as traceid
| fillnull value="NULL" | where Exit!="NULL"
|eval duration=Exit-Entry | eval Durations= if(duration<0,0,duration)
| convert ctime(Entry) |convert ctime(Exit) |table traceid, Entry,Exit,Vin,ESN,Durations | rename traceid as "Trace Id"
The one i m trying to build with the join command
(index=A source=test message="PUBLISH message recieved" )
|join activityId [search index=B sourcetype="test2 " "Message Successfully Processed to x"
| eval Vin=if(isNull(clientId),Vin,clientId)
| eval activityId=if(isNull(activityId),ActivityID,activityId) ]
| eval Entry= if(match(message,"PUBLISH message recieved"),_time,NULL)
| eval Exit=if(match(Message,"Message Successfully Processed to x"),_time,NULL)
|stats min(Entry) as Entry,max(Exit) as Exit, values(Vin) as Vin, values(deviceid) as ESN by activityId
| rename activityId as traceid
| fillnull value="NULL" | where Exit!="NULL"
|eval duration=Exit-Entry | eval Durations= if(duration<0,0,duration)
| convert ctime(Entry) |convert ctime(Exit) |table traceid, Entry,Exit,Vin,ESN,Durations | rename traceid as "Trace Id"
Thanks and appreciate the support!
... View more