topic How to use AND operation with multivalue fields? in Splunk Search

How to use AND operation with multivalue fields?

vrmandadi — Tue, 29 Sep 2020 17:24:50 GMT

I have a two multivalued fields
1)segment_status -with values

SUCCEEDED-100
FAILED-100

2)segment_provider_id-with values

abc.com-10
ddd.com-20
ccc.com-30

I am trying to find the following counts

1) total count of segement_status(SUCCEDED+FAILED) with segment_provider_id=abc.com

index=ggg.....
segment_provider_id="abc.com" segment_status=*
| eval abc=mvcount(segment_status) |stats sum(abc) as "Total"

The above query I tried is taking all the segment_provider_id instead of only abc.com

2)SUCCEDED COUNT AND FAILED COUNT

index=ggg....segment_provider_id="abc.com" segment_status=*
| eval succeeded=mvfilter(match(segment_status, "SUCCEEDED")) | eval failed=mvfilter(match(segment_status, "FAILED")) | stats count(succeeded) AS succeeded_count, count(failed) AS failed_count

The above is also considering all other providers instead of abc.com

I tried using mvexpand but that is taking a lot of space

Thanks in advance

Re: How to use AND operation with multivalue fields?

somesoni2 — Tue, 29 Sep 2020 17:24:53 GMT

Will there be only 3 segment_provider_id or there may be others?

Re: How to use AND operation with multivalue fields?

vrmandadi — Fri, 05 Jan 2018 02:44:21 GMT

there are many like 50+..

Re: How to use AND operation with multivalue fields?

micahkemp — Fri, 05 Jan 2018 04:16:36 GMT

For 1): It seems that you have events with both multivalue fields: segment_provider_id and segment_status. What's happening when you search for segment_provider_id=<value> is you probably get some results with segment_provider_id values that aren't what you searched for.

There is no internal relationship between the first value of segment_provider_id and the first value of segment_status. If these fields are the same length, and value1 of one is associated with value1 of the other, you may need to make use of mvzip to create a new field that looks like: abc.com:SUCCEEDED, then perform additional logic against that field, perhaps something like:

Having events with multiple multi-value fields that are expected to relate to each other is certainly less than optimal, but sometimes can't be avoided. And it should be stated that mvexpand can be a bad idea when there are a lot of events and/or a lot of field combinations (which is why my example uses | table <one field only> prior to calling mvexpand).

Re: How to use AND operation with multivalue fields?

nikita_p — Tue, 29 Sep 2020 17:29:46 GMT

Hi,
Can you check if below query works for you?
index=ggg.....
segment_provider_id=* segment_status=*
| eval abc=mvcount(segment_status)| search segment_provider_id="abc.com" |stats sum(abc) as "Total"

index=ggg....segment_provider_id=* segment_status=*
| eval succeeded=mvfilter(match(segment_status, "SUCCEEDED")) | eval failed=mvfilter(match(segment_status, "FAILED"))| search segment_provider_id="abc.com" | stats count(succeeded) AS succeeded_count, count(failed) AS failed_count

Re: How to use AND operation with multivalue fields?

vrmandadi — Fri, 05 Jan 2018 14:41:46 GMT

It did not work,the problem is ,it is taking all the provider_id instead of abc.com

Re: How to use AND operation with multivalue fields?

vrmandadi — Tue, 29 Sep 2020 17:34:02 GMT

hello @micahkemp

Thanks for the reply ,I tried the below query

This gives me the result correctly but the problem is when I expand time range it is taking forever and its reaching the limit.

Is there any other way other than using mvexpand?

Re: How to use AND operation with multivalue fields?

micahkemp — Fri, 05 Jan 2018 17:17:48 GMT

Try this:

index=ggg
| eval abc=mvzip(segment_status,segment_provider_id,":")
| eval sss=mvzip(abc,segment_type,":")
| eval filtered_sss=mvfilter(match(sss, "SUCCEEDED:abc.com:AD"))
| stats count(filtered_sss) AS count

Or:

index=ggg
| eval abc=mvzip(segment_status,segment_provider_id,":")
| eval sss=mvzip(abc,segment_type,":")
| stats count(eval(filtered_sss="SUCCEEDED:abc.com:AD")) AS count

Re: How to use AND operation with multivalue fields?

vrmandadi — Tue, 29 Sep 2020 17:35:04 GMT

@ micahkemp

Thanks for the query,one small question is there a way to calculate the average of success and failure percentage of it

| eval abc=mvzip(segment_status,segment_provider_id,":")
|eval sss=mvzip(abc,segment_type,":") | eval filtered_sss=mvfilter(match(sss, ".*:abc.com:AD")) |stats count(filtered_sss) as Total,count(eval(filtered_sss="SUCCEEDED:abc.com:AD")) as S_Count |eval Success_Per=S_Count/Total * 100 |fields Success_Per

I am trying this but not giving the correct results

Total S_Count Success_Per
34206 10610 31.017950

The S_Count is showing wrong as the original value is 27,359 .but it is not taking this value.

Re: How to use AND operation with multivalue fields?

micahkemp — Mon, 08 Jan 2018 21:44:20 GMT

I'm not seeing any glaring issues in your search. Is your search time the same? What method of verification did you use to assert that the value should be different than what is shown?