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!
@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