How to use AND operation with multivalue fields?

[vrmandadi]

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

[micahkemp · Accepted solution]

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

[vrmandadi]

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

[micahkemp]

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?

[nikita_p]

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

[vrmandadi]

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

[micahkemp]

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:

| table mvzipfield | mvexpand mvzipfield | rex field=mvzipfield "(?<segment_provider_id>[^:]+):(?<segment_status>.*)" | search segment_provider_id=abc.com | stats count

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).

[vrmandadi]

hello @micahkemp

Thanks for the reply ,I tried the below query

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

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?

[somesoni2]

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

[vrmandadi]

there are many like 50+..