Comparing results of subsearch and main search

[lohit]

Hi All ,

My problem statement is to find the blocked queues over 60 minutes consistently which means that there should be no succesfull connection of queues between blocked queues events. So the approach i am following is to compare the number of blocked queues events with the number of events in indexer for last 60 minutes.if they are equal i will be sure that only blocked queues events exist.

1. First i write the following query to count the events per host for blocked queues

search index=_internal earliest=-60m@m source=*metrics.log group=queue "blocked" | stats count AS Number by host

1. Then i need to pass the above calculated hosts value in the mainn search so that only for these host the main search runs

index=_internal earliest=-60m@m source=*metrics.log group=queue [ search index=_internal earliest=-60m@m source=*metrics.log group=queue "blocked" | stats count AS Number by host | fields + host ]

Above part is giving me correct host values

No comes the comparing part, so i build this query

index=_internal earliest=-60m@m source=*metrics.log group=queue [ search index=_internal earliest=-60m@m source=*metrics.log group=queue "blocked" | stats count AS Number by host | fields + host ] | stats count as Point by host | append [search index=_internal earliest=-60m@m source=*metrics.log group=queue "blocked" | stats count AS Number by host | fields + Number host ] | table host Number Point

which gives me output

host Number Point
a 123

b 147
a 1
b 2

Output is expected as i am running the stats earlier also i the main query but i cannot figure out, how to compare Number and Point count's. Also please help with the usage of "If" command since if the result matches i need to print the queue name along with host.

Please help !!

[ryhluc01]

Did the answer above work for your query?

[woodcock]

First of all, do not use subsearches if you can avoid it; this should work.

index=_internal earliest=-60m@m source=*metrics.log group=queue | stats count As NumByHost count(eval(match(_raw,"%blocked%"))) AS NumBlockedByHost by host | where NumByHost=NumBlockedByHost

[lohit]

just to be sure about the output it is coming like this

host Number Point
a <Nothing> 123
b <Nothing> 146
a 1 <Nothing>
b 2 <Nothing>