Splunk Search

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

javanue
New Member

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!

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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
Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...

Reminder! Splunk Love Promo: $25 Visa Gift Card for Your Honest SOAR Review With ...

We recently launched our first Splunk Love Special, and it's gone phenomenally well, so we're doing it again, ...