Splunk Search

How to compare events using two values of a multi-value field.

parthmadane
Explorer

Hello all, 

I have been struggling for a while now to create a query for comparing the events using two different values of a multi-value field. 

For starter -

We have certain jobs running for which their status is to be monitored. Below is an example of query/data -

Query - source=src_name sourcetype=application Job_Name=*  JOB_STATUS=started

Output -

Job A     

Job B

Job C

Query - source=src_name sourcetype=application Job_Name=*  JOB_STATUS=stopped

Output -

Job A

Job C

JOB_STATUS is the multi-value field that gives the respective Job's status after it starts running i.e. "Started." If the Job run is successful then it will be stopped, thus, there will be an event for that JOB with status as "Stopped".

Else, the Job will remain in started state and so, there'll only be a "Started" event present for that JOB.

What I need help with?

I need a query that can compare and give the list of those Jobs that are only started and not stopped yet.

Example Query - 

source=src_name sourcetype=application {-- query Return jobs that are only in started and not stopped yet --}

Required Output -
Job B
 

Please help out!

Labels (4)
0 Karma

ITWhisperer
Legend

 

source=src_name sourcetype=application JOB_STATUS=started JOB_STATUS!=stopped

 

0 Karma

parthmadane
Explorer

Hello @ITWhisperer,

This won't work for 2 reasons. First, it will only list out the started jobs again, i.e. 

Job A
Job B
Job C

Secondly, as I said, JOB_STATUS is a multi-value field thus, it also contains some other unnecessary values. Which causes JOB_STATUS!=stopped to list again ONLY the started jobs and also job events for the other status values.

0 Karma

ITWhisperer
Legend

Here is a run-anywhere example with additional status values in the multi-value field showing that only job B is returned

| makeresults
| eval event="A,started;running;other unnecessary value;stopped:B,started;running;other unnecessary value:C,started;running;other unnecessary value;stopped"
| eval event=split(event,":")
| mvexpand event
| eval job=mvindex(split(event,","),0)
| eval status=split(mvindex(split(event,","),1),";")
| fields job status
| fields - _time
| search status!=stopped status=started

parthmadane
Explorer

Hello @ITWhisperer ,

I think the information provided by me earlier, was a bit ambiguous. Basically, there is one event each generated w.r.t the JOB. For e.g. when it starts, then runs, some other values, stops. When the events are of such nature, the given solution does not work.

I have made some changes to your sample events to better replicate mine.

| makeresults
| eval event="A,started:A,running:A,other unnecessary value:A,stopped:B,started:B,running:B,other unnecessary value:C,started:C,running:C,other unnecessary value:C,stopped"
| eval event=split(event,":")
| mvexpand event
| eval job=mvindex(split(event,","),0)
| eval status=split(mvindex(split(event,","),1),";")
| fields job status
| fields - _time
| search status!=stopped status=started

You will notice that both the status!=stopped status=started and status=started are returning the same result in this (my) scenario.

Is there any way to compare the events and only return those jobs that are only in started and have not stopped yet. Your help in this regard is appreciated 🙂

0 Karma

ITWhisperer
Legend

If you want to keep all the original data, use eventstats instead

| makeresults
| eval event="A,started:A,running:A,other unnecessary value:A,stopped:B,started:B,running:B,other unnecessary value:C,started:C,running:C,other unnecessary value:C,stopped"
| eval event=split(event,":")
| mvexpand event
| eval job=mvindex(split(event,","),0)
| eval status=split(mvindex(split(event,","),1),";")
| fields job status
| fields - _time
| eventstats values(status) as allstatus by job
| search allstatus!=stopped allstatus=started
0 Karma

ITWhisperer
Legend

You can gather the information on the job together so the status is a multi-value field, then the search will work

| makeresults
| eval event="A,started:A,running:A,other unnecessary value:A,stopped:B,started:B,running:B,other unnecessary value:C,started:C,running:C,other unnecessary value:C,stopped"
| eval event=split(event,":")
| mvexpand event
| eval job=mvindex(split(event,","),0)
| eval status=split(mvindex(split(event,","),1),";")
| fields job status
| fields - _time
| stats values(status) as status by job
| search status!=stopped status=started
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!