Search multi-valued field with specific values in sequence

[vjajula]

Hi,

I have another request similar to my previous post but with a variation

Here is the multi-valued field ColY. ColY has only two values ON or OFF. I need to find all rows which changed values from ON to OFF or vice-versa in any order. Below is the example

ColX	ColY
A123456	ON ON ON
A123457	ON OFF ON OFF
A123458	ON ON OFF ON ON ON OFF
A123459	OFF OFF OFF
A123460	ON ON ON OFF OFF OFF

Required output

ColX	ColY	totalChanges
A123457	ON OFF ON OFF	3
A123458	ON ON OFF ON ON ON OFF	3
A123460	ON ON ON OFF OFF OFF	1

[kamlesh_vaghela]

@vjajula 

Can you please try this?

YOUR_SEARCH | mvexpand ColY
| autoregress ColY as p_ColY p=1 | autoregress ColX as p_ColX p=1
| eval cnt = if(p_ColY!=ColY and ColX=p_ColX,1,0)
| stats list(ColY) as ColY sum(cnt) as cnt by ColX
| where cnt > 0

My Sample Search :

| makeresults | eval _raw="ColX    ColY
A123456 ON,ON,ON
A123457 ON,OFF,ON,OFF
A123458 ON,ON,OFF,ON,ON,ON,OFF
A123459 OFF,OFF,OFF
A123460 ON,ON,ON,OFF,OFF,OFF" | multikv forceheader=1
| eval ColY=split(ColY,",")
| mvexpand ColY
| autoregress ColY as p_ColY p=1 | autoregress ColX as p_ColX p=1
| eval cnt = if(p_ColY!=ColY and ColX=p_ColX,1,0)
| stats list(ColY) as ColY sum(cnt) as cnt by ColX
| where cnt > 0

 Output.

Thanks
KV
▄︻̷̿┻̿═━一   😉

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.