topic Re: How to compare events using two values of a multi-value field. in Splunk Search

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

parthmadane — Thu, 29 Apr 2021 14:06:15 GMT

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!

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

ITWhisperer — Thu, 29 Apr 2021 14:24:12 GMT

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

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

parthmadane — Thu, 29 Apr 2021 14:30:59 GMT

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.

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

ITWhisperer — Thu, 29 Apr 2021 14:36:54 GMT

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

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

parthmadane — Fri, 30 Apr 2021 12:20:24 GMT

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 🙂

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

ITWhisperer — Fri, 30 Apr 2021 12:26:47 GMT

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

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

ITWhisperer — Fri, 30 Apr 2021 12:29:10 GMT

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