Help with multivalue field count

[vrmandadi]

Hello,

I have a multivalue field with two values.

segment_status:

SUCCEEDED-1234333

FAILED-34555

I am trying to get the total of segment status and individual count of Succeeded and FAILED

for the total count I have done the below query

eventtype=abc ... segment_status=*
| eval abc=mvcount(segment_status) |stats sum(abc) as "Total"--this gives me the correct total of both succeded and failed. but I am not able to get the individual count of succesded and failed.

this is the query i tried
eventtype=abc ... segment_status=SUCCEEDED
| eval success=mvcount(segment_status) |stats sum(abc) as "Total" -- this is again giving me the total count but not for success.

Thanks in advance

[kamlesh_vaghela]

Hi @vrmandadi ,

Can you please try this one ?

eventtype=abc ... segment_status=*
| stats sum(eval(like(segment_status,"%FAILED%"))) as FAILED_COUNT sum(eval(like(segment_status,"%SUCCEEDED%"))) as SUCCEEDED_COUNT

My Sample Search:

| makeresults 
| eval segment_status="SUCCEEDED-1234333,FAILED-34555" 
| makemv delim="," segment_status 
| append 
    [| makeresults 
    | eval segment_status="SUCCEEDED-1234333,FAILED-34555" 
    | makemv delim="," segment_status] 
| append 
    [| makeresults 
    | eval segment_status="SUCCEEDED-1234333" 
    | makemv delim="," segment_status] 
| append 
    [| makeresults 
    | eval segment_status="FAILED-34555" 
    | makemv delim="," segment_status] 
| append 
    [| makeresults 
    | eval segment_status="FAILED-34555" 
    | makemv delim="," segment_status] 
| stats sum(eval(like(segment_status,"%FAILED%"))) as FAILED_COUNT sum(eval(like(segment_status,"%SUCCEEDED%"))) as SUCCEEDED_COUNT

Thanks
Kamlesh

[vrmandadi]

Hey kamlesh,

I tried your search but the results are not correct

[somesoni2]

Give this a try

eventtype=abc ... segment_status=*
| eval SUCCEEDED=if(isnotnull(mvfind(match(segment_status,"SUCCEEDED"))),1,0)
| eval FAILED=if(isnotnull(mvfind(match(segment_status,"FAILED"))),1,0)
| stats sum(FAILED) as FAILED sum(SUCCEEDED) as SUCCEEDED count as Total

[vrmandadi]

It shows error

Error in 'eval' command: The expression is malformed. Expected ).

[micahkemp]

Oops, edited to correct that. 🙂

[micahkemp · Accepted solution]

Try this:

| 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

[nick405060]

Ty! I did this for counting internal and external recipient_domains, where recipient_domain is an mv field.

eval count_internal=mvcount(mvfilter(match(recipient_domain, "abc.com")))
and
eval count_external=mvcount(mvfilter(!match(recipient_domain, "abc.com")))

[vrmandadi]

it says Error in 'eval' command: The expression is malformed. Expected ).

[vrmandadi]

I just added a bracket and it worked

| 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

Thnks