topic Re: How to add value based on matrix comparison in Splunk Search

How to add value based on matrix comparison

cros — Wed, 13 Jan 2021 14:02:14 GMT

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

Re: How to add value based on matrix comparison

to4kawa — Sat, 16 Jan 2021 01:06:21 GMT

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?

Re: How to add value based on matrix comparison

cros — Tue, 19 Jan 2021 09:13:21 GMT

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 ?

Re: How to add value based on matrix comparison

cros — Tue, 19 Jan 2021 17:03:54 GMT

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 ?

Re: How to add value based on matrix comparison

to4kawa — Tue, 19 Jan 2021 22:45:57 GMT

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.