How to join two sets of data on a field and get the results that don't have corresponding matching data?

[mohankesireddy]

I have two sets of data, both sets have a common field with common value, when i use join command i am able to find the ones which has a matching data, but what i wanted is to find the set of data which dont have a matching hit.

sample query
index=index1 sourcetype=type1 status=503 | join requestid [search index=index1 sourcetype=type2 status=200 ] | table _time requestid

I want to get all the 503 which dont have a corresponding status 200, but have a same request id. when I use join i am getting the ones which have a corresponding 200 but not the ones which dont have one. I am not able to find a command which can do it. Any help is appreciated.

[martin_mueller · Accepted solution]

Try this:

index=index1 ((sourcetype=type1 status=503) OR (sourcetype=type2 status=200)) | eventstats dc(status) as dc by requestId | search dc=1 status=503

The eventstats will count how many different status values each requestId has. The final search only keeps status=503 values that don't have a second status, ie no status=200 event for that requestId.

[mohankesireddy]

Thanks Martin, it worked like a charm