join search with condition

[arandy01]

I have two searches:
search-A gives values like
 

type	status	hostname	id	port	Size	base	cache
http	OFF	host-1	17	NA	NA	NA	NA
http	ON	host-1	6	NA	NA	NA	NA
http	ON	host-1	15	NA	NA	NA	NA
http	OFF	host-1	1	NA	NA	NA	NA
web	OFF	host-2	17	NA	NA	NA	NA
web	ON	host-2	6	NA	NA	NA	NA
http	ON	host-3	15	NA	NA	NA	NA
http	OFF	host-3	1	NA	NA	NA	NA

Search-B gives value like

type	status	hostname	id	port	Size	base	cache
available	not_processed	host-1	17	NA	NA	NA	NA
available	not_processed	host-2	17	NA	NA	NA	NA
available	not_processed	host-4	15	NA	NA	NA	NA
available	not_processed	host-5	1	NA	NA	NA	NA

I want to merge two search in such a way that it can check hostname in search-B and if hostname is present in search-A the it should not join/merge that row.. the result should be something like below...

type	status	hostname	id	port	Size	base	cache
http	OFF	host-1	17	NA	NA	NA	NA
http	ON	host-1	6	NA	NA	NA	NA
http	ON	host-1	15	NA	NA	NA	NA
http	OFF	host-1	1	NA	NA	NA	NA
web	OFF	host-2	17	NA	NA	NA	NA
web	ON	host-2	6	NA	NA	NA	NA
http	ON	host-3	15	NA	NA	NA	NA
http	OFF	host-3	1	NA	NA	NA	NA
available	not_processed	host-4	15	NA	NA	NA	NA
available	not_processed	host-5	1	NA	NA	NA	NA

[manjunathmeti · Accepted solution]

hi @arandy01,
Try this:

search-A | append [search search-B] | eventstats count(eval(status IN("ON", "OFF"))) as status_count by hostname | where NOT (status_count!=0 AND status="not_processed")

[arandy01]

Hi @manjunathmeti 
Thanks for the quick reply...
But it does not work... and only shows results from search-A

[manjunathmeti]

Updated my answer check now.

[arandy01]

Thanks a lot 🙂
works perfectly 🙂