How to add value based on matrix comparison

[cros]

Hi all, 

My data is logging of support ticket. i retrieved all the change state of each ticket with the transaction command. This command output a list of status for each ticket id. 

What I'm trying to do is to compare each pair of Ticket status in order to create a state for management. 

I have this matrix explaining states for all pair of values : 

         A                B

A  Value1    Value2

B  Value3     Value4

To accomplish this work, I'm using case statement : 

state=case(match(mvindex(status, 0), "^A$") AND match(mvindex(status, 1), "^A$"), "Value1",
match(mvindex(status, 0), "^A$") AND match(mvindex(status, 1), "^B$"), "Value2",
match(mvindex(status, 0), "^B$") AND match(mvindex(status, 1), "^A$"), "Value3",
match(mvindex(status, 0), "^B$") AND match(mvindex(status, 1), "^B$"), "Value4")

For sure my real matrix is much bigger than this. It's why I'm searching an other way to do it. 

My expected result is a table like this : 

Event         Status        State

    1              A, B, A          Value2, Value3

    2              A, A, B          Value1, Value2

    3              B, B, A          Value4, Value3

    4              B, A, B          Value3, Value2

regards,

clement

[to4kawa]

index=_internal 
| head 1 
| fields _raw 
| eval _raw="Status
A, B, A
A, A, B
B, B, A
B, A, B" 
| multikv forceheader=1 
| eval Status=trim(split(Status,",")) 
| rename COMMENT as "the logic" 
| eval State = Status 
| eval State=case(mvdedup(Status)="A" AND mvcount(mvdedup(Status))=1,"Value_1"
        ,mvdedup(Status)="B" AND mvcount(mvdedup(Status))=1,"Value_4"
        ,mvfind(Status,"A")=0,"Value_2"
        ,mvfind(Status,"B")=0,"Value_3")  
| eval remove_index=0,tail=mvindex(Status,(remove_index+1),mvcount(Status)-1) ,Status=tail
| eval Status=mvjoin(Status,",") 
| streamstats count as Event 
| table Event Status State

foreach works for fields, not value.

[cros]

Hi @to4kawa ,

I think your solution may be good thank you. My only concern is about the number of status. In my example i gave only 3 status for each event. But in reality, the number of status can be different for each event (1, 2, 5, or even more). How can i do to manage this ? 

[to4kawa]

index=_internal | head 1 | fields _raw
| eval _raw="Status
A, B, A
A, A, B
B, B, A
B, A, B"
| multikv forceheader=1 
| eval Status=trim(split(Status,","))
| rename COMMENT as "the logic"
| eval State_1=mvindex(Status,0,1), State_2=mvindex(Status,1,2)
| table _raw Status State*
| foreach State_* [ eval <<FIELD>>=case(mvdedup(<<FIELD>>)="A" AND mvcount(mvdedup(<<FIELD>>))=1,"Value_1"
,mvdedup(<<FIELD>>)="B" AND mvcount(mvdedup(<<FIELD>>))=1,"Value_4"
,mvfind(<<FIELD>>,"A")=0,"Value_2"
,mvfind(<<FIELD>>,"B")=0,"Value_3")]
| eval State = State_1.",".State_2
| nomv Status
| streamstats count as Event
| table Event Status State

I don't know for sure, since they didn't write it down properly, but is it like this?

[cros]

I'm still working on it. I'm now in this part :

index=_internal 
| head 1 
| fields _raw 
| eval _raw="Status
A, B, A
A, A, B
B, B, A
B, A, B" 
| multikv forceheader=1 
| eval Status=trim(split(Status,",")) 
| rename COMMENT as "the logic" 
| eval State = Status
| foreach State 
[ eval <<FIELD>>=case(mvdedup(<<FIELD>>)="A" AND mvcount(mvdedup(<<FIELD>>))=1,"Value_1"
,mvdedup(<<FIELD>>)="B" AND mvcount(mvdedup(<<FIELD>>))=1,"Value_4"
,mvfind(<<FIELD>>,"A")=0,"Value_2"
,mvfind(<<FIELD>>,"B")=0,"Value_3") 
| eval remove_index=0,tail=mvindex(<<FIELD>>,(remove_index+1),mvcount(<<FIELD>>)-1),<<FIELD>>=tail] 
| nomv Status 
| streamstats count as Event 
| table Event Status State

The following part is for delete the first value of the multivalue field in order to be able to compare each time the first value and the second. 

eval remove_index=0,tail=mvindex(<<FIELD>>,(remove_index+1),mvcount(<<FIELD>>)-1),<<FIELD>>=tail

but this solution do no work. 

Any idea ?