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
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
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
@ 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.
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?
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
It did not work,the problem is ,it is taking all the provider_id instead of abc.com
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).
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?
Will there be only 3 segment_provider_id or there may be others?
there are many like 50+..