Splunk Search

How to use AND operation with multivalue fields?

vrmandadi
Builder

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

0 Karma
1 Solution

micahkemp
Champion

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

View solution in original post

0 Karma

micahkemp
Champion

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
0 Karma

vrmandadi
Builder

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

0 Karma

micahkemp
Champion

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?

0 Karma

nikita_p
Contributor

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

0 Karma

vrmandadi
Builder

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

0 Karma

micahkemp
Champion

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

0 Karma

vrmandadi
Builder

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?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

vrmandadi
Builder

there are many like 50+..

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...