How do you compare two multivalue fields and get the result in one number (the difference)?

[javanue]

I am trying to compare multivalue fields, but I cannot figure out how to do it correctly?

Here is the original query:

index=unix (host=A OR host=B OR host=C OR host=D OR host=E OR host=F) sourcetype=my_sourcetype source=my_source NOT "localhost" Queued  earliest=-10m 
| rex field=_raw "(?ms)^(?:[^ \\n]* ){4}(?P<job_id>[^\\]]+)" offset_field=_extracted_fields_bounds 
| where match(job_id,"\d+$") 
| stats values(job_id) as Queued by host 
| join host 
    [ search index=unix (host=A OR host=B OR host=C OR host=D OR host=E OR host=F) sourcetype=my_sourcetype 
     source=my_source NOT "localhost" Completed earliest=-10m 
    | rex field=_raw "(?ms)^(?:[^ \\n]* ){4}(?P<job_id>[^\\]]+)" offset_field=_extracted_fields_bounds 
    | where match(job_id,"\d+$") 
    | stats values(job_id) as Completed by host
        ] 
| makemv delim=" " Completed

So the first query gets all the values by each host where the job was queued, and the second query returns all the values by each host where the job was completed.

The results look like the following: (the numbers are jobIDs)

host    Queued    Completed
A          10             10
           11             11
           12             12
           13
           14

B          20             20
C          1               1

What I want to see is:

host    In queue    
A          2             
B          0         
C          0               

It is really important to check the ID-s, not just count the number of results in each multivalue fields.

I tried to look for multivalue functions but did not find the appropriate ones.

Does anyone have any ideas?

Thank you!

[renjith_nair]

@javanue,

Probably we should look at the existing search and fine tune it. You might not need a join at all and also it would be easier if we could extract the status as "Completed" or "Queued" from the events and do the search based on this field.

Try this and see if it works for you

 index=unix (host=A OR host=B OR host=C OR host=D OR host=E OR host=F) sourcetype=my_sourcetype source=my_source NOT "localhost" Queued  earliest=-10m 
 | rex field=_raw "(?ms)^(?:[^ \\n]* ){4}(?P<job_id>[^\\]]+)" offset_field=_extracted_fields_bounds 
 | where match(job_id,"\d+$") 
 | stats count(eval(searchmatch("Completed"))) as Completed,count(eval(searchmatch("Queued"))) as Queued by host,job_id
 | stats sum(eval(if(Completed>0,Completed,0))) as Completed,sum(eval(if(Queued>0,Queued,0))) as InQueue by host